Variables and BQL
You can use Birst variables in BQL expressions, and you can define a variable using a BQL expression. Birst provides built-in variables for some use cases.
Variables can refresh either when users log in (session variables) or when data processes (repository variables).
Variables in BQL Expressions
When referencing a variable in BQL, use the GETVARIABLE function:
GETVARIABLE('variable_name')
Example:
GETVARIABLE('LoadDate')
Tip: In non-BQL situations, such as when referencing a variable in a Birst Connect query, Query (opaque view) Live Access source, Salesforce SOQL query, other Application Connector objects, or in a report schedule, use the V{variable_name} syntax. However do not use this syntax in BQL expressions.
BQL Expression in a Variable Definition
Space Administrators can create query-based variables at Admin - Customize Space - Variables. Use BQL for the expression that defines the variable.
You can use the GETVARIABLE function in the variable expression. Birst treats a variable definition that contains GETVARIABLE as a session variable and it cannot be a repository variable.
Built-In Variables
Be familiar with the built-in variables before creating a new one.
USER Session Variable
The USER session variable stores the user name of the user, such as "brain@acmelabs.com", that is logged in for that session. USER is helpful for personalizing content, such as in a report expression. Another use case is for a data security filter, which is a query that uses a WHERE clause and the USER system variable to restrict access.
Repository Variables for Loads
The pre-defined repository variables hold information about when data was last uploaded and processed into Birst. These are shown in the Admin - Process New Data tab.
- LoadDate: The last time the data was loaded. For example, 08/29/2015.
- LoadDateSFDC: The last data and time that Salesforce data was loaded. For example, 2015-08-29T00:00:00-05.00.
- LoadNumber: The incremented load number, or load ID. For example, 3.
- LoadStartTime: The start data and time of the last load into the space. For example, 2015-08-29T11:46:45-05:00.
- LoadCompletionTime: The end data and time of the last load into the space. For example, 2015-08-29T11:46:45-05:00.