Common Parameter Application

1) Combining parameters and parameter widgets can realize data query.

2) Typical data query scenarios include:

a) Parameter linkage: The selected option in one drop-down box can control the options in another drop-down box.

b) Fuzzy query: The result data contains the key of fuzzy query.

c) Auto query: The data is filtered automatically after the value of widgets are changed.

d) Dynamic columns: The columns to be displayed can be customized.

1. Create dataset [ds1] with 3 parameters

1) Create a new DB query dataset.

2) Input the SQL statement:

SELECT * FROM SIndent
WHERE Owner_region = '${region}'
AND Owner_province = '${province}'
AND Owner_city = '${city}'

3) Refresh the parameters and set the default value (so you can preview the dataset conveniently)

2. Create dataset [region] to query all regions

1) Create a new DB query dataset.

2) Input the SQL statement:

SELECT Owner_region FROM SIndent

3. Create a dataset [province] with a parameter to query provinces in a certain region

1) Create a new DB query dataset.

2) Input the SQL statement:

3) Refresh the parameters and set the default value

SELECT Owner_province FROM SIndent
WHERE Owner_region = '${region}'

4. Create a dataset [province] with a parameter to query cities in a certain region and a certain province

1) Create a new DB query dataset.

2) Input the SQL statement:

SELECT Owner_city FROM SIndent
WHERE Owner_region = '${region}'
AND Owner_province = '${province}'

3) Refresh the parameters and set the default value

1. Create drop-down boxes

1) Click  button to edit the parameter pane.

2) One the right panel, click parameters [region], [province] and [city] one by one to create widgets with the same names.

3) After creating a widget with the same name, click    button and select widget type as drop-down box.

4) You can find the widgets from the widget list in the right pane, and the Widget Value (i.e. the default value) equals to the default value of parameter with the same name.

Note

• When the widget name is the same as the parameter name, they are bound together, and therefore the widget can be used to filter data.

• When creating a widget, the widget value equals to the default value of parameter with the same name; yet after the creation, the default value of parameter no longer controls the widget value, and can be customized in the right panel.

2. Set data dictionary for drop-down boxes

Set the data dictionaries for [region], [province] and [city] respectively.

Note

• The Display Value defines the options in the drop-down box while the Actual Value defines the returned values to the parameter that are used to filter data.

3. Move widgets

1) Click any widget and move it directly.

2) To move multiple widgets at the same time, press ‘Ctrl’ or ‘Shift’ and select the widgets to be moved.

4. Resize widgets

Click any widget, and you can resize it by dragging the border or enter the  size in the right panel.

5. Set Display Position

Click [para] in the right panel and set Display Position as [Center].

6. Preview the effect of the parameter pane

1) The widget values are displayed in the drop-down boxes directly.

2) You can try out the effect of parameter linkage:

a) Select ‘Central China’ for [region], and then the options in [province] are provinces in this region.

b) Select ‘Hubei Province’ for [province], and then the options in [city] are cities in this province.

1. Drag data columns into cells

1) Click  button to edit the report body.

2) Drag all data columns in [ds1] to B3~Q3.

3) Make Row 3 higher.

2. Input table headings and report title

1) Input table headings in B2~Q2.

2) Merge B1~Q1, and insert formula: "Orders belonging to " + $city + ', ' + $province + ', ' + $region

1. Optimize the report style

1) Make B1~Q3 center-aligned and enlarge the report title.

2) Set the color of the report title and table headings using #455075, and make them bold

3) Set the color of data columns using #7f879f.

4) Add inner and outer borders for B2~Q3 and using #f2f4f8.

5) Set the background color for B2~R2 using #eef1f8.

2. Optimize the style of parameter pane

1) Make the text in the 3 label widgets bold, and set text color as #6c7591.

2) Click [para] and set the background color as white.

3. Click [Data Analysis] Preview

1) Click [Query] and you can view the data as the result of being filtered by the 3 parameters.

2) Select other options for [region], [province] and [city]. After query, the report title changes accordingly.

1. Change the query statement of [ds1]

1) Change the SQL to:

SELECT * FROM SIndent
WHERE Owner_region = '${region}'
AND Owner_province = '${province}'
AND Owner_city = '${city}'
AND ( Order_date >= '${start}' AND Order_date <= '${end}')

2) Refresh the parameters. Select the type of [start] and [end] as [Date] and click to select their default values.

2. Create date widgets in the parameter pane

1) Edit the parameter pane and make it higher.

2) In the right panel, click [start] and [end] one by one to create widgets with the same names

3) After the creation, click  button and select the widget type as [Date]

3. Set widget values

1) The widget value equals to the default value of parameter with the same name and can be changed in the right panel.

2) You can choose the format of date, and the format should agree with the format in the dataset (i.e. yyyy-MM-dd in this example)

4. Change the position, size and style of widgets

5. Click [Data Analysis] Preview

You can filter data by date.

1. Change the query statement of [ds1]

1) Change the SQL to:

SELECT * FROM SIndent
WHERE Owner_region = '${region}'
AND Owner_province = '${province}'
AND Owner_city = '${city}'
AND ( Order_date >= '${start}' AND Order_date <= '${end}')
AND Owner_name LIKE '%${name}%'

2) Refresh the parameter and set the default value.

2. Create a text widget in the parameter pane

1) Edit the parameter pane and make it higher.

2) In the right panel, click [name] to create a widget with the same name.

3) After the creation, click  button and select the widget type as [Text].

3. Set the widget value

1) The widget value equals to the parameter with the same name.

2) You can change the widget value in the right panel.

4. Change the position, size and style of widgets

5. Highlight the key of fuzzy query in the query results

1) Edit the report body and define Formula Display for the cell where exists data column [Owner_name] (i.e. J3).

2) Input the following in the formula definition panel: replace($$$,$name,"<font color='#F79482'>"+$name+"</font>").

3) Set the way of [Show Content] as [Display by HTML] for J3.

6. Click [Data Analysis] Preview

1) Fuzzy query can be used to query owners and the query key is highlighted in orange in the result.

2) Not input any text to query all data.

VIII. Add a radio group for multiple choice

1. Change the query statement of [ds1]

1) Change the SQL to:

SELECT * FROM SIndent
WHERE Owner_region = '${region}'
AND Owner_province = '${province}'
AND Owner_city = '${city}'
AND ( Order_date >= '${start}' AND Order_date <= '${end}')
AND Owner_name LIKE '%${name}%'
AND Paid_or_not = '${paid_or_not}'

2) Refresh the parameter and set the default value.

2. Create a radio group widget in the parameter pane

1) Edit the parameter pane.

2) In the right panel, click [paid_or_not] to create a widget with the same name.

3) After the creation, click  button and select the widget type as [Button Group].

3. Set the data dictionary for the widget

4. Change the position, size and style of widgets

5. Click [Data Analysis] Preview

Click to download the sample template: 

Parameter Query 1.cpt

1. Change the query statement of [ds1]

1) Change the SQL to:

SELECT * FROM SIndent
WHERE 1=1 
${if(len(region) == 0, "", "AND Owner_region ='" + region + "'")}
${if(len(province) == 0, "", "AND Owner_province ='" + province + "'")}
${if(len(city) == 0, "", "AND Owner_city ='" + city + "'")}
${if(len(start) == 0, "", "AND Order_date >='" + start + "'")}
${if(len(end) == 0, "", "AND Order_date <='" + end + "'")}
${if(len(name) == 0, "", "AND Owner_name LIKE'%" + name + "%'")}
${if(len(paid_or_not) == 0, "", "AND Paid_or_not ='" + paid_or_not + "'")}

2) Refresh the parameters and set the default values. You can set default values for only a part of parameters.

Note

• WHERE 1=1 makes the condition always true

• len(parameter) == 0 becomes true when the parameter value is null

• ${if(len(parameter) == 0,"","AND dataColumnName = '" + parameter + "'")} means:

• When the parameter value is null, no filter is applied and all data are queried.

• When the parameter value is not null,  the SQL becomes SELECT * FROM tableName WHERE 1=1 AND dataColumnName='${parameter}'

2. Change the query statement of [province] and [city]

1) Change the SQL of [province] as:

SELECT Owner_province FROM SIndent
WHERE 1=1 
${if(len(region) == 0, "", "AND Owner_region ='" + region + "'")}

2) Change the SQL of [city] as:

SELECT Owner_city FROM SIndent
WHERE 1=1
${if(len(region) == 0, "", "AND Owner_region ='" + region + "'")}
${if(len(province) == 0, "", "AND Owner_province ='" + province + "'")}

3. Clear the default values of all widgets

1) For drop-down boxes, the text widget and the button group, clear the Widget Value directly.

2) For date widgets, use [Formula] to define Widget Value and input :“”

4. Display data before clicking the [Query] button

Click [para] and uncheck [Display Nothing Before Query].

5. Click [Data Analysis] Preview

1) Data are displayed directly. Since all widget values are null, all data are queried.

2) Click the drop-down box to select [region] and not select [province], and then options in [city] are all cities in the selected region.

1. Add JavaScript event for all widgets

1) Edit the parameter pane.

2) Select the widget and select [Event] tab in the right panel.

3) Click  to add an event.

a) For drop-down boxes, the date widgets and the text  widget, select the event as [After Editing].

b) For the radio group, select the event as [State Change].

4) Input the following in the JavaScript definition box：_g().parameterCommit();

2. Click [Data Analysis] Preview

1) Input "Li" in the text box and press ‘Enter’，click "No" in the radio group，and click the drop-down boxes to select [province] and [city].

2) Every time a widget value changes, the data below are filtered automatically.

Click to download the sample template:

Parameter Query 2.cpt                                                         

1. Add a template parameter [col]

1) Choose [Template] > [Template Parameter].

2) Click  button to add a parameter and double click to rename it to ‘col’.

2. Create a drop-down checkbox

1) Edit the parameter pane.

2) Click [col] in the right panel to generate a widget with the same name.

3) Click the  button and choose drop-down checkbox.

3. Set the data dictionary

1) Set the data dictionary for [col] and select the type as [Formula].

2) Click the formula definition box after [Actual Value], and input: TABLEDATAFIELDS("ds1").

3) Leave [Display Value] as undefined, so the display value will be the same with the actual value.

4. Set the default value

1) Select the type of Widget Value as [Formula].

2) Click the formula definition window and input: TABLEDATAFIELDS("ds1"), and this formula returns all column names of [ds1].

5. Change the position, size and style of widgets

6. Clear the report content

1) Edit the report body.

2) Enter [shift] and select Column B~Q. Right-click the column name and select [Delete Column].

7. Set the report title

Insert a formula in B1: "Orders belonging to " + $city + ', ' + $province + ', ' + $region.

8. Set the table headings

1) Insert a formula in B2: split($col, ",").

2) Set the Expand Direction as [Horizontal].

Note

• split($col, ",") splits the values in [col] using commas and return an array which stores the selected column names.

• After horizontal expansion, each column name occupies a cell and make a row of table headings.

9. Set the row numbers

1) Insert a formula in B2: ds1.select(#0), and this formula returns all row numbers in [ds1].

2) Set the Expand Direction of A3 as [Vertical].

10. Set the data

Insert a formula in B3: ds1.value(A3, B2), and this formula returns the data of [ds1] with its row number defined by A3 and column number defined by B2.

11. Make the row number invisible using conditional formatting

1) Select B3 and click [Conditional Formatting] > [+] in the right panel.

2) Click [+] and choose [Column Width]. The column width is 0MM by default. No need to set any condition. This conditional formatting makes the width of column A to be zero when being previewed.

 11. Optimize the style

1) Make B1~B3 center-aligned and enlarge the report title .

2) Set the color of the report title and table headings using #455075, and make them bold.

3) Set the color of B3 using #7f879f.

4) Add inner and outer borders for B2~B3 and using #f2f4f8.

5) Set the background color for B2 using #eef1f8.

12. Click [Data Analysis] Preview

All columns are displayed by default and you can select the columns to display in the drop-down checkbox.

Click to download the sample template:

Parameter Query 3.cpt

1. Delete the template parameter

2. Add a dataset parameter in [ds1]

1) Change the SQL to:

SELECT ${col} FROM SIndent
WHERE 1=1 
${if(len(region) == 0, "", "AND Owner_region ='" + region + "'")}
${if(len(province) == 0, "", "AND Owner_province ='" + province + "'")}
${if(len(city) == 0, "", "AND Owner_city ='" + city + "'")}
${if(len(start) == 0, "", "AND Order_date >='" + start + "'")}
${if(len(end) == 0, "", "AND Order_date <='" + end + "'")}
${if(len(name) == 0, "", "AND Owner_name LIKE'%" + name + "%'")}
${if(len(paid_or_not) == 0, "", "AND Paid_or_not ='" + paid_or_not + "'")}

2) Refresh the parameter and set the default value as: *.

3. Click [Data Analysis] Preview and the effect is the same

Using this method will improve the efficiency when the data volume is extremely large.

Click to download the sample template:

Parameter Query 4.cpt