Data Filtering in Multi-Source Report-FineReport Help Document

Last update： 2024-06-14

Overview

Version

Report Server Version

11.0

Expected Effect

After you learned simple multi-source reports, complex multi-source reports, and multi-source sliced reports, you may want to achieve data filtering in multi-source reports.

For example, you may want to filter the data record of Chen Yu in the column Salesperson, as shown in the following figure.

Implementation Method

You can perform data filtering within the main table cell in multi-source reports.

Example

Data Preparation

Create a dataset named ds1. The database query statement is SELECT * FROM SALES_BASIC.

Create a dataset named ds2. The database query statement is

SELECT * FROM SALES_COST where 1=1

${if(len(Salesperson)=0,""," and Salesperson = '"+Salesperson+"'")}

Report Design

Design a table according to the effect shown in the following figure and drag the fields from the two datasets into corresponding cells.

The following table shows the cell settings.

Cell	Setting
B2, B3	Merge cells B2 and B3 and input Region into the merged cell.
C2, C3	Merge cells C2 and C3 and input Salesperson into the merged cell.
E2, E3	Merge cells E2 and E3 and input Selling Cost into the merged cell.
D2	Drag the data column Product types in dataset ds1 into cell D2. Set Expansion Direction to Horizontal and Parent Cell Setting by default.
D3	Drag the data column Product in dataset ds1 into cell D3. Set Expansion Direction to Horizontal and Parent Cell Setting by default.
B4, C4, and D4	Drag the data columns Region, Salesperson, and Sales Volume in dataset ds1 into cells B4, C4, and D4, respectively. Set Expansion Direction to Vertical.
E4	Drag the data column Selling cost in dataset ds2 into cell E4. Set Expansion Direction to Vertical and Left Parent Cell to cell C4.

Filter Condition Setting

1. Double-click cell B4 and click the Filter tab at the top of the dialog box to add two filter conditions.

Condition one is a common condition, with the data column Salesperson in dataset ds1 equals to the data column Salesperson in ds2. Condition two is a formula condition, with the formula added as len($Salesperson) =0 to achieve the effect of selecting all when the parameter is empty. The two conditions are linked with or.

The effect has been achieved in dataset ds2, but the data in cell B4 comes from dataset ds1, which may contain data not present in the dataset ds2. Therefore, condition two must be added to prevent the data in the main table from decreasing when the parameter is empty.

2. Double-click cell E4 and click the Filter tab at the top of the dialog box to add the filter condition as the data column Salesperson equals to cell C4, as shown in the following figure.

Parameter Panel Setting

Click the icon to go to the parameter panel editing page, click the Add All button, and set the widget in the middle to Number Widget, as shown in the following figure.