Improving Report Performance & Run Time

You may have found previously that you have created a Report, but then when you try to view the Report it takes a very long time to open or does not load at all. There are a number of factors that may contribute to this that are easy for you to change and will get your Report working in the way you had hoped it would when you built it. 

Too much data!

If you’re asking your report to look at every contract line item for the past five years, or you’re asking it to drill through to every audited field change for each record, or any other report where you ask Workbooks to access thousands of records, then it will take time to do this.

Think about the data you are really interested in seeing in this report:

• How often is the report used? If viewed every day, do you need to see all data from years or even months ago? 
• Who is using this report? Would the report be more readable for them if split into subsets of data specific to the relevant user groups?
• Where is the report being displayed? If used for multiple Report Cells, could the report be rebuilt into separate reports that only contain the data specific to each Report Cell field?

Here are a few examples:

• A report to find the most recent activity created against an Organisation doesn’t need to include activities created more than a few months ago, or include Organisations that are no longer customers.
• A report on field changes on Opportunities could be constrained to only Audit Records for the fields you’re interested in, and only on Opportunities that are still open.

Number of Record Types

Within a report the possibilities are endless – from a People report you can drill into Organisations, Activities, Transaction Documents, Campaigns, Audit and more, then from each of these you can drill into even more record types.

However, as you add more Record Types into the report, more data rows will be added in and the report will become slower.

Often reports end up this way when there are multiple Summary Views all trying to display different results and therefore needing slightly different data. In these scenarios, instead of trying to achieve everything in one report, it is better to build multiple reports where you can ensure that the report only contains the Record Types for the particular Summary View you’re trying to build.

Sometimes, the addition of extra Record Types into the report is accidental. There can be multiple ways to drill through to the same Record Type, and if a different path is used for different columns or criteria, this can end up duplicating data. For example, it is possible to drill directly from Organisations to Cases, or to first drill through to Employees and then Cases. These 2 routes will provide different results, and if both drill through paths are used in the same report, it can cause duplication of rows.

Inefficient criteria

A report is considered efficient or performant if it loads quickly. For the reports to be efficient, they require efficient criteria. For example:

• Equals and Starts with are more efficient than Contains
• Equals is more efficient than Does not equal
• Does not equal is more efficient than Does not contain
• Standard criteria are more efficient than calculated criteria

Sometimes simply changing criteria from a contains to an equals can really improve the speed of your report, for example:

Opportunity Stage equals “(5) Closed Won, (6) Closed Lost”

Is better than 

Opportunity Stage contains “5, 6”

These changes are more noticeable when your report contains a lot of data.

You should also try to apply the criteria to the record type that your report is based on. For example, if building a report of People that have related Activities in the last month, the report could be started from People, drill through to Activities, and then apply criteria on the Activities to show those with a Completed Date in the last month. Or the report could be started from Activities, with the criteria on the Completed Date, and then drill through to People. Starting the report from Activities will produce a faster report because the criteria are applied to the starting table. Depending on the report, it can make the report faster by changing which Record Type you start it from.

This is especially important if the report is being added as a Report Grid or Report Cell on a Form Layout, as here the criteria are also applied through the constraints on the field. For example, if adding a Report Grid on Organisations that shows all open Opportunities for that Organisation, the constraint on the field will use the Organisation Id or Reference. As this is the main criteria, it is therefore better to build the report from Organisations and not Opportunities.

Calculated Columns and Criteria

It takes time to do calculations. Workbooks is very quick at doing these, however, if there are a lot of them to do, it will still impact loading time. The calculations all have to be done before the report can be loaded, so the more calculations there are, the longer it will take the report to load – especially if these are then used in calculated criteria. Therefore, try to avoid using calculated criteria where standard criteria can be used.

The other factor is the complexity of the calculations. The more complex the calculation, the longer it will take to complete. If the calculation has to compare many different record types or contains long if statements, they will take longer to complete. Try to keep the calculations simple, and remove any calculated columns that are not used.

In summary…

• Try not to build a report that does everything in one go, and instead separate it out into several separate reports or report views. While it may be preferable to pull through all this information into one report, consider the ways you can split the report out into smaller reports. You could then add these reports into a dashboard so that you still have an overview of them all in one place.
• Use criteria to ensure that you are only seeing the data that is relevant and informative.
• Use standard columns and criteria over calculated ones as much as possible.

Sometimes reports need to drill through to many Record Types or contain complicated calculations due to the way your data is currently stored. If this is the case, it is worth engaging with the Workbooks Support or Professional Services Team, as often there could be a process put in place which makes data easier to report on. For example, if you want to report on the date and time that an Opportunity Stage was changed to Closed Won, you have to drill through to the Audit Field Changes, but with a process, the date and time could be stored in a field on the Opportunity, which is much easier to report on.

If you are unsure of how to simplify your reports any further or need someone to review your calculated columns to make them less complex, then please get in touch with our Support Team.