Dynamic Columns by Drop-down Checkbox Widget Linkage

Version

Expected Effect

If a report contains many data columns but a relatively small volume of data, you can present data across different dimensions flexibly by dynamic grouping. When you select different parameter values, data grouped by the corresponding dimensions is displayed, as shown in the following figure.

Implementation Method

You can use an IF clause to realize the effect of dynamic columns, referencing the method by which all values are selected when the parameter is empty in the dataset parameter setting.

Data Preparation

Create a general report and create a dataset ds1. Since the parameter widgets are checkboxes, you need to use the IN clause for the parameters in the dataset, as shown in the following figure.

The SQL statement is as follows.

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")}

Table Design

Design the table as shown in the following figure and drag the fields into the corresponding cells.

Note:

Since the Salesperson, Product_types, and Product columns are set for dynamic grouping (meaning that these fields are not displayed when parameters are empty), you need to set default values for the Salesperson, Product_types, and Product parameters (default values can be removed later).

Adding Parameter Widgets

Enter the parameter editing panel, and click Add All in the upper part of the right Component Setting area to generate parameter widgets. Click the parameter boxes of Salesperson, Product_types, and Product, and select Drop-down Checkbox Widget, respectively, as shown in the following figure.

Adding Datasets for Options of Parameter Boxes

Note:

Since parameters are linked, you need to add the corresponding parameter filter conditions to the SQL statements in the database queries.

Option of the Salesperson

Create a dataset Salesperson with the SQL statement SELECT distinct Salesperson FROM Sales_volume, as shown in the following figure. Since Salesperson is the first parameter, you can select any salesperson and do not need to set a filter condition for linkage. In this case, the keyword distinct is used to remove duplicate values.

Option of the Product Type

Create a dataset Product_types with the SQL statement SELECT distinct Product_types FROM Sales_volume where 1=1 ${if(len(Salesperson)==0,"","and Salesperson in ('" +Salesperson+ "')")}, as shown in the following figure. Since the values of Product_types need to be displayed based on the selected value of Salesperson, you need to set a Salesperson filter parameter. In this case, the keyword distinct is used to remove duplicate values.

Option of the Product

Create a dataset Product with the SQL statement 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+"')")}, as shown in the following figure. Since the values of Product are displayed based on the values of Salesperson and Product_types, you need to set the Salesperson and Product_types filter parameters. In this case, the keyword distinct is used to remove duplicate values.

Setting Data Dictionaries for Parameter Widgets

Select the drop-down checkboxes of Salesperson, Product_types, and Product separately, set Type to Data Query, and select the corresponding datasets (Salesperson, Product_types, and Product) separately from the drop-down list of Dataset in Data Dictionary. Set Returned Value's Type to String and enter ',' (a comma enclosed in single quotes) in Separator for the three drop-down checkboxes separately. The separator is set to separate multiple selected parameter values. For example, if you select Steven and Sophia for Salesperson, the returned value is 'Steven','Sophia'.

Note:

1. The widget names of the Salesperson and Product types widgets must match the parameter names in the Product types and Product datasets to achieve the linkage effect.

2. The separator must be entered in the English half-width mode.

Condition Attribute Setting

Adding condition attributes for cells B2, C2, and D2: Set Column Width to 0 and enter len($$$)=0 in Formula, as shown in the following figure.

Effect Display

PC

Save the template, and click Pagination Preview. The following figure shows the effect.

Mobile Terminal

The preview effects on the DataAnalyst app and the HTML5 terminal are the same, as shown in the following figure.

For details, you can download the template Dynamic Grouping.cpt.