In Sage Intelligence, a number of reports are provided out-the-box for each Sage product. Most of these are standard'single company' reports with some of them provided as consolidation versions—allowing you to aggregate data from multiple companies and create a multi-company report. It is, however, possible to turn any standard report into a consolidation report, and in this tip, I'm going to show you how.
1. Add a Consolidation Connection
Most Sage Intelligence instances already come shipped with a Consolidation Connection. However, if yours doesn't, then you'll need to add one and will need the Connector module for this. If you already have a Consolidation Connection then you can skip to point two, otherwise, follow the below steps to add your own.
a. In the Connector, add a new Connection under the correct driver.
b. Give the Connection a name and click Add. Don't worry about entering any other details in the Connection Info dialogue.
c. Your new Connection will be listed under the Connection Type. Select it, and in the properties window, click the Show Advanced option.
d. Select the options Use Auto Connection System and Consolidation Connection and click Apply.
Once you have applied the changes to the Connection, the relevant connection details will be populated in the properties window.
2. Check to see if you have the latest version of the report you want to consolidate
It's always recommended that you work with the latest version of a report. You can check if there are any updates to a report via the Report Utility.
You can also use the Utility to browse for other reports that may be of interest to you as new ones are added regularly.
3. Make sure your report is unlocked
In the Report Manager, if you have downloaded a report from the Utility, it will likely be unlocked. However, if you want to make use of one of the standard reports that are shipped with Intelligence Reporting, unless you've previously made a copy of it, you will need to unlock it. If you're not sure how to do this, then take a look at the short video here.
It's also recommended that you rename the report after you've made a copy of it.
4. Export and import the report onto your Consolidation Connection
Exporting a report and importing it onto a Consolidation Connection allows it to query multiple companies when it is run.
To do this, follow these steps:
a. Export your report to the desired location on your PC using the Export function on the Tools tab.
b. Then import the same report back into the Report Manager, now using the Import function.
You may want to add a new folder for your consolidation reports.
c. During the import process, when you get to the Import Report dialogue, make sure you select your Consolidation Connection and the folder you want the report placed in.
5. Set the companies you would like data pulled for
With your report imported onto the Consolidation Connection, under its properties, there will now be a Database Consolidation List field showing all the available companies—and you can select the ones you want to include in your report.
Take note that in a Union Report, this property is provided on all the Union Sub-reports, and you'll need to select the companies you want for each one.
If the Union Sub-reports aren't visible, then use the Toggle Hidden Reports option on the Tools tab to display them.
Also take note that in order for your companies to appear in the Database Consolidation List dialogue, they need to be cached in your Tenant Cache. To do this, close the Report Manager, sign into the respected company in your Sage product, and then open the Report Manager again. Repeat this for each company you want to be loaded.
If you would like to view the companies that are saved in your Tenant Cache, select the Home object in the Report Manager and then on the Tools tab select Tenant Cache and then View Tenant Cache.
Once the companies you want have been checked in the Database Consolidation List, you can run out your report. The relevant information from each company will be output to their respective sheets in the report.