Advanced Techniques: Referencing Stored Values in Variables

Topics:

You can reference stored values in variables in several areas of Oracle BI Enterprise Edition, including in analyses, dashboards, and actions. For example, suppose that you wanted to create an analysis whose title displays the current user's name. You can do this by referencing a variable. You can use fives types of variables: session, repository, presentation, request, and global.

About Session Variables

A session variable is a variable that is initialized at login time for each user. When a user begins a session, Oracle BI Enterprise Edition creates a new instance of a session variable and initializes it. There are as many instances of a session variable as there are active sessions on Oracle BI Enterprise Edition. Each instance of a session variable can be initialized to a different value.

There are two types of session variables, as described in the following table.

Type Description
System A session variable used by Oracle BI Enterprise Edition for specific purposes.

System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and non-system session variables).
Non-system A system variable that the administrator creates and names. For example, the administrator might create a SalesRegion non-system variable that initializes the name of a user's sales region.

The administrator creates non-system variables using the Oracle BI Administration Tool.

About Repository Variables

A repository variable is a variable that has a single value at any point in time. A static repository variable has values that persist and do not change until the administrator changes it. A dynamic repository variable has values that are refreshed by data returned from queries.

About Presentation Variables

A presentation variable is a variable that you can create as part of the process of creating one of the types of dashboard prompts that is described in the following table.

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 is not 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.

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.

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.

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.

To create a global variable:

  1. Open the analysis for editing.

  2. In the Selected Columns pane, click Options beside the column name.

    Description of analysis17.gif follows
    Description of the illustration analysis17.gif

  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.

    Description of analysis77.gif follows
    Description of the illustration analysis77.gif

  5. Click Add New Global Variable to display the New Global Variable dialog.

    Description of analysis64.gif follows
    Description of the illustration analysis64.gif

  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 are 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 are 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 are performing. For tasks where you are 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.USER']}
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 is not 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 is not 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 do not 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 are 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")

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

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 is not 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 is not followed by a {, then it is 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")