Overview
Version
Report Server Version |
11.0 |
Expected Effect
The previous document introduces simple multi-source reports. However, in practical applications, row/column data or data obtained from summary calculation in a cross table often comes from different databases.
In the following report, data in columns Project Name, Total Projects, Required Finance, Self-Financed, and Other Funds is all from different datasets.
Implementation Method
Data in this report comes from five different datasets. You can associate and display the data in this report through data column association.
This helps avoid using the traditional approach of combining five datasets into a single source with complex SQL statements, significantly reducing the difficulty of report creation and the requirement for SQL proficiency of report designers.
Example
Report Creation
Dataset Creation
The database has five data tables, one of which stores project ID and name data, and the other four store data on project quantity, financial subsidy funds, self-raised funds, and other funds, respectively.
Each table establishes data associations with the table project through the field ID.
Note:
1. Project IDs and names (ID, Project Name) - project
2. Project Quantity (ID, Quantity) - zs
3. Financial subsidy funds (ID, Amount) - cz
4. Self-raised funds (ID, Amount) - dz
5. Other funds (ID, Amount) - qt
Report Style
The following figure shows the report style.
Data Column and Filter Settings
Drag data columns into corresponding cells. The following table shows the setting for the cells.
| Cell | Dataset | Column Name | Setting |
|---|---|---|---|
| B4 | project | Project Name | Set Data Setting to Group, Expansion Direction to Vertical, and others by default. |
| C4 | zs | Quantity | Set Data Setting to Summary> Sum, Expansion Direction to No Expansion, the filter condition as (Column Name:ID) Equal to 'project.ID', and others by default. |
E4 | cz | Amount | Set Data Setting to Summary> Sum, Expansion Direction to No Expansion, the filter condition as (Column Name:ID) Equal to 'project.ID', and others by default. |
F4 | dz | Amount | Set Data Setting to Summary> Sum, Expansion Direction to No Expansion, the filter condition as (Column Name:ID) Equal to 'project.ID', and others by default. |
G4 | qt | Amount | Set Data Setting to Summary> Sum, Expansion Direction to No Expansion, the filter condition as (Column Name:ID) Equal to 'project.ID', and others by default. |
The following figures show the data and filter setting of cell C4.
Other cells are similar and will not be explained further in this document.
Numbers and Formulas
Enter formulas in the cells where you need to add numbers and calculate totals, as shown in the following table.
| Cell | Formula | Other Setting |
|---|---|---|
A4 | =&B4 | Set Left Parent Cell to B4 on the right Expansion panel (The panel can be displayed by right-clicking cell A4 and selecting Expansion from the drop-down list). |
D4 | =sum(E4:G4) | Set Expansion Direction to No Expansion. |
C5 | =SUM(C4) | Set Expansion Direction to No Expansion. |
D5 | =SUM(D4) | Set Expansion Direction to No Expansion. |
E5 | =SUM(E4) | Set Expansion Direction to No Expansion. |
F5 | =SUM(F4) | Set Expansion Direction to No Expansion. |
G5 | =SUM(G4) | Set Expansion Direction to No Expansion. |
The following figure shows the setting of cell A4.
Effect Display
PC
Save the template and click Pagination Preview. The effect is the same as that shown in section "Expected Effect."
Mobile Terminal
App
HTML5
Completed Template
You can download the template: Multi-Source Report.cpt.
Previous：Simple Multi-Source Report
