Build a Report From Multiple Data Sources

Overview

If you want to build a report that uses multiple databases of any type, you need to create a data source for each database (refer to instructions in this Help Center to find information on configuring your specific type of data sources).

Once your data sources are configured, you can specify separate connections in both the NL and the GL functions. You can change the connection that the NL function uses by specifying "DataSource=" as a filter field and the data source name as a filter.

For data sources that have separate companies (Dynamics GP, Dynamics NAV, and Dynamics 365 Business Central) you must also specify the "Company=" filter field and company name as a filter in NL functions or use the Company filter parameter in the GL function.

Examples

=NL("Rows","Customer","No.","DataSource=","NAV SQL")or=NL("Rows","Customer","No.","DataSource=","NAV SQL","Company=","CompanyABC")

When creating an NL function with the Jet Function Wizard (Jfx), Under Filters, select "DataSource=" from the available keyword list:

The Jet Browser will include "DataSource=" automatically:

The GL function has a separate parameter that will accept the data source name and company name(s).

This example sums the amounts from Purchase Invoice number 1000 from both a Dynamics NAV database and a Northwind Access database.

=NL("First","Purch. Inv. Header","Amount","No.","1000","DataSource=","NAV SQL")+NL("First","Orders","Amount","OrderID","1000","DataSource=","NorthWind")

In the example, there is a data source named NAV SQL that connects to a Dynamics NAV database and a data source named NorthWind that connects to the NorthWind Access database.

An important point to notice is that if you and a colleague both use the same report, you need to name your connections the same thing (either through shared data sources - using the Jet Service Tier - or by using identical local settings).