Tutorial on Using Global Parameters in Direct Connection Dataset

Background

The database has a large amount of data, making each update slow. Secondary filtering is needed on the page. The goal is to query the latest data from the database based on the filter options entered on the page.

Implementation Method

1. Create global parameters as needed. There are 3 types: text, numeric, and date. The default format for date parameters is yyyy-MM-dd (e.g., 2020-08-18). To query a date range (e.g., 2020-07-01 to 2020-08-18), create two date parameters for start and end dates.

2. Create a database dataset. When writing the SQL statement, directly select the created global parameters; for existing datasets, edit the data model and select the global parameters when modifying the SQL. It is recommended to use global parameters in the WHERE clause, and multiple parameters can be referenced. Note: In some databases, date-type parameters may be recognized as strings and need to be converted to the correct format for comparison.

3. After the dataset is created and updated, create a card. If the card cannot preview data due to no data in the dataset, temporarily modify the global parameter default value on the right to ensure data is available for preview.

4. Create a filter on the page, associate the card and global parameter to use it.

Date filter: It is recommended to create a date filter, set the comparison operator according to the date field in the dataset SQL, and set the default date value. In the linkage page, click the card to be associated, check the card name and global parameter, and click "Done" to save. You can also use a parameter filter, but it does not show the comparison operator.

Text, numeric filter:

a. Create a selection filter, select another non-direct connection dataset, select the filter field, and associate the card and global parameter. Advantage: The selection filter can also link other cards on the page that do not use global parameters; disadvantage: You must prepare a dataset containing the filter options in advance.
b. Create a parameter filter, reference the text parameter used in the card, and choose to import filter options from another dataset or select "Text Input" to manually copy and paste filter options, or check "Custom, do not set options" (after saving, you need to manually input filter options for linkage). After saving, it will automatically link all cards on the page that use the parameter. Advantage: Flexible and easy to adjust; disadvantage: Cannot link cards that do not use global parameters.