1. Visualizing Data in Oracle Analytics Server
  2. Report Data
  3. Create Analyses
  4. Advanced Techniques: Reference Stored Values in Variables

Advanced Techniques: Reference 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, semantic model (repository), presentation, request, and global. Content authors can define presentation, request, and global variables themselves but other types (session and semantic model (repository) are defined for you in the semantic model.

Note: Oracle Analytics doesn’t support the variables :user and :password in data source connection credentials.

Type of Variable Defined in Defined by More Information

Session

  • System

  • Nonsystem

Semantic model

Data model authors

 About Session Variables

Semantic Model (Repository)

  • Dynamic

  • Static

Semantic model

Data model authors

 About Semantic Model (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

Administrators

 About Global Variables and Create 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 non-system.

System Session Variables

There are several system session variables that you can use in your reports and dashboards.

The system session variables have reserved names so you can’t use them for any other kind of variable.

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

The non-system session variables are named and created in your semantic 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.

About Semantic Model (Repository) Variables

A semantic model (repository) variable is a variable that has a single value at any point in time.

Semantic model (repository) variables can be static or dynamic. A static semantic model (repository) variable has a value that persists and doesn’t change until the administrator changes it. A dynamic semantic model (repository) variable has a value that is refreshed by data returned from queries.

About Presentation Variables

You create a presentation variable when 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.

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.

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 Model Administration Tool.

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 BI Service Administrator 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.

Create Global Variables

You can save a calculation as a global variable then reuse it in different analyses.

  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.
  4. Click Variable and select Global.
  5. Click Add New Global Variable.
  6. Enter the value for the Name. For example, gv_region, date_and_time_global_variable, or rev_eastern_region_calc_gv.
    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.
    • 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 reflect the variable more accurately.
  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']}

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