Dynamic Column by Functions

Version

Expected Effect

When a report contains many data columns but a relatively small data volume, you can display data using the dynamic column, as shown in the following figure.

Note:

This method is not supported when the row-based engine is enabled for the report.

Implementation Method

You can form a dynamic column using the drop-down checkbox widget and dataset functions.

Data Preparation

Create a general report, and create a database query ds1 with the SQL statement SELECT * FROM Sales_Volumn, as shown in the following figure.

Table Design

Design the table as shown in the following figure.

Insert =ds1.select(#0) into cell A2 to retrieve all row numbers from the dataset ds1, and set Expansion Direction to Vertical.

Insert =$colname into cell B1 to obtain the selected column name, and set Expansion Direction to Horizontal.

Insert =ds1.value(A2,B1) into cell B2 to fetch the value at the specified row and column in the dataset ds1, and set Expansion Direction to No Expansion.

Adding a Template Parameter

Choose Template > Template Parameter, add a text-type parameter, and rename it colname, as shown in the following figure.

Note:

If you want the report to display certain data columns by default, you can set default values for the parameter. For example, you can input Region,Salesperson in Default Value to make the report display the Region and Salesperson columns by default.

Adding the Parameter Widget

Enter the parameter panel, click Add All in the upper-right Component Setting area to generate the parameter widget, click the widget next to the label widget, and select Drop-down Checkbox Widget, as shown in the following figure.

Setting the Data Dictionary for the Parameter Widget

Select the drop-down checkbox widget (with Widget Name set to colname). On the Data Dictionary configuration page, set Type to Formula, enter TABLEDATAFIELDS("ds1") in Actual Value to obtain all the field names in the dataset ds1, and enter $$$ in Display Value to match actual values, as shown in the following figure.

Effect Display

PC

Save the template and click Pagination Preview. The following figure shows the effect.

Mobile Terminal

The following figure shows the preview effect on the DataAnalyst app and on the HTML5 terminal.

For details, you can download the template Dynamic Column by Functions.cpt.