[Direct Connection] Transferring Parameters by Year-Month Interval Component

Version

Problem

In some cases, memory or disk space may be exhausted if the original table contains an excessive amount of data. Therefore, you often need to use a WHERE statement with filter conditions to control the amount of data processed. However, adding fixed values to the WHERE statement 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.

If you select the time range from January to December in 2015 in the Year-Month Interval filter component, the data table will display data in 2015, as shown in the following figure.

Implementation Method

1. You can add a SQL dataset and configure four parameters in the SQL statement, namely Start_Year, Start_Month, End_Year, and End_Month.

2. You can use the Year-Month Interval filter component in the dashboard to bind parameters and obtain data of the entire year in 2015 by filter.

Sample data: Internet_Access_Statistics.xlsx.

Download the sample data and upload the downloaded data to the SQL Server.

Adding 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. Click Add Dataset and select SQL Dataset from the drop-down list in Public Data, as shown in the following figure.

Note:

Only BI data processing users can create SQL datasets.

2. Name the dataset Internet Access Statistics, select the data connection, and enter the SQL statement with parameters SELECT * FROM Internet_Access_statistics WHERE strftime('%Y%m',"Statistical date") >= (${Start_Year} || CASE WHEN ${Start_Month} > 9 THEN ${Start_Month} ELSE '0' || ${Start_Month} END) AND strftime('%Y%m',"Statistical date") <= (${End_Year} || CASE WHEN ${End_Month} > 9 THEN ${End_Month} ELSE '0' || ${End_Month} END). Click Refresh, select Date in Parameter Type separately for the four parameters, set the default values, and click OK, as shown in the following figure.

Note:

1. The example presents the SQL statement for the Year-Month Interval filter component. Different databases have different statements. This document only provides a reference example. For details about SQL parameter statements for other time filter components, see SQL Dataset Parameter Writing Example.

2. You can set any default value. If you set the default value to be empty, all values are selected.

Creating a Group Table

Add a component and create a group table, as shown in the following figure.

Binding Parameters to the Filter Component

1. Add a dashboard and drag the created group table into the dashboard. Drag the Year-Month Interval filter component into the dashboard, as shown in the following figure.

2. Tick Bind Parameter and bind SQL parameters to Start Time on the setting page. Specifically, bind Start_Year to Year and Start_Month to Month. Complete the similar setting for End Time, as shown in the following figure.

Note:

Since all parameters obtained from SQL datasets appear in the drop-down list of parameter binding, you are advised to use different names for different parameters.  

Effect Display

For details, see section "Problem."