Multi-Source Sliced Report

Version

Expected Effect

Multiple sources: Data in a multi-source report comes from multiple data tables or even multiple physical databases. Generally, a multi-source report contains seven to a dozen data sources (not just two or three), and cannot be converted to a single-source report.

Slice: A report is divided into multiple areas vertically, horizontally, or both, with data in each area often coming from different data sources.

The following figure shows an example of the multi-source sliced report.

Implementation Method

A multi-source sliced report typically features complex horizontal and vertical headers. When designing such a report, you can design the headers first and data in the middle area last. You only need to set the corresponding associations and filters and perform the display settings such as the template data dictionary at the end.

Another method for quickly creating a multi-source sliced report is to design each slice separately. For example, for the report shown above, you can start by designing the slice one, verify its accuracy and proceed to design the slice two, and so on. Similarly, the display settings such as the template data dictionary are performed at the end.

Report Creation

Data Preparation

Connect to the database FRDemo and create four datasets named Employee, Product, Order Info, and Order. The corresponding SQL statements are as follows:

The SQL statement for the Employee dataset: SELECT EMPID,TITLE FROM EMPLOYEE

The SQL statement for the Product dataset: SELECT ProductID,CategoryID FROM Product WHERE CategoryID =1 or CategoryID =2

The SQL statement for the Order Info dataset: SELECT Orders.'Order ID', Orders.'Employee ID', OrderDetails_new.'Product ID', OrderDetails_new.Amount,(OrderDetails_new.Amount*OrderDetails_new.Price) AS Total FROM Orders, OrderDetails_new WHERE Orders.'Order ID'= OrderDetails_new.'Order ID'

The SQL statement for the Order dataset: SELECT * FROM Orders

Report Style

The following figure shows the report style.

1. Merge cells B2 to D3, select Insert Slash from the drop-down list of Insert Element on the right panel, enter Product|Quantity|Others into the input box, and select Diverge from Top Left to Bottom Right, as shown in the following figure.

2. Create the slice one.

The slice one data is grouped and summarized by country and employee. Data in the middle area of the slice is controlled by both the left and top headers, as shown in the following table.

The following figure shows the settings in cell E4.

3. Create the slice two.

The slice two directly calculates data of the total amount and quantity. Data in the middle area of the slice is controlled only by the top header, as shown in the following table.

4. Create the slice three.

The slice three data is grouped and summarized by title and employee. Data in the middle area of the slice is controlled by both the left and top headers, as shown in the following table.

Display Setting

You can set display styles, formats, and colors for data as needed.

For cells C4, C8, E2, and E3, choose Display >  Data Dictionary and set Type to Database Table, as shown in the following table.

For example, data in cell C4 comes from the data column Employee ID in dataset Order. However, the actual content needed to be displayed in cell C4 are employee names, not IDs. The table EMPLOYEE contains the correspondence between EMPID (employee ID) and EMPNAME (employee name). Therefore, you can set Data Dictionary in Display on the right panel for cell C4, set Type to Database Table, set Database to FRDemo, set Database Table to EMPLOYEE, and set the column indexes of Actual Value and Display Value to 1 and 2 respectively.

Other cells are similar and will not be explained further in this document.

Freeze Setting and Page Setting

1. Choose Template > Repeat/Freeze. You can set the template to repeat the title row from the No. 1 row to the No. 3 row, repeat the title column from the No. A column to the No. D column, freeze the No. 1 row to the No. 3 row, and freeze the No. A column to the No. D column, as shown in the following figure.

2. Choose Template > Page Setting and set the page size to A3, as shown in the following figure.

Effect Display

PC

Save the template and click Pagination Preview. The effect is the same as that shown in section "Expected Effect."

Note:

If you do not want the data to bedisplayed in pages, you can click Data Analysis Preview.

Mobile Terminal

1. App

2. HTML5

Completed Template

You can download the template: Multi-Source Sliced Report.cpt.