Drop-down box parameter linkage

1. Requirement description

When there are multiple parameter drop-down boxes in the template, the options of the later parameter drop-down boxes need to be changed according to the values of the previous parameters. As shown in the figure below, the product needs to change with the product type.

2. Idea to achieve

The new data set is used as the data dictionary of each parameter box, and the previous parameters are referenced in the later parameter box data set for filtering.

1. Data preparation

Create a new common report and a new data set DS1. The SQL statement is as follows:

SELECT * FROM Sales_Volume 

where Region = '${region}' and Product_Types = '${pdtypes}' and Product = '${pd}'

2. Design form

As shown in the figure below, design the table and drag the field into the corresponding cell

3. Add parameter widgets

In the edit parameters pane, click add all above component settings on the right to generate parameter controls. Click the parameter boxes of region, province and city to select the drop-down box widgets.

4. Add parameter box option dataset

1) Regional Options

New dataset region option: SELECT distinct Region FROM Sales_Volume . Since region is the first parameter, you can select it at will, so linkage filtering is not required (the distinct keyword here is for de duplication)

2) Product Types Options

New dataset product type option:

select distinct Product_Types from sales_volume

where Region = '${region}'

, because product types need to display according to different region values, so region filtering parameters need to be set (the distinct keyword here is for de duplication)

3) Product Options

New dataset product options:

SELECT Product FROM Sales_Volume 

where Region = '${region}' and Product_Types = '${pdtypes}' 

Because product need to display according to different region and product types, region and product types filtering parameters need to be set (the distinct keyword here is for de duplication).

5. Set the data dictionary of the parameter control

1) Select the region drop-down box control, the component name is region, set the data dictionary as data query, and select the corresponding dataset region option. The actual value is the same as the displayed value, which is the owner region, as shown in the following figure:

2) Select the product types drop-down box control, the component name is product types, set the data dictionary as data query, and select the corresponding dataset product types option. The actual value is the same as the displayed value, which is the Product_Types as shown in the figure below:

3）Select the product drop-down box control, the component name is product, set the data dictionary as data query, and select the corresponding dataset Product option. The actual value is the same as the displayed value, and it is the Product, as shown in the following figure:

6. Effect preview

Save the template and click pagination preview. The effect is as follows:

Completed template, please refer to %fr_Home%/Webroot/WEB-INF/reportlets/Doc-EN/parameter/DB_Box_para.cpt

Click download template: 

Dd_Box_para.cpt