Getting Started with Parameter Queries

Expected Effect

The main function of parameters is to enable real-time interaction between users and data, that is, to filter data. We can use parameters in a variety of situations, such as referencing parameters in cells for dynamic titles, displaying different values based on parameter values, and so on. The following figure shows the effect.

Implementation Roadmap

Set template parameters and use parameter widgets on the parameter panel to filter data.

Template Preparation

1. Create a general report, and create a dataset ds1 with the SQL statement  SELECT * FROM Sales_Volume.

2. Choose Template > Template Parameter. In the Template Parameter dialog box, click the  button to add a parameter.

3. Double-click the parameter name on the left and rename it Region. Set Default Value to California on the right, and click OK.

4. Merge cells A1-C1 and add the formula: $Region + " " + "Sales Volume" to display a dynamic title consisting of the selected region name followed by Sales Volume, such as California Sales Volume, as shown in the following figure.

5. Enter Region, Salesperson, and Sales_Volume in cells A2, B2, and C2, respectively. Drag the corresponding data columns from dataset ds1 into cells A3, B3, and C3, respectively, and adjust the cell sizes.

6. Select cell C3. Choose Cell Element > Basic, set Data Setting to Summary, and then select Sum.

Widget Settings

Adding Widgets

1. Click the  icon to enter the widget editing panel, and then click Add All in the Widget Setting panel on the right.

2. Set the widget to a drop-down box widget.

Setting Widgets

1. Select the label widget and set Widget Value to Region:.

2. Select the drop-down box widget and set Widget Name to Region. In the Data Dictionary window, set Type to Data Query, Dataset to ds1, and Actual Value and Display Value to Region. 

Note:

The drop-down box widget name must match the template parameter name.

Adding the Filter

Double-click cell A3 and add a filter condition that sets Region equal to the parameter $Region, so that the cell data is filtered based on the region selected from the drop-down box.

Effect Preview

PC

Save the template and click Pagination Preview. The effect is shown in the following figure.

Mobile Terminal

The effect is shown in the following figure.

Download the template: Parameter Query.cpt