Parameter Linkage Among Drop-down Checkboxes by SQL Statements

Version

Expected Effect

The previous document describes how to link parameters across drop-down boxes by SQL statements. When the parameter widget is a drop-down checkbox widget, how to realize the same linkage effect? For example, the options of the drop-down checkbox product_type will change accordingly with the selected values in the drop-down checkbox region, while the options of the drop-down checkbox product will change accordingly with the selected values in the drop-down checkboxes region and product_type.

Implementation Method

You can realize the data linkage by the SQL functions. The datasets added are used as data dictionaries for each parameter box. The dataset for the latter parameter box references the former parameter for filtering.

Data Preparation

Create a general report and create a dataset ds1. The SQL statement is as follows:

SELECT * FROM Sales_Volume WHERE Region IN ('${region}') AND Product_Types IN ('${product_type}') AND Product IN ('${product}')

Since all parameter boxes are checkbox widgets, you need to use the IN clauses for the dataset parameters in the SQL statement, as shown in the following figure.

Table Design

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

Adding Parameter Widgets

Click the  icon to edit the parameter panel, click Add All in the upper part of the right Component Setting area to generate parameter widgets, click the parameter boxes region, product_type, and product, and select Drop-down Checkbox Widget, respectively, as shown in the following figure.

Adding Datasets for Options of Parameter Boxes

Region Option

Create a dataset Region Option with the SQL statement SELECT distinct Region FROM Sales_Volume, as shown in the following figure. Since region is the first parameter without any filtering for linkage, you can specify the option range of the parameter as required. In this case, the keyword distinct is used to remove duplicate values.

Product Type Option

Create a dataset Product Type Option with the SQL statement SELECT distinct Product_Types FROM Sales_volume WHERE Region IN ('${region}'), as shown in the following figure. Since the values of Product_Types need to be displayed based on the selected value of Region, you need to set region as the filter parameter here.

Product Option

Create a dataset Product Option with the SQL statement SELECT Product FROM Sales_Volume WHERE Region IN ('${region}') AND Product_Types IN ('${product_type}'), as shown in the following figure. Since the values of Product are displayed based on the values of Product_Types and Region, you need to set region and product_type as filter parameters here.

Setting Data Dictionaries for Parameter Widgets

1. Select the drop-down checkbox widget region. Widget Name is set to region. In the Data Dictionary setting box, set Type to Data Query, set Dataset to Region Option, and set Display Value and Actual Value to Region, respectively. Set Returned Value's Type to String, and set Separator to ',' (namely, a comma enclosed with single quote marks in English half-width input mode). In this case, the separator is set to separate multiple parameter values. For example, when you set Region to New York and California, the returned value is New York,California.

Note:

The widget names of region and product_type must be consistent with the names of parameters in the Region Option and Product Type Option datasets. Otherwise, the linkage will fail.

2. Select the drop-down checkbox widget product_type. Widget Name is set to product_type. In the Data Dictionary setting box, set Type to Data Query, set Dataset to Product Type Option, and set Display Value and Actual Value to Product_types, respectively. Set Returned Value's Type to String, and set Separator to ',' (namely, a comma enclosed with single quote marks in English half-width input mode). In this case, the separator is set to separate multiple parameter values. For example, when you set Region to New York and California, the returned value is New York,California.

3. Select the drop-down checkbox widget product. Widget Name is set to product. In the Data Dictionary setting box, set Type to Data Query, set Dataset to Product Option, and set Display Value and Actual Value to Product, respectively. Set Returned Value's Type to String, and set Separator to ',' (namely, a comma enclosed with single quote marks in English half-width input mode). In this case, the separator is set to separate multiple parameter values. For example, when you set region to New York and California, the returned value is New York,California.

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 templateParameter Linkage Among Drop-down Checkboxes by SQL Statements.cpt.