[Direct Connection] Passing Parameters by Date 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 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 volume of data retrieved by FineBI dynamically. For example, you can obtain all data within a dynamic time range.

If you select data in 2015 in the Date 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 two parameters in the SQL statement, namely start_time and end_time.

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

This document takes the Internet Access Statistics table as an example.

You can download the sample data Internet_Access_Statistics.xlsx.

Adding a SQL Dataset

1. If both the Extracted Data and Direct-Connected Data functions are available, 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.

2. Name the dataset Internet Access Statistics, select the data connection, and enter the SQL statement with parameters SELECT * FROM Internet_Access_statistics WHERE "Statistical date" BETWEEN '${start_time}' and '${end_time}'. Click Refresh, select Date in Parameter Type separately for the two parameters, set the default values, and click OK, as shown in the following figure.

Note:

The example presents the SQL statement for the Date Interval filter component. For details about SQL parameter statements for other time filter components, see SQL Dataset Parameter Writing Example.

Creating an Analysis Subject

Create an analysis subject, add a component, and create a group table with the Internet Access Statistics dataset, as shown in the following figure.

Binding Parameters to the Filter Component

1. Add a dashboard, and drag the component you created to the dashboard, as shown in the following figure.

2. Drag the Date Interval filter component to the dashboard. Tick Bind Parameter and bind the SQL parameter start_time to Start Time on the editing page of the filter component, as shown in the following figure.

3. Bind the SQL parameter end_time to End Time and click OK, as shown in the following figure.

Effect Display

For details, see section "Problem."