[Direct] Year month interval filter component passing parameters

1. Overview

1.1 Version

1.2 Problem description

Because the amount of data in the original table is too large, sometimes it may lead to a memory or disk full situation, so it is usually necessary to use where statement, plus filter conditions to control the amount of data. The where statement followed by a fixed value will make the fetching inflexible, so you can use parameters to dynamically control the amount of data fetched by BI, such as fetching all the data within a dynamic time period.

For example, if you select the data for 2015 in the "Year Month Interval" filter component, the data for 2015 will be displayed in the data table, as shown in the following figure.

1.3 Implementation idea

1）Add the SQL data set and configure two parameters in the SQL statement: start time and end time.

2）Use the "Year Month Interval Filter Component" binding parameter in the dashboard to filter out the full year 2015 data.

2. Operation steps

Example data: "Internet_access_statistics_table" under the built-in DEMO

2.1 Add SQL dataset

1）Login to FineBI, select "Data Preparation", go to Business Package, and click "Add Table > SQL Dataset", as shown in the following figure.

2）Name the data set "Internet Access Data", select the data connection, and enter the SQL statement with parameters.SELECT * FROM Internet_access_statistics_table WHERE 1=1 <parameter> and TO_CHAR("date",'yyyymm') >=concat('${begin year}',case when cast('${begin month}' as tinyint)>9 then '${begin month}' else concat('0','${begin month}') end ) and TO_CHAR("date",'yyyymm') <=concat('${cut-off year}',case when cast('${cut-off month}' as tinyint)>9 then '${cut-off month}' else concat('0','${cut-off month}') end )</parameter>，Click Refresh, select the parameter type as "Date" and choose the default value, and click OK. As shown in the figure below.

Note 1: The example is the SQL writing method of year-month interval filter component, different database writing method is different, this article only provides the reference example, other types of SQL parameter writing method details see: SQL data set parameter writing method example.

Note 2: The default value can be chosen arbitrarily, and the default is empty, that is, choose all.

2.2 Create a dashboard

Create a new dashboard, use "Internet Access Data" and add a grouped table as shown below.

2.3 Filter component binding parameters

Add the "Year Month Interval" filter component, edit the "Year Month Interval" filter component, select the binding parameters, and click the "Set" button to select the parameters, as shown in the following figure.

2.4 Effect view

See Section 1.2 of this document for details.