[Direct] Passing parameters in date interval filter component

1. Overview

1.1 Version Support

1.2 Problem

Because the amount of data in the original table is too large, it may sometimes cause the memory or disk to be full. Therefore, it is usually necessary to use a where statement and filter conditions to control the amount of data. The addition of a fixed value after the where statement will make the access inflexible. In this case, you can use parameters to dynamically control the amount of data accessed by BI, for example, fetch all data in a dynamic time period.

For example, we may select only records in 2010 May using the date interval component.

1.3 Solution

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

Use the "Date Interval Filtering Component" to bind parameters in the dashboard to filter out the data for the May of 2010.

2. Procedure

2.1 Add a SQL dataset

Select "Data Preparation", enter the business package, and click "Add Table> Add SQL Data Set", as shown in the figure below:

Name the dataset as Inventory(date). Enter the following SQL

select * from inventory where Date between '${start_time}' and '${end_time}'

Click "refresh", select the parameter type as "Date" and select the default value, click "OK", as shown below.

Note 1: The example is the SQL statement of the date interval filter component. For details on the writing method of other date types, please refer to: SQL Data Set Parameter Writing Example.

Note 2: The default value can be selected arbitrarily. The default value is empty, that is, all are selected.

2.2 Create a dashboard

Create a dashboard and use the fields in inventory(date) to generate a detailed table.

2.3 Bind parameters on the data interval filter component

Add the "date interval" filter component, select the binding parameters, and click OK to select the parameter. The selected parameter can be used for fuzzy search, as shown below:

2.4 Effect preview

See section 1.2