Dynamic grouping

1. Requirements description

When there are many data columns in the report, but the amount of data is not very large, dynamic grouping can be used to flexibly present data of different dimensions. As shown in the figure below, when different parameter values are selected, the data grouped by the corresponding dimension can be displayed.

2. Implementation ideas

Refer to the parameter setting in the dataset parameter is empty to select all the usages, and use the if statement to realize the dynamic column.

1. Prepare data

Create a new ordinary report, create a new data set ds1 , the SQL statement is:

SELECT Region ${if(len(Salesperson)=0,"",",Salesperson")} ${if(len(Product_types)=0,"",",Product_types")} ${if(len(Product)=0,"",",Product")} ,sum(Sales_Volume) as Sales_Summary
FROM Sales_Volume
where 1=1 
${if(len(Salesperson)==0,"","and Salesperson in ('" +Salesperson+ "')")} 
${if(len(Product_types)==0,"","and Product_types in ('" +Product_types+ "')")} 
${if(len(Product)==0,"","and Product in ('" +Product+ "')")} 
group by Region
${if(len(Salesperson)=0,"",",Salesperson")} 
${if(len(Product_types)=0,"",",Product_types")} 
${if(len(Product)=0,"",",Product")}

Since the parameter widget is a check box, the in statement is required for the parameter in the data set

2. Design table

Design the table as shown below, and drag the fields into the corresponding cells:

Note: Since the three columns of salesperson, product_type, and product are set as dynamic grouping columns, that is, when the parameters are empty, the three fields of salesperson, product_type, and product are not displayed, so the three parameters of salesperson, product_type, and product are not displayed. The default value needs to be set first (the default value can be deleted later).

3. Add parameter widget

Edit the parameter panel, click Add all above the Component Settings on the right to Add All parameter widgets, click the salesperson, product type, product parameter boxes, and select the drop-down check box widget.

4. Add parameter box option data set

Note: Due to the linkage between the parameters, it is necessary to add the corresponding parameter filter conditions in the data set SQL.

1) Salesperson options

New data set salesperson : SELECT distinct Salesperson FROM Sales_volume , because the salesperson is the first parameter and can be selected arbitrarily, so there is no need for linkage filtering (here the distinct keyword is for deduplication)

2) Product type options

New data set product type : SELECT distinct Product_types FROM Sales_volume where 1=1 ${if(len(Salesperson)==0,"","and Salesperson in ('" +Salesperson+ "')")}, Because the product type needs to display the corresponding product type according to the different salespersons, so you need to set the salesperson filtering parameters (here, the distinct keyword is for deduplication)

3) Product options

New data set product : SELECT distinct Product FROM Sales_volume where 1=1 ${if(len(Salesperson)=0,"","and Salesperson in ('"+Salesperson+"')")} ${if(len(Product_types)=0,"","and Product_types in ('"+Product_types+"')")} Because the product needs to display the corresponding product according to the salesperson and product type, it is necessary to set the sales Filter parameters for employees and product types (here the distinct keyword is for deduplication)

5. Set the data dictionary of the parameter widget

Select the Salesperson, Product_type, and Product drop-down checkboxes, set the Data Dictionary asData Query, select the corresponding data set (Salesperson, Product_type, Product), set the Return Value Type to String, and set the separator to ', ' (That is, add a comma in the middle of the single quotation mark. The setting of the separator here is to separate the selected parameter values. For example: when the salesperson chooses Sun Lin and Zhang Shan, the return value is Sun Lin','Zhang Shan)

Note 1: The widget name of the Salesperson and Product_type widget must be consistent with the product type and parameter name in the product data set before linkage can be made.

Note 2: The separator must be entered in the English half-width state.

6. Setting condition attributes

Add conditional attributes to cells B2, C2, and D2 : the column width is 0, and the formula condition is: len($$$)=0 , as shown in the following figure:

7. Effect preview

1) Preview on PC

Save the template, click the Pagination Preview, the effect is as shown in the figure below:

2) Preview effect on mobile terminal

Preview the template on the mobile terminal , and the effect is shown in the figure below:

The completed template can be found at: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Primary\GroupReport\Dynamic_ group.cpt

Click to download the template: Dynamic_grouping.cpt