[Direct Connection] Using Dynamic Columns of the Table Component

Expected Effect

You can dynamically switch columns and perform flexible queries as needed. And you can switch to view indicator data in three dimensions.

For example, switch to User_ID, Date, and Access Platform for Browse Volume through Text Drop-down Filter Component.

Implementation Method

You can switch columns dynamically with the coordination among filter components and dynamic parameters.

You can download Internet_Access_Statistics.xlsx.

Adding a Dimension Name Dataset

1. If you have the Extracted Data and Direct-Connected Data functions at the same time, you need to select Direct-Connected Data from the data list. Then click Add Dataset and select SQL Dataset from the drop-down list, as shown in the following figure.

Note:

Only designers can create SQL datasets.

2. Name the table Internet_Access_Statistics and enter the SQL statement: 

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name='Internet_Access_Statistics'

Adding a SQL Dataset with Direct-Connected Parameters

1. Add a SQL dataset, name it Dynamic Parameter, and enter the SQL statement:

select Browse_Volume ,<parameter> ${col} </parameter> as Dynamic_Column from Internet_Access_Statistics

Set Parameter Type to Text and Default Value to the name of a text type dimension, as shown in the following figure.

Note:

If there is a date type field in the dimension, do not set it to the default value.

2. Add the table to the analysis subject.

Creating a Group Table

Note:

Add the chart component that requires dynamic columns before adding the filter component.

Create a group table with the data of Dynamic Parameter and drag the fields into the analysis area, as shown in the following figure.

Binding the Parameter for the Filter Component

1. Add a dashboard and drag the created group table into the dashboard. Then, add Text Drop-down Filter Component, as shown in the following figure.

2. Select Bind Parameter, click the icon, select col under SQL Parameter(1) < Dynamic Parameter, and click OK. Besides, choose COLUMN_NAME under All Fields < Dimension Name < Field and click OK.

Note:

Choose a default value for the filter component. If the value is null, the value in the dynamic column of the table component before filtering is null. As a result, an error will occur till the filter component chooses the value.

3. You can filter COLUMN_NAME if you just need to switch among Date, Access_Platform, and User_ID.

Effect Display

You can switch the dimensions by changing the value in the text drop-down list, as shown in the following figure.