[Direct] Use of dynamic columns in table components

1. Overview

1.1 Expected effect

Users can dynamically switch columns as needed for flexible queries. The metrics data under three dimensions can be switched out.

For example, the text drop-down control switches out "User ID", "Statistics Date" and "Views Statistics" of "Access Platform".

1.2 Implementation idea

Dynamic column switching can be achieved using the filter component in conjunction with dynamic parameters. For the use of filter component parameters, see Filter component parameter usage.

2. Example

Use BI Demo data.

2.1 Add dimension name dataset

1）It is required to be able to drop down the text to appear the dimension name, create a new "Dimension name" SQL dataset, and get the names of all fields of the table by SQL function as follows.

Enter the SQL statement.SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name='Internet_access_statistics_table'

2）Turn on real-time data for the "Dimension name" dataset, please see Real-Time Data for how to turn it on.

2.2 Adding real-time parametric SQL datasets

1）Add the SQL data set named "Dynamic Parameters" and enter the SQL statement.

select access ,<parameter> ${col} </parameter> as dynamic_column from Internet_access_statistics_table

or  SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name='Internet_access_statistics_table'. As shown in the figure below.

2）Set the parameter type to text and the default value to manually enter a dimension name of type text, as shown in the following figure.

Note: If there is a field of date type in the dimension, do not set it as the default value.

3）Turn on real-time data for the "Dynamic Parameters" dataset, see: Real-Time Data for how to turn on real-time data

2.3 Create a dashboard

Note: Add the chart components that require dynamic columns first, then add the filter component.

1）Create a new dashboard and select the group table, which will look like the following.

2）Add a text drop-down filter component, as shown below.

3）Set up the text drop-down filter component with the selected value COLUMN_NAME for the field, select dynamic parameter col for the parameter, and click OK, as shown in the following figure.

Note: To select a default value for the filter component (step 7 in the figure below). If it is a null value, the dynamic column of the form component before the filter is not null, it will display an error report, and will only return to normal status after the filter component selects a value.

4）If you only need to switch between the dimensions of "Statistics Date", "Access Platform" and "User ID", you can filter the field "COLUMN_NAME" as shown below.