OBIEE Variables

Types of OBIEE variables:

1. Session Variables

Session variables created during the creation of new session.

Following are types of session variables:

a.      System session variables:

Which are defined by OBIEE and are reserved:

E.g. USER, PROXY, GROUPS, ROLES, PERMISSIONS, LOGLEVEL, DESCRIPTION, SELECT_PHYSICAL

Initialized when its first usage.

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

b.      Non system session variables

            A common use for non system session variables is setting user filters.

            E.g. Select sales as “User_Sales” from sales_region where user_name=:USER;

           

2. Repository Variable

      a.   Static Repository Variables:

The value of a static repository value is initialized in the Variable dialog box. This value persists, and does not change until an Oracle BI Administrator decides to change it.
E.g. filter on sales region.

      b.   Dynamic Repository Variables:

            Refresh its value after every request.

            E.g. Selection of month from system dates.

           

3. Presentation Variables:

a.      Column prompt

Column Prompt is based on a Real Column or a Formula. Column Prompt, "Is Prompted" and "Protect Filter".

b.      Variable prompt

A variable prompt allows the user to select a value that is specified in the variable prompt to display. Variable Prompt has no actual column behind and is aimed to create presentation or request variables only.

c.       Image prompt

Image area is used in the results. E.g. Sales Region.

d.      Currency Prompt

Represents the currency selected in currency column.

e.       Dashboard prompt

This prompt is global to dashboard.

4. Request Variables:

A request variable is an OBI Server Session Variable but with the scope of the request. The value of the session variable will not be change for the session. A request variable is a variable that is used to temporarily override the value of a session variable.

You can set it up in three main ways:

·         In a dashboard prompt with the help of the SET VARIABLE column.

·         By adding manually the SET VARIABLE clause in a logical SQL statement (into an answer, Dashboard prompt, Issue SQL, …)

Setting Request variables:

In an Answer

      1.   with value:

            In an answer, in the advanced tab, you have the section “Advanced SQL Clauses”.

            In the prefix field, you can add a “SET VARIABLE MYVARIABLE=MyValue;”

      2.   with a presentation variable:

            You can then use the value of a presentation variable to initialize a request variable.

      3.   in dashboard prompt:

            Click on column names->select options->In Set a variable, select Request variable.

            Enter the name of the session variable to override in the Variable Name field.

Referencing variables:

          Session: @{biServer.variables['NQ_SESSION.variablename']}
                  E.g.@{biServer.variables['NQ_SESSION.USER']}

          Repository: @{biServer.variables.variablename} or @{biServer.variables['variablename']}
                  E.g.@{biServer.variables.prime_begin} or @{biServer.variables['prime_begin']}

          Presentation or request: @{variables.variablename}[format]{defaultvalue} or 
                                                   @{scope.variables['variablename']} 
                  E.g.@{variables.MyFavoriteRegion}{EASTERN REGION} or 
                         @{dashboard.variables['MyFavoriteRegion']}