[Direct] Text component binding parameters

1.1 Version

1.2 Problem description

Because the amount of data in the original table is too large, it may sometimes cause the memory or disk to fill up. Therefore, it is usually necessary to use the where statement in SQL and filter conditions to control the amount of data. Adding a fixed value after the where statement will make the access inflexible. In this case, you can use parameters to dynamically control the amount of data accessed by BI, for example, obtain data from different customers.

There are two types of binding parameters for text components:

The first is that when the text filter component is empty, there is no data in the data table. You can enter the field you want in the text drop-down box. For example, enter "10.0" and press "Enter". The data table will automatically filter out the "CUSTOMERID" of "10.0" is as shown in the figure below:

The second is to filter the text by drop-down selection field, without manual input, and supports "select all", the effect is shown in the following figure:

1.3 Implementation ideas

Example 1:

1) Add a SQL dataset, and configure the "shop number" parameter in the SQL statement.

2) Use the "text drop-down filter component" to bind parameters in the dashboard.

Example 2:

1) Add a SQL dataset, and configure the "shop number" parameter in the SQL statement.

2) Add SQL dataset without configuring parameters.

3) Use the "text drop-down filter component" to bind parameters in the dashboard, and use the "shop number" field under the dataset without parameter configuration as the filter component drop-down option.

Note: Currently, only the text filter component and the date filter component provide the function of binding parameters.

2. Example 1: Manual input filter

2.1 Create SQL dataset

1) The data processing user selects "Data Preparation", enters the "package", and clicks "Add Table>SQL dataset", as shown in the figure below:

First, you need to create the "CUSTOMERID" parameter in the SQL dataset.

Set the table name as "customerinfo", and enter the SQL statement with parameters select * from CUSTOMER where CUSTOMERID in ('${ID}'), that is, set "CUSTOMERID" as a parameter and name it as "ID" , Click the parameter setting "Refresh", and select the default value of 10.0, click "OK" to save, as shown in the figure below:

Note 1: The default value can be selected arbitrarily and only affects the preview data.

Note 2: SQL parameter writing can refer to: SQL dataset parameter writing example.

If the "Parameter Type" is "Text", and you want to display multiple default values during preview, you can enter for example: 10.0','1.0, and the preview interface will display two corresponding data, as shown in the following figure:

Note: The format of multiple default values is: parameter value 1',' parameter value 2.

2.2 Create Dashboard

Create a component, make a data table, select "group table" for the chart type, and drag in the field, as shown in the following figure:

2.3 Filter component that only binds parameters

A new text filter component is added, as shown in the figure below:

Click "Binding parameter" and enter the 'ID' to perform fuzzy search and bind the "ID", as shown in the figure below:

Note: The "Binding parameter" button will appear only after ordinary users have the permission of a dataset with parameters. Since all the parameters obtained by the SQL dataset are in the bound parameter drop-down box, it is best not to use the same parameter name for the parameters.

2.4 Effect view

For details of the effect, please refer to section 1.2 of this article.

3. Example 2: Drop-down box selection filter

The filter component only binds parameters and can only be filtered by hand input. If you need a drop-down box, you need to bind the field to provide the content of the drop-down box. This section describes how to achieve the effect of selecting fields from the drop-down box while implementing binding parameters.

Implementation requirements: The table where the bound field is located cannot have the bound parameter "ID".

3.1 Create SQL dataset

3.1.1 Create SQL dataset with parameters

1) The data processing user selects "Data Preparation", enters the package, and clicks "Add Table> SQL Data set", as shown in the figure below:

First, you need to create the "CUSTOMERID" parameter in the SQL dataset.

Set the table name as "customerinfo", and enter the SQL statement with parameters select * from CUSTOMER where CUSTOMERID in ('${ID}'), that is, set "CUSTOMERID" as a parameter and name it as "ID" , Click the parameter setting "Refresh", and select the default value of 10.0, click "OK" to save, as shown in the figure below:

Note 1: The default value can be selected arbitrarily and only affects the preview data.

Note 2: SQL parameter writing can refer to: SQL dataset parameter writing example.

If the "Parameter Type" is "Text", and you want to display multiple default values during preview, you can enter for example: 10.0','1.0, and the preview interface will display two corresponding data, as shown in the following figure:

Note: The format of multiple default values is: parameter value 1',' parameter value 2.

3.1.2 Create SQL dataset without parameters

This step is mainly to obtain the complete "CUSTOMERID" data, so that all the customerid can be selected in the drop-down box.

Using the same data table, enter select * from CUSTOMER to create a SQL dataset without parameters, as shown in the following figure:

3.2 Create Dashboard

To create a component, select the "customerinfo" data table created in Section 3.1.1, and after selecting the "Group Table" for the chart type, drag in the fields, as shown in the following figure:

3.3 Filter component binding parameter fields

A new text filter component is added, as shown in the figure below:

First, bind the "ID" to the text component. At this time, it is the same as example 1, and manual input filtering can be realized, as shown in the following figure:

In order to display all customerid data in the drop-down box, it is necessary to use the customerinfo table without setting parameters and drag it into the filter component field column, as shown in the following figure:

Note 1: The "Binding parameter" button will appear only after ordinary users have the permission of a dataset with parameters.

Note 2: There is no need to drag the "CUSTOMERID" field of the CUSTOMER table with parameters into the text filter component, because the data from the database of the "CUSTOMER" table this time is the default value 10.0 set in the SQL dataset for the "CUSTOMERID", containing information of the cusotmerid . Therefore, the drop-down selectable value of the text box is only 10.0, excluding the field values of all CUSTOMERID.

3.4 Filter CUSTOMERID by filtering CUSTOMERNAME

At this time, the user may feel that using the drop-down box to filter the selected CUSTOMERID is the ID field, which is not easy to remember, and the existing data is a "CUSTOMERID" corresponding to a "CUSTOMERNAME", thus hoping to directly select the CUSTOMERNAME to filter, you can set as follows:

At this time, when filtering is performed, the customer name is filtered on the surface, but the customer number is actually filtered.

3.5 Effect view

For details, please refer to section 1.2 of this article.

4. Notice

There are two usages of filter components: the usage of parameter binding described above and the usage of ordinary filter components.

In "Example 2" by default, the function of the filter component is a parameter, that is, the control range of the filter component of the bound field can only filter the data table component made by the table "CUSTOMER" with parameters, as shown in the following figure:

If you want the filter component to meet the effect of example 2 and filter the "CUSTOMER" table without parameters where the binding field is located, you can go to the "Manage > System > General" and find the "BI Parameter" setting box, and turn on the "Parameter widges filtering takes effect", as shown in the figure below: