Using Variables in OBIEE

You can reference variables in several areas of Oracle BI Enterprise Edition, including in analyses, dashboards, KPIs, actions, agents, and conditions. 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.

There are five types of variables that you can use:

■Session
■Repository
■Presentation
■Request
■Global

Session Variables

A session variable is a variable that is initialized at login time for each user. When a user begins a session, the Oracle BI Server 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 the Oracle BI Server. Each instance of a session variable could be initialized to a different value.

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).

·                     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 session variables using the Oracle BI Administration Tool.

Repository Variables

A repository variable is a variable that has a single value at any point in time.

There are two types of repository variables:

·                     Static — Repository variables whose value persist and do not change until the administrator decides to change them.

·                     Dynamic — Repository variables whose values are refreshed by data returned from queries.

The administrator creates repository variables using the Oracle BI Administration Tool.

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 — 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" (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 — 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" (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.

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 a value in the column or variable prompt, the value of the presentation variable is set to the value that the user selects.

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 — 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.

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.

Global Variables

A global variable is a column created by combining a specific data type with a value. The value can be a string, number, date, time, expression, formula, and so on. 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. (See "What is Multitenancy?" in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.)
You create a global variable as part of the process of creating an analysis.
Global variables can be of the following types:

■Date
■Date and Time
■Number
■Text
■Time

To create a global variable:
1.In the "Selected Columns pane" on the Criteria tab, click the Options button beside the column whose formula you want to edit and select Edit Formula. The "Edit Column Formula dialog: Column Formula tab" is displayed. You can create a custom header for the global variable by using this tab.
2.Click the Variable button, and then select Global. The "Insert Global Variable dialog" is displayed.
3. Click the Add New Global Variable button. The "New Global Variable dialog" displays.
4. Enter a unique name.
5. Select a data type.
6. Enter a value.
7. Click OK. The new global variable is added to the Insert Global Variable dialog.
*Note: Only string and numeric request variables support multiple values. All other data types only pass the first value.
8. Select the new global variable that you just created, and then 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. Enter a new name for the column to which you have assigned a global variable to more accurately reflect the variable.
9. Click OK.

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 appropriate privileges can manage (add, edit, and delete) global variables.

Where Can I Reference Variables?

You can reference variables in the following areas (but not all types of variables can be referenced in each area):

·                     Title views.

·                     Narrative views.

·                     Static text views.

·                     Filters.

·                     Column formulas.

·                     Conditional formatting conditions.

·                     Table and column headings in analyses.

·                     Direct database requests.

·                     Dashboard prompts and inline prompts.

·                     Headers and footers for PDF output.

·                     Link or image objects in a dashboard.

·                     Text objects in a dashboard.

·                     Graphs to specify conditional formatting of graph data.

·                     Gauge thresholds.

·                     Gauge limits.

·                     Agents.

·                     Actions to specify parameters.

·                     Conditions to specify parameters.

·                     Selection steps.

·                     KPIs to define thresholds.

·                     KPIs included in a KPI watchlist.

·                     URL fields in dialogs.

Syntax for Referencing Variables

You can reference variables in analyses, dashboards, KPIs, and agents. 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 you use depends on the type of variable as described in Table 1.

Note:

In the syntax, if the "at" sign (@) is not followed by a brace ({), then it is treated as an "at" sign.

Table 1 Syntax for Referencing Variables

Type of Variable	Syntax	Example
Session	@{biServer.variables['NQ_SESSION.variablename']}	@{biServer.variables['NQ_SESSION.USER']}
	where variablename is the name of the session variable, for example DISPLAYNAME.
	For a list of system session variables that you can use, see "About System Session Variables" inMetadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
Repository	@{biServer.variables.variablename}	@{biServer.variables.prime_begin}
	or	or
	@{biServer.variables['variablename']}	@{biServer.variables['prime_begin']}
	where variablename is the name of the repository variable, for example, prime_begin.
Presentation or request	@{variables.variablename}[format]{defaultvalue}	@{variables.MyFavoriteRegion}{EASTERN REGION}
	or	or
	@{scope.variables['variablename']}	@{MyFavoriteRegion}
	where:	or
	■variablename is the name of the presentation or request variable, for example, MyFavoriteRegion.	@{dashboard.variables['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.)	or
	■(optional) defaultvalue is a constant or variable reference indicating a value to be used if the variable referenced by variablename is not populated.	(@{myNumVar}[#,##0]{1000})
	■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.)	or
	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.MyOwnTimestamp}[YY-MM-DD hh:mm:ss]{)
		or
		(@{myTextVar}{A, B, C})
Global	@{global.variables.variablename}	@{global.variables.gv_date_n_time}

Table 2 Guidelines for Referencing Variables in Expressions

Type of Variable	Guidelines	Example
Session	■Include the session variable as an argument of the VALUEOF function.	"Market"."Region"=VALUEOF(NQ_SESSION."SalesRegion")
	■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.
Repository	■Include the repository variable as an argument of the VALUEOF function.	CASE WHEN "Hour" >= VALUEOF("prime_begin") AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END
	■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.
	If you are using a dynamic repository variable, then the names of the initialization block and the repository variable must be enclosed in double quotes ("), separated by a period, and contained within parentheses. For example, to use the value of a dynamic repository variable named REGION contained in a initialization block named Region Security, use this syntax:
	VALUEOF("Region Security"."REGION")
	For more information, see "About Repository Variables" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
Presentation	■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}' Note: 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'})	"Market"."Region"=@{MyFavoriteRegion}{EASTERN REGION} or "Products"."P4 Brand"=(@{myTextVar}['@']{BizTech}) or "Products"."PO Product Number"=(@{myNumVar}{1000}) or "Sales Person"."E7 Hire Date"=(@{myDateTimeVar}{timestamp '2013-05-16 00:00:01'}) or "Time"."Total Fiscal Time"=(@{myDateVar}{date '2013-05-16'}) or "Time"."Time Right Now"=(@{myTimeVar}{time '00:00:01'}) For multiple values (in specified data types) when using SQL: If the column type is Text and variablename is passing val1, val2, and val3, the resultant is ('val1', 'val2', 'val3'). or If the column type is Date and variablename is passing 2013-08-09 and 2013-08-10, the resultant is (date '2013-08-09', date '2013-08-10'). or If the column type is Date-time and variablename is passing 2013-08-09 00:00:00 and 2013-08-10 00:00:00, the resultant is (timestamp '2013-08-09 00:00:00', timestamp '2013-08-10 00:00:00').