Parameter Linkage Among Drop-down Boxes by SQL Statements

Version

Expected Effect

When multiple parameter drop-down boxes exist in a template, the options of the latter drop-down boxes need to change accordingly with the parameter value of the former drop-down box. After you select the values in the former drop-down boxes, the options of the latter drop-down boxes are displayed according to the selected values in the former drop-down box. Hierarchical relationships exist among the options of drop-down boxes, enabling multi-level linkage.

For example, the options of the drop-down box product_type need to change with the selected value in the drop-down box region, while the options of the drop-down box product need to change with the selected values in the drop-down boxes of 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 datasets for the latter parameter boxes can reference the former parameters for filtering.

Data Preparation

Create a general report and create a dataset ds1 with the SQL statement SELECT * FROM Sales_Volume where Region = '${region}' and Product_Types = '${product_type}' and Product = '${product}', 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 Box Widget, respectively, as shown in the following figure.

Adding Datasets for Options of the 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 = '${region}', as shown in the following figure. Since the values of Product Type need to be displayed based on the selected value of Region, you need to set region as a filter parameter.

Product Option

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

Setting Data Dictionaries for Parameter Widgets

1. Select the drop-down box widget region. Widget Name is set to region. In the Data Dictionary setting box, set Type to Data Query, select Region as Dataset, and set Display Value and Actual Value to Region, respectively, as shown in the following figure.

2. Select the drop-down box widget product_type. Widget Name is set to product_type. In the Data Dictionary setting box, set Type to Data Query, select Product Type as Dataset, set Display Value and Actual Value to Product_types, respectively, as shown in the following figure.

3. Select the drop-down box widget product, Widget Name is set to product. In the Data Dictionary setting box, set Type to Data Query, select Product as Dataset, set Display Value and Actual Value to Product, respectively, as shown in the following figure.

Effect Display

PC

Save the template and click Pagination Preview, as shown in the following figure.

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

Template Download

For details, you can download the template Parameter Linkage in Drop-down Box Widgets.cpt.