The Secret to Overcoming Data Filtering Challenges with Custom Calculations

OpenAir offers powerful reporting capabilities that allow you to access and share key data from your OpenAir system. However, it’s not uncommon for our customers to report that they are seeing data in their report that is not supposed to be included based on how they’ve configured their report. One of the first things that we will check is whether custom calculations are being used.

Custom calculations are one way to enhance your OpenAir reporting by allowing you to define specific reporting values that can augment the values available natively from OpenAir. Administration > Reports > Custom calculations

In addition to specific operations (addition, subtraction, multiplication, division), custom calculations allow for the inclusion of specific filters so that the values they display can be tailored to your specific requirements. By default, these custom calculation filters may override filters applied to your report, which can cause confusion or even render the report unsuitable to your needs. Because of this, it’s often the case that when you use custom calculations you will see data in your report that you’re not expecting.

For example, you may want to create a calculation to display approved timesheet hours, but only for time entries associated with specific task types. In the custom calculation you can add a task type filter and select only those task types that you want the calculation to dispay:

This calculation will display time entries associated with the task types “billable” and “approved investment” (task types are customizable in OA, so your specific task types will vary).

When including this calculation in a report, we may ONLY wish to display values from the “billable” task type, which can be set as a report filter in the report form. However, your report may return data that includes hours from the “approved investment” task type. This happens because the custom calculation filter overrides the report filter.

You can see in the example below that, even though the report is filtered to only show the approved investment task type, values associated with the billable task type are also displayed:

There is a solution! An internal switch called Enable intersection of global report filters with calculated fields will allow the report filter to be respected even if the report has a custom calculation using its own filters.

Now that the switch is enabled your report filters will be respected even if there is a custom calculation filtered on the same field.