Redefining Fields by Filters in OpenAir

Last week’s tip talked about the powerful filtering capability within calculated fields that can help you define and redefine values for your OpenAir reporting needs.  When you want to define new values by simply filtering on a control values of a field to get a new one, such as creating a ‘billable time’ calculated field which is only a filter on time type, for example, you can achieve this by creating a basic equation of Timesheet-all hours times 1.  But where does the 1 come from?  It is actually a calculated detail field used specifically for these types of calculated summary values.

To setup a calculated detail field, navigate to the Administration -> Custom Calculations -> Custom Detail Fields area

From here, the New menu will provide a long list of database tables to choose from.  The key to setting up the detail field is selecting the table that matches the field used in the equation.  In the case of the example above, select the Time Entry Detail field option since you are creating a custom calculation involving timesheet data.   If you were handling something with invoicing or revenue, you could pick the Charge Detail or Recognition Transaction Detail option, respectively.

Once you’ve selected the desired detail field, now you can setup your CONSTANT value which can be used in the custom calculations.  Make sure the constant value is setup as a Ratio type so it can be used effectively in any type of operation (times, divided by, minus, etc.)

You will be able to select this new field when defining your custom calculations AND, since the constant is of the same data type as the field used in the equation, values will roll-up in summary reports!