Using Variables

What Are 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.

Session variables are populated by a query specified in an initialization block (INIT BLOCK). These session INIT BLOCKs are typically executed only once at login time. However, if the INIT BLOCK is marked as deferred, then it can be executed at a later time when the relevant variables are referenced.

Session INIT BLOCKS are generally associated with a query statement, but can also be associated with an LDAP object. For more information about INIT BLOCKs, see "Working with Initialization Blocks" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

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.

For more information, see "About Session Variables" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

What Are 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 populated by a query specified in an INIT BLOCK. You can specify the interval for which the INIT BLOCK is refreshed. A dynamic INIT BLOCK automatically refreshes its variables' values at the specified interval.

  Dynamic INIT BLOCKs can only be associated with a query (that is, by a SQL statement, an ADF query, or another supported data source). For more information about INIT BLOCKs, see "Working with Initialization Blocks" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

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

For more information, see "About Repository Variables" in Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

What Are 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 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 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.

  For information on working with column prompts, see "Creating a Column Prompt."

• Variable prompt — A presentation variable that is 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.

  For information on working with variable prompts, see "Creating a Variable Prompt."

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.

What Are 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 or a variable prompt.

You can create a request variable as part of the process of creating one of the following types of dashboard prompts:

• 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" (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 the Variable Name field.

  For information on working with column prompts, see "Creating a Column Prompt."

• Variable prompt — A request variable that is 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 request variable as part of a variable prompt, in the "New Prompt dialog" (or Edit Prompt dialog), you have to select Request Variable in the Prompt for field and then enter a name of the session variable that you want to override in the Variable Name field.

  For information on working with variable prompts, see "Creating a Variable Prompt."

The value of a request variable is populated by the column prompt 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 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.

What Are 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.

   Figure 2-12 Example of the Insert Global Variable Dialog

   
   

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.

   Figure 2-13 Example of the New Global Variable Dialog

   
   
   
   

   Note: If you are selecting "Date and Time" as the data type, 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, you must use the Text data type, as in the following example: "Base Facts"."1- Revenue"*3.1415

   
   

7. Click OK. The new global variable is added to the Insert Global Variable dialog.

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.

   Figure 2-14 Example of a New Global Variable in the Edit Column Formula Dialog

   
   

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.