Dynamically Display First N Groups-FineReport Help Document

Last update：March 24, 2021

I. Overview

1.1 Problem Description

In the report application, the grouping and summary data display only needs to display the first few categories with a relatively high proportion, and the remaining categories with a small proportion are usually merged into other categories, making the displayed table look more concise and clear. So in the case of ordinary grouping, how to dynamically display the first N groups and merge the following groups into others? Results as shown below:

1.2 Realization Ideas

In the Cell Element> Data Settings position, grouping is achieved through custom formulas.

II. Example

2.1 Template Preparation

1) Create a new dataset ds1, the sql statement is:

SELECT * FROM SALES_BASIC where Product='Television'

As shown in the figure below:

2) In turn, drag the Salesperson, Product Type, Product, and Sales Volume fields in ds1 into cells A2, B2, C2, and D2 to set the template style, as shown in the following figure:

3) Click cell D2, select "Summary-Sum" in the "Cell Element"> "Data Settings" position, and set the data type of cell D2 to Group Sum, as shown in the figure below:

2.2 Set Drop-down Box Widget

Add a drop-down box widet, which is used to select the first few groups that need to be displayed.

1) Create a new template parameter TOPN. Click "Template", select "Template Parameters", create a new template parameter "TOPN", and select "Integer" as the default value, as shown in the figure below:

2) Add the TOPN parameter in the parameter panel, select the widget type as "drop-down box widget", select the data type as "formula" in the "data dictionary", and write the formula range(1,5) in the actual value, which is the drop-down box Add digital values that can be selected, as shown in the following figure:

Note: The RANGE (from, to, step) function represents a sequence of numbers starting from the integer from, taking step as the size of each step, up to the integer to.

2.3 Custom Group

1) Select cell A2, and in the "Cell Element"> "Data Settings" position, select "Group-Advanced-Custom" in turn, as shown in the figure below:

2) Click the "Custom" position, select "Formula Group" as the custom grouping method, select "Ordinary Grouping" as the display mode, and write the formula in the "Custom Value" position: if(inarray($$$,ds1.select( Salesperson))<=$TOPN,$$$,"Other"), merge all salespersons after the Nth salesperson into others, as shown in the following figure:

Formula explanation:

Steps Effect Formula Description
ds1.select(Salesperson) Take the Salesperson column in the ds1 dataset tablename.select(colname)：Select the data in a certain column of the dataset, and the return result is an array
inarray($$$,ds1.select(Salesperson)) Get the position of the current cell salesperson in the Salesperson column inarray(co,array)：Returns the position of co in the array array, if co is not in the array, returns 0
if(inarray($$$,ds1.select(Salesperson))<=$TOPN,$$$,"Other") Rename the salesperson whose position is greater than N to other
IF(boolean,string1,string2)：Judgment function, return string1 when boolean is true, and return string2 when it is false
$parameter name: reference parameter

Steps	Effect	Formula Description
ds1.select(Salesperson)	Take the Salesperson column in the ds1 dataset	tablename.select(colname)：Select the data in a certain column of the dataset, and the return result is an array
inarray($$$,ds1.select(Salesperson))	Get the position of the current cell salesperson in the Salesperson column	inarray(co,array)：Returns the position of co in the array array, if co is not in the array, returns 0
if(inarray($$$,ds1.select(Salesperson))<=$TOPN,$$$,"Other")	Rename the salesperson whose position is greater than N to other	IF(boolean,string1,string2)：Judgment function, return string1 when boolean is true, and return string2 when it is false $parameter name: reference parameter