Syntax for Referencing Variables

You can reference variables in analyses and dashboards.

How you reference a variable depends on the task that you’re performing. For tasks where you’re presented with fields in a dialog, you must specify only the type and name of the variable (not the full syntax), for example, referencing a variable in a filter definition.

For other tasks, such as referencing a variable in a title view, you specify the variable syntax. The syntax that you use depends on the type of variable as described in the following table.

Type Syntax Example

Session

@{biServer.variables['NQ_SESSION.variablename']}

where variablename is the name of the session variable, for example DISPLAYNAME.

@{biServer.variables['NQ_SESSION.SalesRegion']}

Semantic Model (Repository)

@{biServer.variables.variablename}

or

@{biServer.variables['variablename']}

where variablename is the name of the variable, for example, prime_begin

@{biServer.variables.prime_begin}

or

@{biServer.variables['prime_begin']}

Presentation or request

@{variables.variablename}[format]{defaultvalue}

or

@{scope.variables['variablename']}

where:

variablename is the name of the presentation or request variable, for example, MyFavoriteRegion.

(optional) format is a format mask dependent on the data type of the variable, for example #,##0, MM/DD/YY hh:mm:ss. (Note that the format isn’t applied to the default value.)

(optional) defaultvalue is a constant or variable reference indicating a value to be used if the variable referenced by variablename isn’t populated.

scope identifies the qualifiers for the variable. You must specify the scope when a variable is used at multiple levels (analyses, dashboard pages, and dashboards) and you want to access a specific value. (If you don’t specify the scope, then the order of precedence is analyses, dashboard pages, and dashboards.)

When using a dashboard prompt with a presentation variable that can have multiple values, the syntax differs depending on the column type. Multiple values are formatted into comma-separated values and therefore, any format clause is applied to each value before being joined by commas.

@{variables.MyFavoriteRegion}{EASTERN REGION}

or

@{dashboard.MyFavoriteRegion}{EASTERN REGION}

or

@{dashboard.variables['MyFavoriteRegion']}

or

(@{myNumVar}[#,##0]{1000})

or

(@{variables.MyOwnTimestamp}[YY-MM-DD hh:mm:ss]{)

or

(@{myTextVar}{A, B, C})

Global

@{global.variables.variablename}

where variablename is the name of the global variable, for example, gv_region. When referencing a global variable, you must use the fully qualified name as indicated in the example.

The naming convention for global variables must conform to ECMA Scripting language specifications for JavaScript. The name must not exceed 200 characters, nor contain embedded spaces, reserved words, and special characters. If you’re unfamiliar with JavaScripting language requirements, consult a third party reference

@{global.variables.gv_date_n_time}

You can also reference variables in expressions. The guidelines for referencing variables in expressions are described in the following topics:

Session Variables

You can use the following guidelines for referencing session variables in expressions.

  • Include the session variable as an argument of the VALUEOF function.
  • Enclose the variable name in double quotes.
  • Precede the session variable by NQ_SESSION and a period.
  • Enclose NQ_SESSION in double quotes.
  • Enclose both the NQ_SESSION portion and the session variable name in parentheses.

For example:

"Market"."Region"=VALUEOF("NQ_SESSION"."SalesRegion")

Presentation Variables

You can use the following guidelines for referencing presentation variable in expressions.

When referencing a presentation variable, use this syntax:

@{variablename}{defaultvalue}

where variablename is the name of the presentation variable and defaultvalue (optional) is a constant or variable reference indicating a value to be used if the variable referenced by variablename isn’t populated.

To type-cast (that is, convert) the variable to a string or include multiple variables, enclose the entire variable in single quotes, for example:

'@{user.displayName}'

If the @ sign isn’t followed by a {, then it’s treated as an @ sign. When using a presentation variable that can have multiple values, the syntax differs depending on the column type.

Use the following syntax in SQL for the specified column type in order to generate valid SQL statements:

  • Text — (@{variablename}['@']{'defaultvalue'})

  • Numeric — (@{variablename}{defaultvalue})

  • Date-time — (@{variablename}{timestamp 'defaultvalue'})

  • Date (only the date) — (@{variablename}{date 'defaultvalue'})

  • Time (only the time) — (@{variablename}{time 'defaultvalue'})

For example:

'@{user.displayName}'

Semantic Model (Repository) Variables

You can use the following guidelines for referencing semantic model (repository) variables in expressions.

  • Include the variable as an argument of the VALUEOF function.
  • Enclose the variable name in double quotes.
  • Refer to a static semantic model (repository) variable by name.
  • Refer to a dynamic semantic model (repository) variable by its fully qualified name.

For example:

CASE WHEN "Hour" >= VALUEOF("prime_begin") AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END