Defining Parameters when Adding SQL Datasets

This document describes how to name and define parameters when adding SQL datasets.

For details about the specific usage of parameters, see [Direct Connection] Transferring Parameters Through the Jump Function and [Direct Connection] Transferring Parameters Through the Text Filter Component.

Naming Rules

1. The name cannot start with a number or the $ symbol.

2. The variable name can only consist of letters (from a to z and from A to Z), digits (from 0 to 9), underscores (_), and at symbols(@).

3. The variable name cannot contain special characters (such as ?, *, ., and -) and spaces.

4. The variable name cannot contain reserved words in the programming language, such as lowercase reserved words like true and false. However, the capitalized TRUE and FALSE can be utilized.

Classic Naming Rules

1. Hungarian Notation

In the Hungarian notation, the name of a variable (made up of several characters) indicates its data type.

The basic rule is variable name = property + type + object description.

For example, all variable names starting with i (short for int) represent variables of the int type. All variables starting with s (short for string) represent variables of the string type.

2. Camel Case

As its name suggests, the Camel case refers to the practice of naming variables and functions by combining uppercase and lowercase letters.

Similar to the Pascal case though, the Camel case requires the first letter to be lowercase, for example, userName. The Camel case is known as the camel hump-shaped name.

3. Pascal Case

What differs between the Pascal case and the Camel case is that the Pascal case requires the first letter to be uppercase, for example, UserName.

Example

Defining Parameters Without Formulas

When adding SQL datasets, define parameters with the where formula and filter the required data to shorten the data retrieval time, for example, SELECT * FROM Sales where Region = '${Region}'.

Defining Parameters with Formulas

In SQL datasets, filter conditions can be dynamically generated with the parameter macro ${}. ${} can be concatenated with the SQL statement to form the final query statement in FineBI, which is then passed to the database for execution. Multiple parameter macros ${} can be used.

1. For example, enter the SQL query statement SELECT * FROM 'Retail_Store Dimension' where ShopNature in ('${ShopNature}') and ShopStyle in ('${ShopStyle}').

The ShopNature and ShopStyle parameters are set as shown in the following figure.

2. In addition to adding built-in function names and constants in FineBI to ${}, you can also add custom parameters to ${}.

For example, enter the following SQL statement.

SELECT * FROM User_Portrait_Analysis WHERE 1=1 ${if(len(Gender) == 0,"","and gender = '" + Gender + "'")} ${if(len(City) == 0,"","and area = '" + City + "'")}

IF is a built-in formula in FineBI, and Gender and City are custom parameters. If the value of the City parameter is not null (namely, len(City)!=0), for example, if the value of City is New York, and the area needs to be New York  (namely, and area='New York'), concatenate the and area='New York' statement to the IF statement to form the following SQL statement.

SELECT * FROM User_Portrait_Analysis where 1=1 and gender='M' and City='New York'

Note:

You can use all built-in FineBI formulas (for example, IF), within ${}. For details, see function.

1. For the character parameter, single quotation marks are required, for example, '${adce}'. For the numeric parameter, single quotation marks are not needed, for example, ${adce}. In the above statement, adce repersents a parameter.

2. To reference a parameter value, prefix the parameter with $, for example, $adce.