Variables in OBIEE

There are basically four types of variables:

·        Session

·        Repository

·        Presentation

·        Request

Session Variables

Session variables are created and assigned a value when each user logs on. When a user begins a session, the Oracle BI Server creates a new instance of a session variable and initializes it.So, every login has its own session variable.

Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session.

Session variables can only be created using the Oracle BI Administration Tool

There are two types of session variables:

·        System A session variable that the Oracle BI Server and Oracle BI Presentation Services use 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).Few system variables are (USER,PROXY,LOGLEVEL,PORTALPATH, DISABLE_CACHE_HIT)

·        Non-system A system variable that the administrator creates and names. A common use for non-system session variables is setting user filters.For example, the administrator might create a Sales Region non-system variable that initializes the name of a user's sales region.

Non-system session variables can only be created using the Oracle BI Administration Tool.

Syntax for Referencing Session Variable

For displaying session variables, we should use “@{biServer.variables['NQ_SESSION.VariableName']}”

For using session variables in expression, we should use
 “VALUEOF(NQ_SESSION.“VariableName”)”

Repository Variables

A repository variable has a single value at any point in time. Repository variables can only be created using the Oracle BI Administration Tool

There are two types of repository variables: 

·        Static Repository variables whose value persist and do not change until the administrator decides to change them. The value of a static repository variable is initialized in the Variable dialog

·        Dynamic Repository variables whose values are refreshed by data returned from queries. You also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.

Syntax for Referencing Repository Variable

For displaying repository variables, we should use

“@{biServer.variables.VariableName}” or “@{biServer.variables['VariableName']}”

For using repository variables in expression,we should use “VALUEOF(“VariableName”)” for static variable and “VALUEOF(“Dynamic Initialization Block Name”.“VariableName”)” for dynamic variable.

Presentation Variables

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

Column prompt 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" (or Edit Prompt dialog), you have to select Presentation Variable in the Set a variable field and then enter a name for the variable in the Variable Name field.

Variable prompt 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" (or Edit Prompt dialog), you have to select Presentation Variable in the Prompt for field and then enter a name for the variable in the Variable Name field.

Syntax for Referencing Presentation Variable

For displaying presentation variables, we should us

“@{variables.VariableName}[Format]{DefaultValue}”

Format and DefaultValue are optional

Format is useful to format the data for e.g., for Date, format can be MM/DD/YYYY. Note: Default Value is not formatted.

For using presentation variables in expression, we should use

“@{“VariableName”}{DefaultValue}” Default value is optional

Request Variables

A request variable lets you 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.

Column prompt 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.

Variable prompt To create a request variable as part of a column prompt, in the "New Prompt dialog" (or Edit Prompt dialog), you have to select Request Variable in the Set a variable field and then enter the name of the session variable to override in theVariable 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.