Dynamic Column by a Dataset Parameter

Version

Expected Effect

If you use functions to form a dynamic column, all data needs to be fetched. However, the performance may be deteriorated when only data in a few columns are selected from a large amount of data.

This document introduces an alternative method to form a dynamic column, which allows pagination querying for layered reports in case of a large amount of data and improves report performance, as shown in the following figure.

Implementation Method

You can form a dynamic column by defining a dataset parameter and dragging the data column to be displayed into corresponding cell.

Data Preparation

New General Report

1. Create a database query ds1 with the SQL statement SELECT ${col} FROM Sales_Volume. Set the default value of the dataset parameter col to * (string) to query all data, as shown in the following figure.

2. Create a database query ds2 with the SQL statement SELECT * FROM Sales_Volume LIMIT 1 to retrieve one data record (containing all field names) from the table, as shown in the following figure.

Report Design

Table Design

Since you are uncertain about the number of required fields, you need to drag all fields into the template during table design.

1. Table Header

Insert formulas from cell A1 to E1. The formulas convert the value of the parameter col into an array and return the 1st to 5th values from the array, respectively.

The following table describes the formulas.

The following figure shows the operation steps.

2. Table Content

Insert data columns from cell A2 to E2. Set Dataset to ds1, enter #1, #2, #3, #4, and #5 into Data Column respectively, set Data Setting to List, and set Expansion Direction to Vertical, as shown in the following figure.

Note:

#N specifies the index of the column in which data is located in the dataset. For example, #1 specifies data in the first column.

Report Border Setting

Select cells from A2 to E2, click Condition Attribute on the right panel, and click the  icon. In the Attribute area, click  and select Border from the drop-down list. Click Edit and External in sequence. In the Formula Condition area, set Type to Formula and enter the formula len($$$)!=0, as shown in the following figure. Through this formula, the border is displayed if the cell contains data.

Header Background Setting

Select cells from A1 to E1, click Condition Attribute on the right panel, and click the icon. In the Attribute area, click , select Background from the drop-down list, click Edit, select a blue color, and click OK. Click , select Border from the drop-down list, and click Edit External in sequence. In the Formula Condition area, set Type to Formula and enter the formula len($$$)!=0. Through this formula, the background and border are displayed if the cell contains data. The steps are similar to those in the "Report Border Setting" section, and the following figure shows the setting page.

Parameter Widget Setting

Click the parameter panel and click Add All. Click the widget named col appearing on the parameter panel and select Drop-down Checkbox Widget, as shown in the following figure.

Select the drop-down checkbox widget, set Widget Value to String, and clear the default value. Click the  icon in Data Dictionary, set Type to Formula, and enter the formula TABLEDATAFIELDS("ds2") in Actual Value, as shown in the following figure.

Note:

The default value of the drop-down checkbox widget on the parameter panel must be empty. Otherwise, not all columns can be retrieved.

Click Event Setting

If the parameter is empty, all columns are retrieved by default when you click the Query button.

Click the Query button on the parameter panel, select the Event tab, click the  icon, select Click from the drop-down list, add the parameter a with the value set to the formula TABLEDATAFIELDS("ds2") to retrieve all filed names, and enter the following JavaScript codes, as shown in the following figure.

The JavaScript codes are as follows:

var Widget = this.options.form.getWidgetByName("col");  var value =Widget.getValue(); //Get the actual value of the widget.   
if(value==''||value==null)
{   /*Get the text widget on the parameter panel and check wh
ether
its value
is empty or null
before assignment.*/   Widget.setValue(a);     _g().parameterCommit();     }

Effect Display

PC

Click Pagination Preview. When you select the required data columns and click Query, the data in the corresponding columns will be displayed, as shown in the following figure.

Mobile Terminal

The report can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

You can download the template Dynamic Column by a Dataset Parameter.cpt.