Using Custom Calculations In OpenAir Reporting

Creating Custom Calculations is a standard feature of OpenAir Reporting. They complement the plethora of standard reporting values with your very own calculations to interpret data and align reports to your organization’s measures.

A custom calculation is exactly what the name says – a calculation you create for a customized computed value. You can find this feature in the Administration–>Global Settings page under the Reports header. Access to create custom calculations is granted via the role security setting “view and modify calculated fields” or to anyone who has the full Administrator role. We recommend you limit the number of users who can access this feature to avoid losing control of field definitions.

Start With Picking The Type Of Report

Equations and filters in Custom Calculations enable you to customize reporting values necessary to support business reporting, without having to download and manually calculate information outside of OpenAir. Because OpenAir is designed to accommodate many enterprises, OpenAir has a wide variety of standard reporting values however the underlying definition may not meet your specific needs. By using Custom Calculations, you can tweak reports to align with your business processes and metrics.

To start, you need to pick the type of report you’re working with, so the system can present you with the list of available operands. A Project company summary report will have different calculations to choose from than an account-wide company summary report. The type of report is the foundation of your available values for use in a calculation. Account-wide company summary reports provide the most flexibility across modules when creating reports and they also provide the most flexibility when creating Custom Calculations.

Defining Constants

Believe it not, the key to unlocking the real power in OpenAir’s Custom Calculations is defining constants. A Custom Calculation must have an equation even if you are just trying to filter a single value by a certain attribute (i.e. Timesheet – all hours by task type or time type). Remember your 3rd-grade math? X*1 = X, Y-0 = Y, etc. Those are equations so setting up constants for 1 and 0 are extremely helpful to create an equation and therefore be able to set filters to define new reporting values.

As obvious as it may seem, each constant needs to be individually defined by data types (i.e 1-time entry, 1 expense, etc.). Mismatched types will give you unexpected results in your calculations. If your reports are filled with unexpected zeroes, it’s most likely caused by mismatched types, and constants need to be created for the base equation to work.

Tips And Tricks To Filters

The same filters are available in Custom Calculation as in the type of report you choose to start the custom calculation creation process. These filters are made up of standard tables, custom fields such as dropdowns and picklists, and user entity tags.

There are however two unique approaches to consider with Custom Calculations that are different than reports:

Custom Exclusions

OpenAir provides the option to ’exclude’ selections in a filter when creating reports. These exclude options are not available within Custom Calculation filters. You either have to create a filter that includes everything you want, which may require regular maintenance to add new items as data is added to OpenAir or create your own ‘exclusion’ filter group.

To create an exclusion group, first, create a custom calculated value that is filtered on what you would ideally select ‘to exclude’. For example, timesheet hours for all internal users filtered by a custom field of company ownership. Then create another calculation subtracting the exclusion group value from all timesheet hours and call this new value Vendor Hours. With just two simple steps, you’ve created your own custom exclusion group.

Custom Date Filters

Date filters can be uniquely useful in Custom Calculation because they support meta-data values. Relative start and end dates give you control over date ranges and date indexes to refine reporting based on what executive leadership needs to see. For example, you can focus on one project or client and show data for today in relation to the past quarter, fiscal year, calendar year, this time last year, and other filters to provide an insightful overview that would otherwise take a dozen manual reports to show.

Although your Customized Calculations should be doing all the heavy lifting, there are still plenty of filters that can be performed in the reports they’re being fed into. You can run the same report multiple times with different filters to create a wide variety of reports to meet regional views as well as global views. A caution comes with the duality of filtering in calculation and reports. Make sure to avoid setting a filter that is already used as part of the custom calculation. The custom calculation will always win which can confuse the report user when it appears the report’s filter isn’t working.

Document As Well As Define

Custom Calculations have a lot of components. Keeping track of everything is best handled through naming conventions for a clear understanding of use in a report.

Naming Conventions

When creating Custom Calculations, it’s important to name each constant you’re creating so the system understands the difference between it and the pre-existing definitions. Selecting the option to ‘Do not prepend module name’ sets the custom calculated values apart from the long list of reporting value options when setting up reports. It also helps you when customizing your formulas to remember which variables and constants you’re pulling in.

Descriptions And Details

Just like in programming, comments are important for the next person coming along to understand what each formula is meant for. The description field available in a custom calculation definition provides room to notate a short description of the equation’s purpose, but it’s not a novel. Keep in mind you’re limited to 199 characters, even though it’ll let you keep typing beyond that.

Notating a few details or brief description about calculations helps business units differentiate between Custom Calculations that pertain to them. It’s a reliable approach to controlling equations and knowing which value you’re really looking for. Without these important details, you’re running blind and could have conflicts down the road, as different units may end up reporting incorrect data.

Creating Reports And Calculations Tailored To You

OpenAir makes it easy to redefine standard reports using Custom Calculations. Many users today bypass the Advanced utilization or User Summary utilization reports with their own custom calculated utilization values on Account-wide summary reports.

By creating formulas and constants in Custom Calculations, you drill down into the data that’s most important for your company reports. Raw data can be deceiving when it’s pulled in without any filters, and different business units need different perspectives on the same data to properly analyze progress reports.

Taking the time to develop these customized equations creates a pre-filter on top of existing reporting filters to better format data to match other systems, like Excel or SQL. Without them, you won’t have a real perspective on what’s happening, what’s negatively impacting budgeting, forecasting, and more.

Here at Top Step, we’re experts with OpenAir and can work with your company on a custom-fit solution. Check out our most recent webinar to learn more tips and tricks using Custom Calculations in NetSuite’s OpenAir platform.