The Report Designer is a powerful tool designed to meet your financial reporting needs. Based on your company data, the resulting workbook can be large in size and depending on your requirements, a lot of the information it contains may not be necessary.
Take the example of a report writer we worked with recently. Using Sage 300, their Designer workbook was sitting on 20 megabytes and this posed problems when trying to share the report. By following a few steps we were able to reduce it to 7 megabytes. This made it faster to run.
In this tip we take you through the steps followed in the above example. They can be summarized as follows:
- Filter unnecessary account types – the report was only focused on Income Statements so Balance Sheets accounts can be removed.
- Remove unused currencies – reporting only needed to be done in Functional Currency.
- Remove zero balances.
- Delete any unnecessary sheets/layouts from your report.
Depending on your Sage 300 setup or the ERP you are using, your Report Designer may be less complex than the one in this example and so not all the steps mentioned above may apply. For example, if you are not using multicurrency then step 2 can be omitted.
It is recommended that you have a good understanding of your ERP’s features as well as Report Manager reports and filters to work through this tip. Step 2 also requires a Connector license and an understanding of containers and expressions.
This tip does not provide a comprehensive guide on the subject but we hope that it will give you some ideas to play with. It will also teach you more about the Report Designer and how it works. Please note that Sage Intelligence does not support any customizations made to your reports.
Step 1
If your report is focused on Income Statements only then can you remove all balance sheet accounts. This is done by adding a filter to the Accounts sub report.
1. In the Report Manager, make a copy of your Report Designer in order to unlock it and be able to work with it.
2. Double click on your copy and then double click on Union Sub Reports to show the sub reports list.
3. Right click on the Report Designer – Accounts item and select Go to Sub Report. This will take you to the actual sub report.
4. In the Filters tab, add a filter to the AccountType field which will only bring through Income Statement accounts.
Step 2
Let’s look at the Actuals sub report now and here we can apply two filters which will remove the unused Currencies and Currency Types. Follow the points in step 1 to navigate to the report.
With this sub report we cannot just go to the Filter tab and click the Add button. This is because Allow Filtering first needs to be enabled on the Currency and Currency Type expressions in the Connector.
1. In the Report Manager, double click on the Actuals sub report and then double click on Source Container.
2. Right-click on the container name and select Go to Container In Connector.
3. Once in the Connector, double click on the container to show the expressions.
4. Select the Currency field and in the properties tab check the Allow Filtering option. Remember to click Apply to save your changes.
5. Do the same for the CurrencyType field.
6. Now in the Report Manager you can add your filters to filter the Currency and Currency Type.
Because of the way the Actuals container is created, if you run your report out at this point you will actually get an error.
There is a trick to solving this which is as follows:
7. Go to the Actuals container again and select the container source.
8. Scroll all the way to the end and add the line WHERE 1=1. This is a dummy WHERE clause which allows the Filters we created above to be added to the end of the container source code.
There are other sub reports you may want to apply the Currency and Currency Type filters to. Examples of these are Budgets and Opening Balances.
Step 3
You may want to remove zero balances from your report. A good example of where to do this is the Quantity sub report. So following what you have learned from the steps above, add a Not Equal To filter to the QuantityAmount field. Note that filtering has already been enabled for the QuantityAmount field in the Connector.
Step 4
Lastly, each sheet in your workbook will add to its size. So run out the report and delete any unnecessary sheets. Depending on your Report Designer, this could be the Home sheet, Instructions sheet or any layouts that might exist. Remember you will then have to save your workbook back to the report in the Report Manager for your changes to persist.
We hope that this tip has given you some more insight into the Report Designer, its capabilities and how to use different filters with it. We happy for you to share this exciting tip with friends and colleagues.
