Do you want to write a report that consolidates data for different companies even though the databases are not in the same locationserver? We’ll show you how, but, before you can create and design consolidated reports, you need to first understand the Connector module hierarchy and the process of creating new Connections, Containers and Expressions. Hence, we have split this tip into two parts:
- Understanding the Report Connection Hierarchy
- The Process of Connecting to Multiple Servers
After reading and practising this tip you will be able to create a report from different databases and consolidate unlike data in one Microsoft® Excel® workbook.
NOTE: As an example this document describes the process of how to connect to a single Sage 100 ERP type (ProvideX), however this process is not limited to multiple connections in one Connection Type. You could utilize the same technique if the Connection Types were different i.e. for Sage 100 ERP – SQL and ProvideX.
The same method can be used for any other ERP.
1.0 Understanding the Report Connection hierarchy
The process described in this document shows how you can create a single report which merges data from multiple servers by connecting to data which could be under different connection types and servers.
NOTE: This process is not the same as using a Consolidated Report on a Consolidated Connection. A Consolidated Connection still only connects to one server as it’s done on the Connection level, and will only have access to databases on that one server.
Report Connection Hierarchy
Before beginning, the hierarchy of the report connection must be understood.
The report connection hierarchy is as follows:
Connection Type
- Connection (Connects to one server or equivalent)
- Container
Connection Type
The first level of the report connection hierarchy is the Connection Type. Connection Types are used in order to connect to the relevant Sage ERP/Accounting or other databases.
Connections
The second level of the report connection hierarchy is Connections. Connections are located below a Connection Type and are connected to a single server or equivalent, as shown below:
Containers
The third level of a report connection is Containers. Each report, whether a sub report or not, has an associated container. A Container is a published Table, View, Stored Procedure or Dataset (based on a join) that can be made available to allow reports to be written in the Report Manager module.
Expressions
The fourth level of a report connection is Expressions. Adding a data expression enables you to choose the data fields (publish) from the data container(s) which are available through the Report Manager.
The Process of Connecting to Multiple Servers
In the Sage Intelligence Connector create two connections; one for ABX and the other for ABC, under the relevant Connection Type. In this case the connection type is ProvideX for Sage 100 ERP.
NOTE: If you also wanted to report off a SQL database you would create one connection under the MAS 200 Connection Type and the other under the MAS 90 Connection Type. The rest of the steps in the process would be the same.
Include your containers into these connections, this can be done by copying the required container and pasting it into the newly created Connection.
For this example, I copied the standard Customer Sales Container available in Sage 100 ERP and pasted them in each of these connections.
Open the Report Manager and add two new reports (they must be the same report with the same output structure into sheets).
Select the relevant container in each connection, for each report.
In this example one report would be for Test ABC and the next report would be for Test ABX.
In Report Manager, add a new union report and select both reports created in step 3.
Ensure both sub reports have the same output sheet as seen below.
Run out your report and notice that your data from the two different connections is now displayed on the same worksheet.
