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). See Using 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.

See Creating Variables.

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. See Creating Data Security Filters.

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.