Did you know you can remove the ‘Generated’ date-timestamp from downloaded reports?

OpenAir reporting is capable of quite a bit of reporting but there are plenty of occasions where people just want to download information into Excel for pivot table use during data analysis. By default, report downloads in OpenAir have 2 additional rows at the bottom of the downloaded data – a blank row and a message stating ‘Generated on: ’ where date and time is the date and time you ran the report.

If you forget to delete this last line, any pivot table or sorting you perform may pick this line up and cause a bit of confusion in your overall result.

You can have this generated message removed automatically from data downloads by contacting OpenAir Support and having the internal switch enabled to ‘disable date and timestamps on reports’. This is a global option, however, so consider carefully if others are using this information for reference such as comparing month to month snapshots downloaded and saved. The blank line at the bottom of the data still remains. This tends to have has less impact on sorting and pivot tables.

Want more OpenAir tips? We’ve pulled them all together into a convenient eBook of 50+ tips from the past year. Click here to find out how to get your copy.

Many customers have the need to invoice customers in a fixed fee manner or payment schedule, but also to do some analysis on the money value of time worked on the project. The setup usually followed is to add time billing rules to a project to convert hours into money equivalents for comparison to the budget and/or invoiced amount to date. Time billing charges in this case are not invoicable simply since the fixed fee rules and defined payment schedule are controlling invoicing; the time charges are only used for reporting. To support this type of reporting, a charge stage may be setup to hold the time billing charges from fixed fee projects. Let’s call the charge stage ‘Hours Equivalent’. To ensure the charges in this charge stage are not invoiced, the setup of the charge stage allows for exclusion of invoicing.

Check the box to ‘exclude charges from invoicing’.

There is one other option that is important, however, regarding charge stage setup and that is the option to exclude charges in the charge stage from the project analysis report. By checking this option, any charges in the charge stage will be ingnored by the financial analysis standard report within the project. This removes the duplicated values under the income section and computed income amount fields so you have a more accurate financial analysis summary report for use.

Only charges not in Hours Equivalent (and other charge stages with the exclude option enabled) will appear in the analysis report found under the Financials drop-down menu (or Analysis link, based on your User Interface settings).

Now you can use OpenAir’s custom calculation feature to create a new reporting field called ‘Hours Equivalent’ or something that ony holds charge values from the Hours Equivalent charge stage. Easy-peasy to compare invoiced and budget to converted time effort now and monitor burn of project delivery!

Want more OpenAir tips? We’ve pulled them all together into a convenient eBook of 50+ tips from the past year. Click here to find out how to get your copy.

This week’s tip comes fresh from the UK OpenAir User group. Many of us, when using reporting, may read the hover pop-up that describes the definition of a field.

Some are quite large and the print can be quite small. And then you move your cursor just a bit and the darn thing disappears – thus the nature of hover pop-ups! For a more permanent and reference oriented solution, consider running the Advanced report Summary Report Values. This report has been a part of OpenAir for many years and existed even before the hover pop-ups were added to the system explaining the fields.

When you run the report, you can pick a cluster of summary values by Module/Entity such as Timesheets, Projects, etc. to display/download for future reference. The information you display is more detailed than the hover pop-ups. Below is a screenshot of what you can include in your reference report including field formula data.

Did you know you can use filters and constants in custom calculations for creating utilization metrics?

A common report request is to have a utilization report displaying billable, non-billable, pre-sales or business development, administration, and so forth types of %’s displayed for employees. The combination of all these values provides a great amount of insight into the activities of individuals, trends among departments, or even just validating the capacity plan is accurate. The challenge is trying to get all of these values on a single OpenAir report since they are derivatives of the same basic equation – timesheet hours / available hours.

Custom Calculations provided the start to handling this situation. Now they have been extended with a feature that allows not only the creation of new computed values, but also the creation of new values based on filtered data or settings. This feature is called Report Filters on Custom Calculations and can be activated by contacting OpenAir Support. Once enabled, your custom calculation screen will change a bit to show a series of field filter options.

Taking our utilization value example, you can now create a new Administration % value by simply setting up the equation of Timesheet-approved hours divided by Work Schedule hours (or whatever your equation is), naming the new calculation ‘Administration %’ then selecting the project stage filter or perhaps the project filter to only include the Administration project.

Now the value will appear in the available values window of report setup on Summary reports which means a new column called Administration % can be added to your utilization report. Repeat this approach for all of the utilization values you would like.

Next week – we’ll talk about ways to create simply a new value with no equation.

You may find yourself wishing you could have certain fields or values tracked as changes over time. OpenAir’s audit trail capability is quite robust in tracking field value changes, but it is not an easily parsed or used field for reporting to view information by historical values. Examples of reports you may have wanted to perform in the past are job code promotions (consider VSOE?), transfer between department or offices of your global organization, and so forth. The cost center functionality in OpenAir automatically tracks historical values based on when the field value changes. The effectivity date of this field is not controlled by the administrator, it is automatically stored by OpenAir when the field value is changed. So what are your alternatives? User Entity Tags are an option to consider.

User Entity Tags were introduced in 2007 with the intent of providing a date-index tracking of user defined fields. When did your consultant get promoted to senior consultant? When did John transfer from the US to the UK offices? This allows reporting to include reports based on certain field filters and the date ranges applicable for those field filters. The cost center functionality is date indexed but the date is not controllable directly the administrator. User Entity Tags provide the ability to change information retrospectively with appropriate date association.

To activate user entity tags, go to the Administration module-Global Settings tab and select Optional features to ‘Enable user tag feature’ switch (or Account module, Company tab, Settings sub-tab).

Once enabled, a new link will appear as part of the user definition:

Before you can set user entity tag values, you have to define which types of values or tags you need to track. These are defined in Tag groups located on the Administration – Global Settings page.

Create a tag group for each type of item you would like to track by date. Each tag group has a name or title and a list of values applicable to the field.

Now you can update your user’s User Entity Tag link with current values and historical values by date.

The Default values are current values while the historical entity tag list holds the historical value of the fields with an associated start/end date which defines when the historical value was valid.

User Entity Tags automatically add filters to reports to be able to include/exclude information by value and be considered by date range.

Next week – other uses for User Entity Tags!