How can I combine data from multiple databases in one Liberty Reports workbook?
Liberty Reports establishes one default connection for each type of database that is available. All of the Liberty Reports queries and worksheet functions reference a connection name and you can then change the database that a given connection is associated with at anytime. This makes it possible to have one report that you can use with any number of databases, but with the single default connection, you can only report with one database at a time. This article addresses how to create additional connections allowing a single workbook to query data from multiple databases of the same type. This concept applies to all editions of Liberty Reports however the examples below are based on Sage 100 Contractor.
The default connections that are provided are based on the name given to the type of database. One default connection is created for each. For example, the following default connection names may be provided (depending on the editions of Liberty Reports installed):
- Builder MT - WMS
- Prolog Manager
- Sage 100 Contractor
- SQL Server Database
Click here for more information about Databases and Connections.Creating Additional Connections
To have the ability to report data from more than one database (of the same type) within a single workbook, additional connection names for the type of database are needed. You can create as many connections for the same type of database as you may require.
- Click the Liberty Reports tab on the ribbon.
- Click Manage Connections in the Design Tools group.
- Click the Connections tab. Here you will see a list of the default connection names available to the current workbook.
- Click Add to bring up the Create New Connection to Database window.
- In the Connection Name box, enter a new unique connection name. It is recommended that you use a non-database-specific name (e.g., "Sage 100 Contractor Archive #1" instead of "2014 Year End Archive"). This is because you might want to associate the connection with a different database at a later time.
- In the Database list, select the database that you want the connection to be associated with. You can change this at anytime by clicking Select Databases on the Liberty Reports toolbar. For example, you might initially associate your "Sage 100 Contractor Archive #1" connection with your "C:\MB72014 Archive" database, but later you may want to switch it to "C:\MB72015 Archive".
- Select an option under Startup Behavior based on what you want to happen when opening the workbook.
- Click OK to add the new connection and then click Close.
In both the Query Wizard and Function Wizard, the first step allows you to select the connection that is to be used to query the data. If the workbook you are working in already has an active connection, the first step will be skipped over. Just click the Back button to get back to the first step where you can select the desired connection.
Note that each query and worksheet function created via the wizards can only reference a single connection name. Using ordinary Excel formulas you can combine their results in order to get consolidated values if desired.