Regardless of the report writer you are using, the most commonly asked question is, “How can I get my reports to print faster?” There are some options in the build of the reports that can help with performance. Here are just a few Ideas:
- Use attributes to filter rows instead of accounts.
- Create rows and use grouping and sorting within your layout editor.
- Use Native excel formulas, not OSR Sum.
- Create a reporting tree within your data warehouse.
- Use the BI360 Staging (New Feature!)
Use attributes to filter rows instead of accounts
Under your accounts in the data warehouse, create attributes for the accounts. You can create a parent child attribute or a simple group and group descriptions. With these attributes, you can then create a row filter on the group; for example, if you have a parent group for operational expenses and a child for salaries and office expenses you can create a row group by operational expenses and then the child and that will take your row from 30 operational expense lines to 1 parent line of operational expenses.
Create rows and use grouping and sorting within your layout editor
You can use inner and outer groupings on rows to allow for more dynamic reporting. To use a grouping, you can drag in an account and create a row filter by account for example, and then in the row above, you can drag in a segment filter like department. Then in your layout editor, you can expand the spread of the department below to cover more than one row. Now when your report runs, you will group first by department and then by account.
Use Native excel formulas not OSR Sum
When building a report with expanding rows, the easiest way to create a sum is to right click and select OSR Sum of the above row. When you do this and the report is run, the expanding rows are turned into a named value. That is what the OSR Sum is referencing to create the sum. This adds a lot of time to a report generating. A better option is to leave a blank row after the expanding row and then use a excel sum including the blank row. This will give you a sum of all the expanding rows.
Create a reporting tree within the data warehouse
Many companies are creating a departmental report and then running it and creating a tab for each department as well as a summary tab to roll up all the reporting units. If you do this without using a reporting tree than you will have two tabs in your excel workbook: a detail and a summary. This will take additional time to generate your report. If you are using a data warehouse, you can create a reporting tree in the data warehouse and attach that as a sheet filter to your workbook. When generating a report using a tree, you can select a branch of the tree or the entire tree.
Use BI360 Staging
Staging is new in version 4 and was developed for users who connect directly with their ERP systems. Staging creates an optimized database that contains the information that is brought in by selected reports. The staging database is an indexed version of your company’s ERP and contains only the information related to reports that have been analyzed.
To learn more about BI 360 and the tips and tricks to successfully speed up your reports, contact Colleen at KTL Solutions’ main number, 1.866.960.0001 or email at sales@ktlsolutions.com
Colleen Williams | COO, Consultant Lead
Colleen graduated with a Bachelors of Science Degree in Accounting from California State University San Bernardino. She has extensive accounting experience over the past years working as staff accounting and controller. Colleen has over 10 years Great Plains experience which includes many implementations for private companies. She also has extensive experience with Business Intelligence, budgeting and report writing while working on BI360, QuickBooks, Oracle, PeopleSoft, Timberline and Solomon. Colleen has worked in various industries that include: real estate, property management, timeshare sales, publishing and insurance. These various industries have given her the necessary experience to handle large company consolidations and report writing, budgeting and forecasting, project accounting, sales invoice processing, and fixed assets.
