Creating Variables

Space Administrators can create custom variables for use in:

Reports and dashboards
Data security filters
Birst Connect queries
Birst Connect server name, database name, user name, and generic connection strings (single-value constants)
Application Connector queries

See Using Variables for an overview of Birst built-in variables.

You can define the type and content of variables using either of the following:

Query: Query-based variables are assigned the first column of the first row result of a BQL query. A query variable definition is a BQL expression. It can return one or multiple values.
Constant: Constant-based variables are a string for a constant value of session variable. It can contain one or multiple values

Go to Admin - Customize Space - Variables.
Click Create. The Add Variable dialog box opens.
Type a name for the new variable.
Select the type of variable to create, Query or Constant, then select either Single Value or Multi-value.
- If you set the variable to Single Value, you will get the first record (value) from the first column of the query.
  Tip: Sorting the query is very important when using a Single Value variable.
- If you set the variable to Multi-value, a comma delimited list of values from the first column of the query will be assigned to the variable.
  Important: Multi-value variables can be used in security filters, against XMLA cube sources, and within Birst Connect and application connector query sources, using the V{variable name} syntax.

If you have real-time connections using Live Access in Birst Connect, they appear in the Connection list. Select the connection to use. This field is grayed out if there are no Live Access connections.
Type or copy the expression for the variable in the Expression text box.
Tips for Expressions:
- You can create and validate an expression in Designer or Visualizer, then copy and paste it into the Expression field.
- Variable expressions that include the GETVARIABLE function are treated as session variables and cannot be repository variables.
- The following example shows a expression-based variable that can filter a report on the most recent quarter. This type of variable is most useful as a repository variable.

Tips for Variables used with Specific Application Connectors:

If you are creating a datetime variable to use in the Selection Criteria field of a Marketo object, you must use the following date format required by Marketo: yyyy-MM-ddTHH:mm:ss.SSSZ
If you are creating a variable to use for the Start Date or End Date for a Google Analytics or Omniture SiteCatalyst object extraction, you must use the following syntax: YYYY-MM-DD
If you are creating a constant variable to use for the Profile ID for a Google Analytics object extraction or for the Report Suite ID for an Omniture SiteCatalyst object extraction, the expression should contain only the Profile ID or Report Suite ID. Multiple Profile IDs or Report Suite IDs should be separated by commas (with no spaces).
If you are creating a date/datetime variable to use in a SOQL query in a Salesforce.com query object, the FORMAT function must be used in the variable expression in order to format the date/datetime to one of the following formats supported by Salesforce:
Date: yyyy-MM-dd
DateTime: yyyy-MM-dd HH:mm:ssZ
The following example shows a Birst variable created for use in a Salesforce SOQL query as a single value BQL query returning datetime, stored in the server time zone with UTC offset:

The following example shows a variable with a constant value in server time zone for use in a SOQL query in a Salesforce.com query object.

Optionally, enter a value as the Default if Invalid, if the query does not return anything or is invalid.
For Session Overwrite, select Yes if you want the variable to be a session variable that resets for each user session. By default (No), the variable will be a repository variable that refreshes when data is processed.
Click Validate to check for errors in the variable definition.
Click OK when all errors are resolved. The Variables tab lists the new variable.

Refresh your variables by re-processing the data. See Processing Uploaded Data.
To modify or delete a variable, select the variable and click either Modify or Delete.