In business scenarios, you may need to place an analysis result into a text component (such as a daily report) or extract data to include in a component title (such as extracting the best employee names for the current statistical period). By using variables, you can reference data analysis results in a component.
Business Intelligence (BI) supports 3 scopes of variables as follows:
Page variables: The scope is the current page. Page variables do not take effect when referenced on other pages. They are the most commonly used type of variables.
Project variables: The scope is any page in the current project. Project variables do not take effect when referenced in other projects. They are mainly used to define the shared information of a project, such as the success metric of a project.
System variables: The scope is all pages in the current organization. System variables are mainly used to define the shared information of an organization, such as obtaining usernames for watermark display scenarios.
Edition support: Basic Edition and Professional Edition.
This document introduces the following operations (using page variables as an example):
Creating Variables
Referencing Variables
Linking Variables
Formatting Variable Results
Syntax Description
Creating Variables
Follow the steps below to create a variable:
1. Entry: Go to the console > Page > Edit > Variable
.
2. Defining Variable Information
Click "+Add" to add a variable.
Enter the reference name: The new variable reference name "emp_total" is used to count the total number of employees. The reference name is the most important parameter and should consist of English letters and underscores. When calling the variable, use this name for reference. Although you can assign a custom variable name for easier identification during editing, you should still use the reference name when referencing the variable.
Value type: It specifies the method of obtaining a variable value. You can enter a static value, but the most commonly used method is "quick analysis".
3. Setting the Variable Value
Through "quick analysis," you can obtain chart analysis results, and the operations are similar to chart editing. The following example shows how to obtain the current number of employees:
3.1 Select the data table: Select the variable source data table.
3.2 Select a field for analysis:
In this example, the number of employees can be obtained by counting the "name" of employees.
Drag and drop the "name" field into the metric analysis box.
Select aggregate > count after deduplication to obtain the total number of employees.
3.3 Analyze: Click Analyze to obtain data results. The results can be displayed in a table or JSON format.
Note:
By default, the data in the first column of the first row is taken as the variable data result. If you changed the data table or modified "result formatting" earlier, variable errors may occur. In this case, switch to the "Result Formatting" tab and confirm whether the row and column are correct. You can format the result by yourself. For details, see Processing Variable Results.
Referencing Variables
Variables can be used in various scenarios, such as inserting variables in a text component or a chart title. Variables are referenced using double curly braces, such as "{{emp_total}}".
1. Inserting a Variable in a Title
1.1 Open the target page, click the Edit component, and set the component title.
1.2 Insert variables: In the style panel, find the title settings, and enter the variable reference name "{{EPL_Num}}" at the location where the title needs to be displayed.
1.3 View the variable parsing result after saving.
2. Inserting a Variable in a Text Component
2.1 Create a text component in the canvas.
2.2 Enter text content. In the part to be replaced, insert a variable, then select the page variable "{{EPL_Num}}".
2.3 Alternatively, directly enter the variable content with curly braces, such as "{{EPL_Num}}".
2.4 Save the settings and check whether the variable is parsed. Set the text style, such as bold.
Note:
If variable parsing fails, the variable will be displayed as text, such as "{{EPL_Num}}".
To set the style for a variable, you should select a complete variable reference, such as "{{EPL_Num}}", instead of "{{emp". Otherwise, the variable cannot be parsed.
Linking Variables
In scenarios with filters, we often need to analyze variables based on filter results. For example, when the filtered department is "test group", we expect to display only the number of employees in the "test group" rather than the number of employees in all departments. In this case, we need to use the filter linkage variable feature.
1. Create a filter to select the department.
2. Filter linkage variable settings: Select the linked variable "emp_total" and associate it with the "department" field.
3. Save the settings. Select "front-end development team" under "department", and "emp_total" will display the real-time data values.
Processing Variable Results
After quick analysis processing, we obtain a "table" result (response). You can obtain data values from the "table" by specifying rows and columns. The principle is shown in the figure below.
Through result formatting, we can also set the returned result data and format.
Entry: Click Result Formatting to go to format editing. By default, the result data in the first column of the first row is obtained.
Format editor area: This is the main operation area. You can enter formatting functions to process the results.
Reference function area: The system provides commonly used operation functions. You can locate the cursor at the text position in the editor, then click a function to insert it at the cursor position.
Result output area: You can click Preview to view the current output data result in the result output area.
The following describes the result formatting methods for variables in several common scenarios:
Obtaining Data of a Specific Cell (Such as Obtaining the Performance Value of the Top-Performing Employee)
Text Formatting (Such as Obtaining the Last Name of an Employee)
Numeric Formatting (Such as Formatting a Number to Display 2 Decimal Places)
Time Formatting (Such as Displaying Only the Current Month and Day in a Calendar)
Looped Processing (Such as Generating a Department Ranking List)
Judgement Processing (Such as Marking in Red When the Period-Over-Previous Period Value Is Less Than 0)
1. Obtaining Data of a Specific Cell
Final effect:
Operation steps:
Add a text component to the canvas: Set the corresponding style, and the numeric part uses the variable reference "{{emp_max}}".
Add a variable: Open the variable panel, add a page variable named "emp_max", then set the dimension and metric. Sort the output values in descending order to easily obtain the top three rows.
Format the result: Open the result formatting panel, and obtain the data in the "index_key_H_1" column (the employee output value column) of the third row.
Click Preview to view the result. The result is the value in the output value column of the third row.
2. Text Formatting (Such as Obtaining the Last Name of an Employee)
Final effect:
Operation steps:
Add a text component to the canvas: Set the corresponding style, and the numeric part uses the variable reference "{{emp_name}}".
Add a variable: Open the variable panel, add a page variable named "emp_name", then set the dimension and metric. Sort the output values in descending order to obtain the employee with the highest output value.
Format the result: Open the result formatting panel. Obtain the employee name via "response[0]['dim_key_A_1']", then insert the formatting function "substrFunc" after the value to extract one character starting from the first letter (that is, the last name).
Click preview to view the result value.
In the text component, reference the variable, and add a hidden name symbol as follows:
3.
Numeric Formatting (Such as Formatting a Number to Display 2 Decimal Places)
Final effect:
Operation steps:
Add a text component to the canvas: Set the corresponding style, and the numeric part uses the variable reference "{{emp_max}}".
Add a variable: Open the variable panel, add a page variable named "emp_max", then set the dimension and metric. Sort the output values in descending order to obtain the employee with the highest output value.
Format the result: Open the result formatting panel, then obtain the output value of the employee via "response[0]['index_key_G_1']".
In the text component, reference the variable, and format the result as follows to display the value in ten thousands with 2 decimal places:
You can also perform the above steps in the result formatting module of the variable editing page. Formatting when referencing is demonstrated here. The result is the same.
4. Time Formatting (Such as Displaying Only the "month day" in a Calendar)
Final effect:
Operation steps:
Add a text component to the canvas: Set the corresponding style.
Add a variable: Open the variable panel, add a page variable named "income_day", then set the dimension and metric. Sort the output values in descending order to obtain the date with the highest output value.
Format the result: Open the result formatting panel, then edit the format as follows.
In the text component, reference the variable "{{income_day}}", and view the effect.
5. Looped Processing (Such as Generating a Department Ranking List)
Final effect:
Operation steps:
Add a text component to the canvas: Set the corresponding style.
Add a variable: Open the variable panel, add a page variable named "dpts", then set the dimension and metric. Sort the output values in descending order to obtain the department ranking.
Format the result: Open the result formatting panel, then edit the format as follows.
In the above figure, the map function iterates over all rows. After the result of each row is obtained, the result is converted into text using join for display when referenced.
In the text component, reference the variable "{{dpts}}", and view the effect.
6. Judgement Processing (Such as Marking in Red When the Period-Over-Previous Period Value Is Less Than 0)
Final effect:
Operation steps:
Add a text component to the canvas: Set the corresponding style.
Add a variable: Open the variable panel, add a page variable named "income_dep", then set the dimension and metric.
In the above figure, the metric is set to the employee output value and its day-over-previous-day ratio. The day-over-previous-day ratio is used to judge whether the output value of the current day is increased or decreased compared to that of the previous day.
Format the result: Open the result formatting panel, then edit the format as follows.
In the above figure:
Iterate over all rows using the map function.
After the result of each row is obtained, format the "day-over-day" column value to a percentage format with 2 decimal places.
Set a "color" variable for color annotation of the day-over-previous-day ratio. Assign values to "color" based on whether the day-over-previous-day ratio is greater than 0.
Return the text of each row. Here, the value and period-over-previous period ratio for each day are generated, and the HTML element "<span style=''></span>" is used to style the period-over-previous period ratio.
Convert the result into text using join for display when referenced.
In the text component, reference the variable "{{income_dep}}", and view the effect.
Note:
Variable result formatting can be performed during variable editing or referencing, as shown in the previous Numeric Formatting example where variables are formatted when referenced in the text component. The usage method and effect are consistent.
To determine in which scenarios formatting is performed during variable editing or referencing, refer to the following:
If formatting is very complex, such as in the looped and judgment processing scenarios described above, we recommend that you perform formatting directly during variable editing.
If formatting is not complex and merely involves obtaining different rows and columns of values, we recommend that you perform formatting when referencing.
The following example shows the scenario of formatting when referencing.
Objective: Display the best employee name, department, and output value in the 3 cards, respectively.
Effect:
Steps:
Add 3 texts in the canvas and set the style.
Add a variable: Open the variable panel, add a page variable named "good_emp", then set the dimension and metric. Sort the output values in descending order to retrieve 1 result entry.
Format the result: Open the result formatting panel, and obtain the first row of the result (Note: Obtain the entire row, not the row and column).
Insert variables in the text components, and reference different data column names in the variable formats.
In the above figure, the values of different columns in the result are obtained, such as "employee name" for the best employee and "employee output value" for the best employee output value.
Syntax Description
BI uses a template language to obtain and process variables. The syntax is basically the same as JavaScript.
1. Obtaining Analysis Result
The response is the raw data result. You can obtain row and column values via a two-dimensional array.
{{response[x][y]}}
Obtain the row: "x" in the code represents the row index, starting from 0. 0 indicates the first row.
Obtain the column: "y" in the code represents the column key (the value is a string and should be enclosed in single quotes). The key can be viewed from the table header, as shown below:
The following example obtains the third-highest employee output value from the output values sorted in descending order:
{{response[2]['key_G']}}
The above code will obtain the result "114000".
2. Syntax Logic
2.1 Logical Operations:
// Arithmetic operations. JavaScript math operators are supported
{{a+b}}
// Logical operations
{{ a || b}}
//if...else
{{if a>b }} output result 1 {{/if}}
{{if a>b }} output result 1 {{else if a>2*b}} output result 2 {{/if}}
// Ternary operations
{{ a>b ? 'output result 1':'output result 2'}}
// Loops. The following example obtains all employee names (response is the entire table)
{{each response}}
No. {{$index+1}}: {{$value['key_A']}}
{{/each}}
// Loops can also be handled with built-in templates
BI has some built-in standard functions for rapid result formatting, such as adding thousand separators to numbers. Currently, BI has the following built-in formatting methods (see the product interface for details):
// Percentage processing method percentageFunc
// Format: {{percentageFunc A B C}}. A indicates the number of decimal places (default: 2), B indicates the thousand separator (disabled by default), and C indicates the rounding mode (roundFive: round half-up; roundUp: round up; roundDown: round down)
// Format: {{dateFormatFunc A}}. A is the time format. Default: 'yyyy-MM-dd'
{{ response[0]['key_A'] | dateFormatFunc 'MM'}}
2.3 Using JavaScript Syntax
In addition to built-in formatting methods, BI supports using JavaScript syntax to process data results, such as the string.substring() method. As shown in the example below, the substring method is used to obtain the month.
