Overview
Expected Effect
In [Direct Connection] Passing Parameters by Text Filter Component, you can learn how to use the parameter bound to the filter component to dynamically control the data amount retrieved by FineBI from the directly connected database. In the document mentioned above, no data is displayed in the data table when the filter component is empty. Therefore, how to display all data in the data table when the filter component is empty?
For example, when no parameter value is selected from the drop-down list, all values in the data table are displayed in the component, as shown in the following figure.
Implementation Method
You can add a SQL dataset and configure the parameter Store_Nature in the SQL statement. Additionally, you need to include the parameter clause in the SQL statement to ensure that all values are selected when no value is provided for the parameter.
You can add a SQL statement without parameters. This step is mainly to obtain all the data of Store Nature, allowing you to select all values of Store Nature from the drop-down list of the filter component.
You can use the Text Drop-Down filter component in the dashboard to bind the parameter and set values of the Store Nature field from the dataset without parameters as the options of the drop-down list in the filter component.
Note:Procedure
Creating a SQL Dataset with a Parameter
1. If 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 folder to enter a business package, click Add Dataset, and select SQL Dataset from the drop-down list, as shown in the following figure.
Note:
2. Enter the SQL statement with a parameter SELECT * FROM Store_Sales_Statistics WHERE 1=1 <parameter> AND "Store Nature" in ('${Store_Nature}') and name the business package Store (parameter). Click OK to save the SQL dataset.
Note:The following explains the SQL statement:
where 1=1 represents that the condition is always true, avoiding errors caused by the redundancy of where if no subsequent parameter conditions exist.
<parameter> and Store Nature in ('${Store_Nature}') </parameter> represents the added Store_Nature parameter, which is used to retrieve all values of Store Nature if the intermediate parameter is empty.
Creating a SQL Dataset Without Parameters
This step is to obtain all data of Store Nature, which allows you to select all values of Store Nature from the drop-down list.
Create a SQL dataset without parameters using the SQL statement SELECT * FROM Store_Sales_Statistics, and name the table Store (no parameter), as shown in the following figure.
Adding a Detail Table
1. Add data to the subject, as shown in the following figure.
Create a component with the table Store (parameter), select Detail Table in Chart Type, and drag needed fields into the Dimension and Indicator bars, as shown in the following figure.
Setting a Text Filter Component
1. Add a dashboard and drag the created detail 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 Store_Nature to the filter component on the setting page, as shown in the following figure.
3. If you want to select different store natures in the filter component, drag the Store Nature field from the table without parameters into the filter component, as shown in the following figure.
Effect Display
In this case, when no value is selected in the filter component (indicating that the parameter is empty), all values of the Store Nature field (including Self-owned and Managed) are displayed in the table, as shown in the following figure.
If you select Managed from the drop-down list of the filter component, the detail table filters data with Shop Nature of Managed by passing the parameter to the SQL statement, as shown in the following figure.
