I. Overview
1. Problem description
By default, dynamic columns made by using data sets or functions can only display fixed column fields. There is no dynamic grouping and statistics function. If the report needs to use dynamic columns and a certain field needs to be summarized, then the original method is not Too applicable and must be improved.
The final effect is as follows:
2. Implementation ideas
Since the summary provided by FineReport for ordinary columns is no longer applicable to dynamic columns, if you want to realize the automatic summary of "Sales_Volome" according to the previous column, you must process it through SQL. First, determine whether the dynamic column has selected "Sales_Volome", and then set the sales field Processed into a summary.
II. Example
1. Prepare template
Open the template %FR_HOME%\webroot\WEB-INF\reportlets\doc\Parameterr\DynamicCol\Data set parameter realization dynamic column.cpt . For details, please refer to: Data Set Parameters Realize Dynamic Columns .
1) Cell data settings
Change the data setting of cell A2-F2 from "List" to "Group", as shown in the figure:
2) Set sql statement
The SQL statement to modify the data set ds1 is: SELECT ${if(len(col)=0,'*',replace(col,'Sales_Volume','sum(Sales_Volume)'))} FROM Sales_Volume ${if(len(col)<1,"","group by "+replace(col,',Sales_Volume',''))}
When the parameter is empty, query all, grouped and displayed according to the left parent grid
When the parameter is checked but the "Sales_Volume" column is not included, only the left parent cell will be grouped and displayed
When the parameter is checked and the "Sales_Volume" column is included, the "Sales_Volume" column can be grouped and summarized by the left parent grid
The statement description is as follows:
formula | Formula description |
${} | The built-in formulas of FineReport can be supported in parentheses |
if(len(col)=0,'*',replace(col,'Sales_Volume','sum(Sales_Volume)')) | Parameters representing col is empty, the query all; when not empty, use the replace function to query values result in "Sales_Volume" row with a sum (Sales_Volume) replaced |
if(len(col)<1,"","group by "+replace(col,',Sales_Volume','')) | Indicates that when the parameter col is empty, execute empty, which is equivalent to SELECT ${if(len(col)=0,'*',replace(col,'Sales_Volume','sum(Sales_Volume)'))} FROM Sales_Volume When it is not empty, execute "group by "+replace(col,',Sales_Volume','') to group the query results according to one or more columns; |
"group by "+replace(col,',Sales_Volume','') | The sales column cannot appear in the group by , so if the "sales" column is checked, the "sales" column is replaced with empty Note: The comma in',sales' cannot be omitted, because when replacing with a null value, an SQL statement error will occur. |
3) Delete the variable default value of the parameter col
As shown in the following figure:
2. Effect preview
1) PC terminal
Save the template, click "Pagition Preview", the effect is as shown in the figure below:
2) Mobile terminal
Both App and H5 are supported, and the effect is shown in the figure below:
III. Template download
The template has been completed, please refer to: %FR_HOME%\webroot\WEB-INF\reportlets\doc-EN\Parameter\DynamicCol\Group_statistics_for_dynamic_columns.cpt
Click to download the template: Group_statistics_for_dynamic_columns.cpt
Previous：Dynamic grouping
