Advanced Techniques: Referencing Stored Values in Variables

You might want to create an analysis whose title displays the current user's name. You can do this by referencing a variable.

You can reference several different types of variable in your analyses, dashboards, and actions: session, repository, presentation, request, and global. Content authors can define presentation, request, and global variables themselves but other types (session and repository) are defined for you in the data model.

Type of Variable Defined in Defined by More Information

Session

  • System

  • Nonsystem

Data model

Data modeler

About Session Variables

Repository

  • Dynamic

  • Static

Data model

Data modeler

About Repository Variables

Presentation

Prompts for analyses and dashboards

Content authors

About Presentation Variables

Request

Prompts for analyses and dashboards

Content authors

About Request Variables

Global

Analyses

Advanced content authors

About Global Variables and Creating Global Variables

About Session Variables

Session variables are initialized when a user signs in. These variables exist for each user for the duration of their browsing session and expire when the user closes the browser or signs out. There are two types of session variable: system and nonsystem.

System Session Variables

There are several system session variables that you can use in your reports and dashboards. These variables have reserved names so you can’t use them for any other kind of variable. Modelers can define values for these session variables in Data Modeler. See Creating Variables to Use in Expressions. Note that in Data Modeler, a session variable is defined with the Update Value set to On sign in.

System Session Variable Description Example SQL Query Value (Variable dialog)

PORTALPATH

Identifies the default dashboard a user sees when they sign in (they can override this preference after signing in).

To display ‘mydashboard’ when a user signs in:

 select ‘/shared/_portal/mydashboard’ from dual;

TIMEZONE

Specifies the default time zone for a user when they sign in.

A user’s time zone is typically populated from the user’s profile. Users can change their default time zone through preferences (My Account).

To set the time zone when a user signs in:

select ‘(GMT-08:00) Pacific Time (US & Canada)’ from dual;

DATA_TZ

Specifies an offset from the original time zone for data.

This variable enables you to convert a time zone so that users see the appropriate zone.

To convert time data to Eastern Standard Time (EST):

select ‘GMT-05:00’ from dual;

This example means Greenwich Mean Time (GMT) - 5 hours

DATA_DISPLAY_TZ

Specifies the time zone for displaying data.

To display Eastern Standard Time (EST):

select ‘GMT-05:00’ from dual;

This example means Greenwich Mean Time (GMT) - 5 hours

Non-System Session Variables

A session variable named and created in your data model. For example, your data modeler might create a SalesRegion variable that initializes to the name of a user's sales region when they sign in.

Modelers can define values for these session variables in Data Modeler. See Creating Variables to Use in Expressions.

About Repository Variables

A repository variable is a variable that has a single value at any point in time. Repository variables can be static or dynamic. A static repository variable has a value that persists and doesn’t change until the administrator changes it. A dynamic repository variable has a value that is refreshed by data returned from queries.

Modelers define dynamic and static repository variables in Data Modeler. The Update Value option determines whether a variable is dynamic or static. For dynamic variables, Update Value = On a Schedule. For static variables, Update Value = Never. See Creating Variables to Use in Expressions.

About Presentation Variables

A presentation variable is a variable that you can create as part of the process of creating a column prompt or a variable prompt.

Type Description

Column prompt

A presentation variable created as part of a column prompt is associated with a column, and the values that it can take come from the column values.

To create a presentation variable as part of a column prompt, in the New Prompt dialog, you must select Presentation Variable in the Set a variable field. Enter a name for the variable in the Variable Name field.

For information on working with column prompts, see Creating Column Prompts.

Variable prompt

A presentation variable created as part of a variable prompt isn’t associated with any column, and you define the values that it can take.

To create a presentation variable as part of a variable prompt, in the New Prompt dialog, you must select Presentation Variable in the Prompt for field. Enter a name for the variable in the Variable Name field.

For information on working with variable prompts, see Creating Variable Prompts.

The value of a presentation variable is populated by the column or variable prompt with which it was created. That is, each time a user selects one or more values in the column or variable prompt, the value of the presentation variable is set to the value or values that the user selects.

About Request Variables

A request variable enables you to override the value of a session variable but only for the duration of a database request initiated from a column prompt. You can create a request variable as part of the process of creating a column prompt.

You can create a request variable as part of the process of creating one of the following types of dashboard prompts:

  • A request variable that is created as part of a column prompt is associated with a column, and the values that it can take come from the column values.

    To create a request variable as part of a column prompt, in the New Prompt dialog, you must select Request Variable in the Set a variable field. Enter the name of the session variable to override in the Variable Name field.

  • A request variable that is created as part of a variable prompt isn’t associated with any column, and you define the values that it can take.

    To create a request variable as part of a variable prompt, in the New Prompt dialog (or Edit Prompt dialog), you must select Request Variable in the Prompt for field. Then enter a name of the session variable that you want to override in the Variable Name field.

The value of a request variable is populated by the column prompt with which it was created. That is, each time a user selects a value in the column prompt, the value of the request variable is set to the value that the user selects. The value, however, is in effect only from the time the user presses the Go button for the prompt until the analysis results are returned to the dashboard.

Certain system session variables (such as, USERGUID or ROLES) can’t be overridden by request variables. Other system session variables, such as DATA_TZ and DATA_DISPLAY_TZ (Timezone), can be overridden if configured in the Oracle BI Administration Tool.

Note:

Only string and numeric request variables support multiple values. All other data types pass only the first value.

About Global Variables

A global variable is a column created by combining a specific data type with a value. The value can be a Date, Date and Time, Number, Text, and Time.

The global variable is evaluated at the time the analysis is executed, and the value of the global variable is substituted appropriately.

Only users with the BIAdvancedContentAuthor role can manage (add, edit, and delete) global variables.

You create a global value during the process of creating an analysis by using the Edit Column Formula dialog. The global variable is then saved in the catalog and made available to all other analyses within a specific tenant system.

Creating Global Variables

You can save a calculation as a global variable then reuse it in different analyses. By simply creating a global variable, you don’t have to create a new column in the Data Modeler.

  1. Open the analysis for editing.
  2. In the Selected Columns pane, click Options beside the column name.
  3. Select Edit Formula to display the Column Formula tab. You can create a custom header for the global variable by using this tab.
  4. Click Variable and select Global to display the Insert Global Variable dialog.
  5. Click Add New Global Variable to display the New Global Variable dialog.
  6. Enter the value for the Name. For example, gv_region, date_and_time_global_variable, or rev_eastern_region_calc_gv. See Syntax for Referencing Variables for syntax requirements.

    Note:

    The name for a global variable must be fully qualified when referencing the variable, and therefore is prefixed by the text "global.variables". For example, a global variable set to calculate revenue is displayed in the Column Formula dialog as follows:

    "Base Facts"."1- Revenue"*@{global.variables.gv_qualified}

  7. Enter values for the Type and Value.

    Note:

    If you’re selecting "Date and Time" as the data type, then enter the value as in the following example: 03/25/2004 12:00:00 AM

    If you’re entering an expression or a calculation as a value, then you must use the Text data type, as in the following example: "Base Facts"."1- Revenue"*3.1415

  8. Click OK. The new global variable is added to the Insert Global Variable dialog.
  9. Select the new global variable that you just created, and click OK. The Edit Column Formula dialog is displayed with the global variable inserted in the Column Formula pane. The Custom Headings check box is automatically selected.
  10. Enter a new name for the column to which you have assigned a global variable to more accurately reflect the variable.
  11. Click OK.

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]}

Repository

@{biServer.variables.variablename}

or

@{biServer.variables['variablename']}

where variablename is the name of the repository 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.)

Note: 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

@{MyFavoriteRegion}

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.

Note: The naming convention for global variables must conform to EMCA 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 sections.

Session Variables

The guidelines for referencing session variables in expressions are:

  • 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 both the NQ_SESSION portion and the session variable name in parentheses.

For example:

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

Presentation Variables

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, enclose the entire syntax 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:

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

Repository Variables

The guidelines for referencing repository variables in expressions are:

  • Include the repository variable as an argument of the VALUEOF function.

  • Enclose the variable name in double quotes.

  • Refer to a static repository variable by name.

  • Refer to a dynamic 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