[Direct Connection] Passing Parameters by Text Filter Component

Version

Problem

In some cases, memory or disk space may be exhausted if the original table contains a large amount of data. Therefore, you often need to use a WHERE clause with filtering conditions to control the amount of data processed. However, adding fixed values to the WHERE clause can make data retrieval inflexible. In this case, you can use parameters to control the amount of data retrieved by FineBI dynamically. For example, you can obtain all data within a dynamic time range.

Two methods are available for passing parameters by text filter components.

The first method is that no data will be displayed in the data table when the text filter component is empty. You can enter the required field value in the text box. For example, if you enter 11011 and press Enter, the data of the shop with the shop number 11011 will be displayed automatically in the data table, as shown in the following figure.

The second method is you select field values from the text drop-down list to obtain the required data. You do not need to enter field values in the text box, and all field values can be selected. The following figure shows the effect.

Implementation Method

Example one:

1. You can add a SQL dataset and configure the StoreID parameter in the SQL statement.

2. You can use the Text Drop-Down filter component to bind parameters in the dashboard.

Example two:

1. You can add a SQL dataset and configure the StoreID parameter in the SQL statement.

2. You can add a SQL dataset without configuring parameters.

3. You can use the Text Drop-Down filter component to bind parameters in the dashboard and set values of the Shop number field from the dataset without parameters as the options of the drop-down list in the filter component.

Note:

You can only use text filter components and time filter components to pass parameters currently.  

Creating a SQL Dataset

1. If both the Extracted Data and Direct-Connected Data functions are available at the same time, you need to select Direct-Connected Data from the data list. Select Public Data, click a business package, 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. You need to create the StoreID parameter in the SQL dataset first. Name the table Store Info and enter the SQL statement with a parameter SELECT * FROM Store_Sales_Statistics WHERE "Shop number" IN ('${StoreID}', namely setting Shop number as a parameter and naming the parameter StoreID. Click Refresh in Parameter Setting, set Default Value to 11011, and click OK to save the above setting, as shown in the following figure.

Note:

1. You can set any value as Default Value, which only affects the data preview.

2. For details about SQL parameter writing, see SQL Dataset Parameter Writing Example.

In some cases, you may want to display multiple default values during the preview when Parameter Type is set to Text. For example, if you enter 11011','11012 as Default Value, two records of data will be displayed on the preview page, as shown in the following figure.

Note:

The input format of multiple default values is Parameter Value 1','Parameter Value 2.

Creating a Group Table

Create a component, select Group Table in Chart Type, and drag the corresponding fields into the Dimension and Indicator bars, as shown in the following figure.

Binding the Parameter Only to the Filter Component

1. Add a dashboard and drag the created group table into the dashboard. Add a Text Drop-Down filter component to the dashboard, as shown in the following figure.

2. Tick Bind Parameter and bind the SQL parameter StoreID to the filter component on the setting page, as shown in the following figure.

Note:

If you are a general user, the Bind Parameter button will only appear after you are assigned the permission on a dataset with parameters. Since all parameters obtained from SQL datasets appear in the dropdown box of parameter binding, you are advised to use different names for different parameters.  

Effect Display

For details, see section "Problem."

For a filter component only bound to parameters, you can only filter data by manually entered fields. If a drop-down box is required, you need to bind a field to the filter component to provide the content for the drop-down box. This section introduces how to bind parameters and add a drop-down box to a component, which allows you to select field values from the drop-down list.

To achieve the expected effect, the table where the bound field is located cannot be the same table where the bound parameter StoreID is located.

Data Preparation

Creating a SQL Dataset with a Parameter

1. As designers, select Public Data, select a business package, click Add Dataset, and select SQL Dataset from the drop-down list, as shown in the following figure.

2. You need to create a StoreID parameter in the SQL dataset first.

Name the table Store Info, enter the SQL statement with a parameter SELECT * FROM Store_Sales_Statistics WHERE "Shop number" IN ('${StoreID}', namely setting Shop number as a parameter and naming the parameter StoreID. Click Refresh in Parameter Setting, set Default Value to 11011, and click OK to save the above setting, as shown in the following figure.

Note:

1. You can set any value as Default Value, which only affects the preview data.

2. For details about SQL parameter writing, see SQL Dataset Parameter Writing Example.

In some cases, you may want to display multiple default values during the preview when Parameter Type is set to Text. For example, if you enter 11011','11012 as Default Value, two records of data will be displayed on the preview page, as shown in the following figure.

Note:

The input format of multiple default values is Parameter Value 1','Parameter Value 2.

Creating a SQL Dataset Without Parameters

This step is to obtain all data of Shop number, which allows you to select all shop numbers from the drop-down box.

Enter SELECT * FROM Store_Sales_Statistics and create a SQL dataset without parameters using the same data table, as shown in the following figure.

Adding Data

Create an analysis subject and add two data tables to the analysis subject, as shown in the following figure.

Creating a Group Table

Create a component, select Group Table in Chart Type, and drag the corresponding fields into the Dimension and Indicator bars, as shown in the following figure.

Binding the Parameter and Field to the Filter Component

1. Add a dashboard and drag the created group table into the dashboard. Add a Text Drop-Down filter component to the dashboard, as shown in the following figure.

2. Tick Bind Parameter and bind the SQL parameter StoreID to the filter component on the setting page, as shown in the following figure.

3. To display all data of Shop number, you need to use the Shop number field in the Store Info (no parameter) table and drag the field into the Field bar in the filter component, as shown in the following figure.

Note:

1. If you are a general user, the Bind Parameter button will only appear after you are assigned the permission on a dataset with parameters.

2. In this case, you cannot drag the Shop number field from the Store Info table with the parameter StoreID into the filter component. If you do so, data retrieved from the database for the Store Info table will be the information of the shop with the shop number 11011 (default shop number set in the SQL dataset). Therefore, you can select only 11011 from the drop-down list instead of all field values of Shop number.  

Filtering Shop Number Through Shop Name

In this case, you may find it difficult to remember the store numbers for filtering data in the drop-down box. Additionally, since a one-to-one correspondence exists between Shop number and Shop name, you may prefer to directly select store names to filter the data.

You can click the dropdown triangle icon to set Actual Value. Drag the required fields into the Field and Actual Value bars separately, as shown in the following figure.

In this case, when you select shop names for filtering, the data will actually be filtered based on shop numbers.

Effect Display

For details, see section "Problem."

You can use the filter component in two methods, namely the parameter binding method introduced above and the method of using general filter components.

In example two, the function of the filter component is based on the parameter by default. In other words, the filter component bound to the Shop number field can only filter the data within the data table component created by the Store Info table with the parameter StoreID, as shown in the following figure.

In some cases, you may want the filter component to retain the effect in example two and also filter the data in the component (bound to the Shop number field) created by the Store Info (no parameter) table without parameters. You can choose System Management > System Setting > BI Parameter and enable SQL Parameter and Filter Function Effective for Widget, as shown in the following figure.