The Sage Intelligence Financial Report Designer report provides a comprehensive tool for your financial reporting needs. However, there may be times when you’d like to include other data in it to add to the information you can report on in a single report.
Although the Report Designer has a defined structure which must be maintained, it is possible to add other sub reports to it. An example of this would be to add a Sales report so that you can report on both financial and sales data in a single dashboard.
In this tip I’m going to guide you through the steps to achieve this. Take note that the new sub reports won’t benefit from the Layout Generator or Task Pane functionality.
1. Start by running out a copy of the Report Designer report.
2. Then, add a new sheet to the workbook by selecting the plus icon next to the sheet tabs.
3. You can also rename it to reflect the information it will contain, i.e. the standard report that you want to add as a sub report.
4. Next, drag the new sheet to the start of the visible ones.
5. Then count the number hidden sheets in the workbook and make a note of it. You can do this by right clicking on one of the sheet names and selecting Unhide. Then count the number of items listed in the dialogue that appears. In my case it’s 12. This is important as you need to correctly tell Sage Intelligence where to push the data for the added report.
6. Now, with the workbook left open, go back and select the report in the Report Manager. Under the Union Sub Reports tab you’ll see the sub reports that are part of it.
7. The next step is to add the new sub report which you can do by clicking Add and then selecting the one you want.
8. You’ll see that it is added at the bottom of the list and given the output sheet number 1.
9. Drag it to the top so that it runs last and change the output sheet number to the number of hidden sheets plus one. So in my case this would be 13. You can change the output sheet number by right clicking the sub report and opening its properties.
10. You’re now set with all the changes that are required to both your report and template. The last thing to do is save the template back to the report using Save Excel Template on the Home tab.
11. You can now run out your report and will find the information from the added sub report pushed to the new sheet. The new sheet may be hidden but you can access it by unhiding it.
If you find that the information isn’t there it may be that it’s being written to a different sheet. If this is the case then go back and check that you have worked out the ordering correctly. It may help to unhide all sheets in the workbook and count them again from left to right. It’s also important that the new sheet remains in the position it is in. This is why it was moved to the left so that no new layouts would be added before it. Once everything is correctly in place you can continue designing layouts from it and combine the information in them with that of your financial ones.
