Using Multiple Data Sources in a Report

You can create reports that contain data from multiple queries that use a wide range of data sources (relational database queries, OLAP server queries, imported data sets, and local join queries).

In a sense, the data in a single table correlates data across queries in a report, retrieving all rows from the table on the “left” and any rows from the table on the “right” that have matching values. Unlike actual joined topics in the Query section, the tables are not linked in the same sense. In the Report section, data is only in the report.

The rules for using multiple data sources in a single report are:

Report group headers require a common value that belongs to all results or table sets be included in the report. If you introduce a unique value belonging to one results or table set, nothing is returned for any band below that group header.
Table Dimensions items can originate from only one data source.
Values from multiple data sources can be included in Table Facts.
The Results column that you place as a table determines the Facts used. For example, you have two Results sets:
Results 1
Region
Sales

Results 2
Region
Units

East
10

East
100

West
20

West
200

South
400
If you place “Region” from Results 1 into the Table Dimension and then drop Results 1: Sales and Results 2: Units into the Table Facts, you get the following table:
Region
Sales
Units
East
10
100
West
20
200
Note that the data from Results 2: South is not included.
Smart charts and pivot tables added to a multiple data source report are subject to the same restrictions as described above.

Region	Sales	Units
East	10	100
West	20	200

To build a multiple data source report:

Build the queries that you want to include in the report:
- Verify item data types and associated data values in source documents so that you know how to correlate them when creating the reports.
- Build the Request line, and add server and local filters, data functions, and computations to the query as needed.
Click Process.
Select Insert, then New Report.
A new report section is displayed.
From Elements, expand the file button of the Results or Table set.
If necessary, click Groups on the Section title bar to open the Group data layout.
A group header categorizes data into repeating collections of records in a header band.
If necessary, click Table on the Section title bar to open the Table data layout.
The Table data layout shows two panes: Table Dimensions and Table Facts. Use the Table Dimensions pane to build the column dimensions (labels) in the report. Use the Table Facts pane to build the numeric values in the report.
Build the group header band by dragging the item(s) from the Results sections from Elements to the Groups data layout.
Build the column dimensions of the report, drag the item(s) from the Query sections from Elements to the Table Dimensions in data layout pane.
Build the column numeric values of the report by dragging the item(s) from the Query sections from Elements to the Table Facts in data layout pane.