Simple multi-source report

1. Concept introduction

Multiple data sources means that the data displayed in the same report comes from multiple different datasets.

As shown in the figure below, for a simple multi data source report, the blue part on the left comes from the sales_basic table, and the yellow part on the right comes from the sales_cost table, that is, the data comes from two different database tables:

2. Implementation ideas

So how to implement such a simple multi-source report in FineReport?

In fact, it is very simple. It is to make good use of the common field to establish the connection between multiple data sets and show their data relationship.

1) Traditional report making method

The traditional method of multi-source report is to integrate multi-source into single source by spelling SQL.

For example, the above example integrates the sales table with the total sales table: select * from sales_basic, sales_cost, where sales_baisc.Salesperson = sales_cost.salesperson.

If the database table is more complex, and the number of tables used is more than 2, it can be imagined that the final SQL query statement will be very complex.

2) How to make in finereport

Finereport naturally supports reports with multiple data sources. Any number of data sets can be added to a report. Each data set uses the simplest SQL statement to query the required table data.

In the report, we only need to use the filter to associate the data from different tables, which makes the multi data source report making easier.

1. Dataset preparation

Add datasets ds1 and ds2，as shown in the figure below.

The SQL statement is select * from sales_basicand select * from sales_cost .

Both DS2 and DS1 datasets have a Salesperson field.

2. Data binding

1) Drag the selling cost data column in DS2 to cell D3, and set the data to group and common, as shown in the following figure:

2) Set the parent cell of cell D3 to cell B3 where the salesperson is located, so that the total selling cost will expand with the salesperson, as shown in the figure below:

3. Setting filter

Although the parent of selling cost is the salesperson data column, because they come from different data sets, gross sales will not filter the parent salesperson as a condition, that is, there is no affiliation.

We need a one-to-one correspondence between the selling cost and the salesperson, so we need to set filter conditions to associate the data of these two datasets with the salesperson.

Select the cell D3 where the total sales amount is located, double-click the cell, select the filter label, add the filter condition: salesperson equals 'B3 ', and take out the data matching the salesperson in cell B3, as shown in the following figure:

4. Effect preview

Save the template and select pagination preview. The effect is as shown in the expected effect in I.1.

For completed templates, see %fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\ReportApplication\BasicApplication\SimpleMulti.cpt

Click download template: 

SimpleMulti.cpt