Oracle Business Intelligence Cloud Services – Variables
June 17th, 2020 by Sunil Guddanti

Variables are useful to handle dynamic scenarios. We have five types of Variables.

Type of VariablesDefined InDefined by
Session Variables
  • System
  • Non system
RPD Calculations, RPD Filters, Report Calculations, Report Filters & Dashboard PromptsData modeler
Repository Variables
  • Dynamic
  • Static
RPD Calculations, RPD Filters, Report Calculations, Report Filters & Dashboard PromptsData modeler
Presentation VariablesAnalysis ,Agents & DashboardsContent authors
Request VariablesRequest Variables Analysis, Agents & Dashboards Content authorsContent authors
Global VariablesAnalysisAdvanced content authors

(a) Session variables:

1. Session variable will populate separately for each & every session or user.

2. It persist only while a user’s session is active.

3. It receive values when users establish their sessions.

4. Session variables exist for each user for the duration of their browsing session and expire when the user closes the browser or signs out.

Syntax:VALUEOF (NQ_SESSION.Variablename)

System session variables:

1. These are pre-defined session variables and used by oracle BI server for specific purpose such as authenticating users.

2. We have below system variables. [Case-sensitive must be in CAPITAL]

  • USER.
  • GROUP.
  • ROLES.

3. These variables are useful in special cases such as authenticating user. These variables should not use for any other purpose.

Non-system session variables:

1. Non-system session variables are application specific customized variables.

2. These variables required Session initialization blocks.

3. Session initialization blocks will executed whenever user login into analytics application.

Ex: Data Security or row level security.

(b) Repository variables:

1. Repository variables also called as Oracle BI Server variables.

2. The variable values will updated whenever Oracle BI Server started or restarted based on schedule.

Syntax: VALUEOF (Variable name)


  • Dynamic variables are associated with initialization block.
  • A dynamic repository variable has a value that it should refreshed by data returned from queries.
  • An SQL query is called as initialization block.
  • The initialization blocks will executed with oracle BI server refresh and based on the schedule of initialization block.
  • The Schedule option is available only for dynamic repository variable initialization blocks.


  • If we want to use a constant value at ‘n’ no. of places then we will go for static variables.
  • It has a value that persists and does not change until the administrator changes it.
  • Ex: Database name, username of connection pools
  • Static variable values are initialized in the Static Repository Variable dialog box
  • We have three static predefined syntax variables for date: Date, Time and Timestamp.

Ex: To Capture Max Year and Max Month from Database.

(C) Presentation variables:

  • Presentation Variables are useful to capture “User Response”
  • Presentation variable is a variable that can be created either column prompt or a variable prompt.

Syntax: @ {<variable name>} {<value>} [<format>]

Here <value< and <format< are optional.

  • Variable Name is the name of the Presentation Variable.
  • Value is by default value
  • Format is to convert one format to other format.

(d) Request variables:

Request variable enables you to override the value of a session variable and it happens only during a database request initiated from a column prompt. It is useful to override session variable values

  • The name of request variable should be same as session variable
  • Request variable are defined at Dashboard Prompts
  • These variables are useful to send values from report to RPD.

(e) Global variables:

  • It 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.
  • It will evaluate the time the analysis is exececution, and the value of the global variable is substituted appropriately.
  • Users with the BI Advanced Content Author role can manage (add, edit, and delete) global variables.
  • Global variable can saved in CatLog and made available to other analysis.

Creating Global Variables:

By creating global variables, we can reuse it in different analyses. Simply creating a global variable, there is no need to create separate column in Data model.

  1. Open the analysis for editing column formula.
  2. In the below Selected Columns pane, click Gear Option beside the column name.
  3. Click on Edit Formula to display the Column Formula tab. You can create a custom header for the global variable by using the tab.
  4. Click Variables and select Global to display the Insert Global Variable dialog box.
  5. Click on Add New Global Variable to display the New Global Variable dialog box.
  6. Enter the value for the Name.
    Ex: gv_region, time_and_date_global_variable.
    The name of global variable should be fully qualified while referencing the global variable, and it prefixed by 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 we are selecting "Date and Time" as data type, then 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, then you must use the Text data type, as in the following example: "Base Facts"."1- Revenue"*3.1415
  8. Click OK. Then the new global variable is added to the Insert Global Variable dialog.
  9. Add the new global variable just created and click OK. The Column Edit Formula dialog is displayed with global variable inserted in column formula pane. Then Custom Headings check box is automatically selected.
  10. Enter the new name for the column to which you have assigned a global variable to more accurately reflect the variable.
  11. Click OK.

Syntax for Referencing Variables:

Refer variables in analyses and dashboards.

However, referencing a variable depends on the task that you are performing. For tasks while defining in the fields in 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.

Session Variable@{biServer.variables ['NQ_SESSION.variablename']}

Where variable name is the name of the session variable, for example TIMEZONE.
@{biServer.variables ['NQ_SESSION.Actuals]}

@{biServer.variables['variablename']} where variable name is the name of the repository variable, for example, Shipped_Date

Presentation or request@{variables.variablename}[format]{defaultvalue}

Variable name is the name of the presentation or request variable, for example, MyFavoriteRegion.
(optional) format is a format mask dependent on data type of the variable, for example #,##0, MM/DD/YY hh:mm:ss. (Note that format isn’t applied to the default value.)
(Optional) default value is constant or variable reference indicating a value to be used if the variable referenced by variable name is not populated.
scope identifies the qualifiers for the variable. You must specify 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 order of precedence is analyses, dashboard pages, & dashboards.)
Note: While 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 & therefore, any format clause is applied to each value before being joined by commas.
@{variables.MyFavoriteRegion}{NORTH REGION}

@{dashboard.variables ['MyFavoriteRegion']}
(@{variables.MyOwnTimestamp} [YY-MM-DD hh:mm:ss]{)
(@{myTextVar}{A, B, C})

Where variablename is the name of the global variable, for example, gv_Date. When referencing a global variable, you must use the fully qualified name as indicated in the example.
Note: The naming convention for global variables must confirm to EMCA Scripting language specifications for JavaScript. The name must not exceed 190 characters, nor contain embedded spaces, reserved words, and special characters. If you’re unfamiliar with JavaScripting language requirements, consult a third party reference

Referencing Variables in Expressions:

Session • Include the session variables as an argument of the VALUEOF function.
• The variable name in double quotes.
• Precede the session variable by a NQ_SESSION and a period.
• Both the NQ_SESSION parameters and the session variable name in parentheses.
Repository• Include the repository variable as an argument of the VALUEOF function.
• The variable should be in double quotes.
• Refer to static repository variable by name.
• Refer to 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 in double quotes ("), separated by a period, and contained within parentheses. For example, to use the value of dynamic repository variable named REGION contained in a initialization blocks named Region Security, use this syntax: VALUEOF("Region Security"."REGION")
CASE WHEN "Hour">= VALUEOF("prime_begin") AND "Hour"
Presentation or request• Use this syntax:
where variablename is name of the presentation variable and defaultvalue (optional) is 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 entire syntax in single quotes, for example: '@{user.displayName}' Note: If the @ sign is not followed by {, then it is treated as an @ sign.
"Product"."P9 Brand"= (@{MyTextVar}[‘@’]{Lotus})

Sunil Guddanti
Sr.Software Engineer
June 17th, 2020