what is a presentation variable in obiee

Real World OBIEE: Demystification of Variables Pt. 2

Matthew Walding

In part one of this blog series, I went over using bins and presentation variables to dynamically create groups and switch between them in a report and on a dashboard. In part two, I am going to talk about making reports dynamic for periods of time using repository, system and presentation variables. Before I dive into an example, there are a couple of things I would like to cover first.

The sysdate function returns the current datetime set by the system where the database resides. Sysdate is a really useful function for creating repository variables for use with date dimensions. If I go into SQL Developer, I can write a query to return the current sysdate :

CURRENT_DATE

The current_date functions returns the current datetime set by the system where the bi server resides. This datetime may differ from sysdate depending on the geographical location of the database vs. the system that OBIEE resides on. I can write a query using sql developer to return the datetime using the current_date function:

Since my database and OBIEE instance are on the same system, sysdate and current_date are the same.

When using sysdate or current_date to create repository variables for dates (which I am going to show in an upcoming example), you have to keep something in mind. While the date may match, the time may not. To show an example of this, I am going to join one of my date columns with sysdate.

If I run this query, I don't get an error but I get no results.

Why? To answer this, I need to write a query to inspect my date column.

As you can see by the results of my query, the DIM_DATE_KEY column does have the same format as sysdate but all the times are set to 00:00:00 (or midnight). To further demonstrate the difference between my date column and sysdate , I am going to write a new query and use the TRUNC (or TRUNCATE ) function.

As you can see, the query runs successfully but notice how sysdate and DIM_DATE_KEY still have different times. How is the join possible? Because I used the truncate function in the where clause in my query for sysdate . Without going into too much detail, using truncate on a date function without any formatting (which I will cover later) will set (or truncate) the datetime to the start (or midnight) of the current day. For example, if I run another query that just selects the truncated sysdate from dual, I get this result.

Now, lets dive into an example.

Note: For all of the examples in this blog series I am using OBIEE 12.2.1.2.0

The Scenario

In this example, I have been asked to create a report that is going to reside on a products dashboard. It needs to have the same product grouping as the report I used part one of this series, needs to contain Gross Rev $, Net Rev $ and # of Orders and have a prompt that can select between the first and current day of the month and every day in-between. The person who requested the report wants the prompt to change dynamically with each month and does not want users to be able to select future dates.

There are two foreseeable challenges with this report. The first, and probably the most obvious, is how to make the date prompt for the current month and have it change dynamically with each month. The second is how to pass the dates into the report.

There is one more challenge that I will have to tackle. There is a gap in the data loads for # of Orders. Data does not update until the 2nd or 3rd of each new month. This wouldn't be a big deal except the person who requested the report wants a summary of the previous months # of Orders to be shown until the data is updated for the current month.

Fortunately, by using Repository, System and Presentation Variables, I can accomplish all of the requirements of this report.

The Example

For this example, I am going to start by creating Repository Variables to use with my date column in order to make the dates dynamic. There are other ways to make dates dynamic using functions within Answers but they are a little bit trickier to use and are less common. I am going to go over some of those functions in part three of this blog series.

Repository Variables are created using the Admin Tool. By launching the Admin Tool and opening my RPD in online mode (can also be created offline), I can go to Manage > Variables to start creating my first Repository Variable.

From the Variable Manager window, I can create a Repository Variable by selecting Action > New > Repository > Variable.

I am going to start by creating the Repository Variable for the current date. Since this variable will be dynamic, I need to make sure I select the option 'Dynamic' and I am going to give it the name USCurDate .

Now I need to create a new init block. I can do this by clicking New...

Once in the Repository Variable Initialization Block screen, I need to give the init block a name, set the schedule for when variable or variables will be refreshed then click Edit Data Source to define the connection pool the init block will use as well as the initialization string (query) the init block will use to populate the Repository Variable.

In the data source window, I am going to set my connection pool to one I have created just for my init blocks and then type in the following into the initialization string window:

If I click Test , the query will execute and will return a result.

Notice how the result is the same as the query I ran using SQL Developer earlier.

Now I need to create a Repository Variable for the first day of every month. I am going to use the same method as before and name it USMoBeginDate. The query I am going to use is slightly different from the previous query. I still need to use the TRUNC function but I also need to apply formatting so that it truncates to the start of the month. I am going to enter the following into the initialization string window:

Some other useful queries I can use are:

First Day of the Current Year

Last Day of the Previous Year

Previous Year Date

Now I need to create a Repository Variable for the previous month to use with my # of Orders measure column. Upon inspection, I discover that the column I need to use is called Calendar Year Month and is a VARCHAR or character type. If I go into Answers and pull in the Calendar Year Month column, I can see the format is ' YYYYMM '

To create the Repository Variable, I am going to use the same method as with the current date and first day of the current month Repository Variables and issue a new query. Because the Calendar Year Month column is a VARCHAR, I need to use the to_char function to change sysdate from a date type to a character type, use some formatting syntax and use some basic arithmetic. The query is as follows:

To break down each part of this query, lets start with the year. In order to use the 'YYYY' format I must first cast sysdate to a character ( to_char(sysdate, 'YYYY' )). Then I need to cast that result back to and int so that I can multiply by 100. This will give me the result 201500.00. The reason for this is when I add the month number to my yearx100 , there will always be a leading 0 for month numbers 1-9. To get the previous month number, I have to first cast sysdate to a character and use the formatting 'MM'. I then have to cast it back to an int and subtract 1 to get the previous month number ( to_number(to_char(sysdate, 'MM') -1 ) then cast the entire statment back to a character type so that it matches the type for the Calendar Year Month column. When I run the query, I get this result.

Now that I have my three repository variables (USCurDate, USMoBeginDate and Prev_Month) I can start to create the report.

Im going to fast forward a little bit to the part of the report creation process where I will use my Repository Variables I created using the Admin Tool. Since I am using virtually the same report as part one of this blog series, please refer back for how to create custom groups using bins and presentation variables and custom value prompts.

Because of the delay in the data load for the # of Orders at the beginning of the month, I can not use a global report filter. Instead, I am going to have to use something called a Filter Expression within each measure column formula.

About Filter Expressions

Unlike global report filters, column formula level filter expressions are used when you need to specify a particular constraint within the column formula itself. Because the filter is at the column formula level, it is independent of any subsequent column filters.

Note: When using a column formula filter for a measure, you can not add a global filter of the same data subject on top of it. For example, if using a column level filter for a particular Year and Month, I can not add a global filter for a particular year. The two filters contradict each other and the result will be null .

To add a filter in the column formula, go to Edit formula, make sure the column syntax is highlighted and click Filter.

From here the Insert Filter window will pop up and I can select the attribute column to filter the measure by. Here, I want to use the column Day Date to filter Gross Rev $ by the day.

I can add a column by double clicking it in the the Subject Areas pane. When a column is added, I will be prompted with a New Filter window and from here, everything is exactly the same process as adding a global report filter.

Here I need to define the operator as is between since we are dealing with date ranges. I could call my Repository Variables for current_date and first day of the month here but, because the request is for a prompt to select between date ranges, I am going to have to call Presentation Variables and use the prompt to populate the actual values.

Note: If you are unsure about the functionality of Presentation Variables, see part one of this blog series

To add Presentation Variables to the filter expression, click Add More Options and select Presentation Variable from the dropdown.

When a Presentation Variable is added to the filter, two new text boxes appear. The Variable Expr box is where you define the variable to be used and the (default) box is used to add a default value. The default value is optional but, when defining a Presentation Variable within a filter, you have to specify a default value in order to get any results. The reason for this is because, when the report is run, the query issued will use the Presentation Variable placeholder that is defined unless a default value is specified. In other words, the default value will always be used unless the Presentation Variable is populated with a value or a list of values.

Because I want the users to be able to specify a date range, I need to define two Presentation Variables: one for the start date and one for the end date. I can add another place for a Presentation Variable by simply clicking Add More Options again and selecting Presentation Variable.

Now I need to add both my start and end date Presentation Variables in the Variable Expr boxes. I’m going to call my start date presentation variable pv_start_dt and my end date presentation variable pv_end_dt . I am also going to specify a default date range from the beginning of the current month (10/01/2015) to yesterday's date (10/15/2015).

If I click OK, I will be taken back to the Insert Filter screen where I can see the filter expression previously defined.

Clicking OK again will return me to Edit Column Formula which shows the column formula with the filter expression defined in the previous steps.

Now I have to do the exact same thing for the Net Rev $ column. Since the filter expression is identical, I can simply copy and paste the column formula for Gross Rev $ and replace the column name in the expression.

Now I need to take care of the # of Orders column. This column is tricky because of the gap between the 1st and the 2nd or 3rd of every month. I could use a filter expression that defaults to the previous month by using the previous month repository variable I created in a previous step, but this alone wouldn’t switch over when the data became available.

So how can we fulfill the requirement of the report if we don’t know the exact date in which the data will be available? This can be accomplished by using a CASE statement as shown previously in part one of this series. We can break the Case statement down into two parts or two conditions:

1. When the day for the current month is less than or equal to 2 OR if # of Orders is null, then filter # of Orders by Calendar Year Month using the value of the Prev_Month Repository Variable.

2. When condition one is not true, then filter # of Orders by Day Date between the values of the pv_start_date and the pv_end_date Presentation Variables

Putting both conditions together and using the correct syntax for Column Formula results in the following formula:

Note that I am using CURRENT_DATE in my column formula. In this case, I am extracting the day number from the current date by using the extract day function ( DAY(CURRENT_DATE) ). I am going to talk about this in further detail when I talk about using built in functions in Answers to make reports dynamic in part 3 of this series.

There is one problem with this, however. Because of the arithmetic I am using to put current year and current month together, there will be a problem when the month number is 01 for January. The function will subtract 1 from 01 and put the month number at 00 and not 12 of the previous year. I can solve this problem using a CASE statement to switch to another Repository Variable that returns the last month of the previous year when the month is equal to January.

Going back to the Admin Tool, i'm going to create another Repository Variable and call it PREV_YR_LAST_MO

In the initialization string window, I am going to use this select statement:

Going back to my column formula, I need to insert a case statment to switch to my PREV_YR_LAST_MO Repository Variable when MONTH(CURRENT_DATE) = 1 .

Now I need to create my dashboard prompt. I am going to start by clicking on New > Dashboard Prompt.

I need to create two prompts: One for the start date and one for the end date. Because I am using presentation variables as placeholders for the date between values, I have to use a Variable Prompt instead of a Column Prompt. Variable Prompts allow us to define a presentation variable and then define a list of values for the users to select from.

To create a Variable Prompt for Start Date, I can click on the new prompt icon and select Variable Prompt.

There a few things I need to do in order to make this prompt function for the report. First, I have to define the same presentation variable name ( pv_start_dt ) that I used in the filter expressions for the Gross Rev $, Net Rev $ and # of Orders columns.

Because this is not a column prompt, I have to manually specify the values I want the user to be able to select from. Rather than typing in each value, I can use the SQL Results option from the Choice List Values dropdown and use a SQL statement to select the exact values that I want.

This may seem daunting at first but there is a very straightforward way to accomplish this. Rather than manually writing out a SQL query, we can make use of the Advanced Tab within a new report.

I’m going to start by clicking New > Analysis and selecting the column that I want values for: Day Date.

I need to add a filter to Day Date so that it returns only the values I want to user to select from.

Now I need to select the operator to be is between and add two Repository Variables that I have set up: one for the first date of the current month and one for the current date of the current month.

If I go to results, I can see the data returned with the filter I have specified.

As you can see, the Day Date column only contains the values from the first of the month to the current date (October, 16th 2015 in this example)

Now for the good stuff. I can navigate to the Advanced Tab and copy the SQL statement used to generate these values and paste them into the SQL Results text box in my prompt.

You will notice that within the SQL Statement generated by OBI, there are numbers and s_# between the SELECT and Day Date column, after the Day Date column and there is also an order by clause that uses a number “2”. Without going into too much detail, this what OBI uses to make the query more efficient when retrieving results from the database. In order to allow the values to populate the prompt, these have to be removed in OBIEE 12c and the “ORDER BY” clause has to be rewritten in order to make it work.

Changed to this

This can be a bit confusing if you are not very familiar with SQL but just remember:

When populating a prompt using an SQL statement in OBIEE 12c, take out any number and anything that begins with “s_” between the SELECT and first column and anything that begins with “s_” after any subsequent columns and make sure the “ORDER BY” clause contains the actual column name of the column you want to order by.

Note: If you do not require any values to be in order, you can omit the “ORDER BY” clause all together .

If I expand Options in the Edit Prompt window, I can add a default selection or a default value that the prompt will start with. I can use the USMoBeginDate here as well so that the prompt always starts with the first date of every month as the start date.

Note: You will notice that under Options in the Edit Prompt window there is a Variable Data Type option with a dropdown selector. This can be used if the data type needs to be specified to something other than the default which is ‘text’ or character type. If you are getting an error when running the report that says “Selected value does not match datatype. Expected [this value] but got [this value]” you need to change the Variable Data Type to the datatype of the column you are prompting on. In this example, we are prompting a date datatype so therefore it needs to be set to date .

If I click OK, I can check the values in the display window by clicking the dropdown for the Start Date prompt I just created.

The blue checkmark indicates the value that is selected which, because the first date of every month was set by using the USMoBeginDate Repository Variable as the default value, defaults to the first date of the current month (October, 1st 2015) in this example.

Now I need to create another Variable Prompt for the End Date. The SQL statement used for Start Date can be reused for the values as we want the exact same values to be available for selection. I am going to specify the presentation variable to be named pv_end_dt, and the default value to be the USCurDate Repository Variable so that the End Date prompt always defaults to the current date.

Now all that’s left to do is put the prompt and report on the Dashboard. Here is the result.

So that concludes part 2 of Demystification of Variables. Please feel free to ask questions or leave me a comment! In part 3, I am going to talk about using built in front end functions and presentation variables to make reports dynamic for any series of time. Until next time.

Sign up for more like this.

• OBIEE Tutorial
• OBIEE - Home
• OBIEE - Data Warehouse
• OBIEE - Dimensional Modeling
• OBIEE - Schema
• OBIEE - Basics
• OBIEE - Components
• OBIEE - Architecture
• OBIEE - Repositories
• OBIEE - Business Layer
• OBIEE - Presentation Layer
• OBIEE - Testing Repository
• OBIEE - Multiple Logical Table
• OBIEE - Calculation Measures
• OBIEE - Dimension Hierarchies
• OBIEE - Level-Based Measures
• OBIEE - Aggregates
• OBIEE - Variables
• OBIEE - Dashboards
• OBIEE - Filters
• OBIEE - Views
• OBIEE - Prompts
• OBIEE - Security
• OBIEE - Administration
• OBIEE Useful Resources
• OBIEE - Questions Answers
• OBIEE - Quick Guide
• OBIEE - Useful Resources
• OBIEE - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

OBIEE – Variables

In OBIEE, there are two types of variables that are commonly used −

• Repository variables
• Session variables

Apart from this you can also define Presentation and Request variables.

Repository Variables

A Repository variable has a single value at any point of time. Repository variables are defined using Oracle BI Administration tool. Repository variables can be used in place of constants in Expression Builder Wizard.

There are two types of Repository variables −

• Static repository variables
• Dynamic repository variables

Static repository variables are defined in variable dialog box and their value exists until they are changed by the administrator.

Static repository variables contain default initializers that are numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as date, time, etc. You cannot use any other value or expression as the default initializer for a static repository variable.

In older BI versions, the Administrator tool did not limit value of static repository variables. You may get warning in consistency check if your repository has been upgraded from older versions. In such case, update the static repository variables so that default initializers have a constant value.

Dynamic repository variables are same as static variables but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You can also set up a schedule that the Oracle BI Server will follow to execute the query and refresh the value of the variable periodically.

When the value of a dynamic repository variable changes, all cache entries associated with a business model are deleted automatically.

Each query can refresh several variables: one variable for each column in the query. You schedule these queries to be executed by the Oracle BI server.

Dynamic repository variables are useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains current orders and the other contains historical data.

Create Repository Variables

In the Administration Tool → Go to Manage → Select Variables → Variable Manager → Go to Action → New → Repository > Variable.

In the Variable dialog, type a name for the variable (Names for all variables should be unique) → Select the type of variable - Static or Dynamic.

If you select dynamic variable, use the initialization block list to select an existing initialization block that will be used to refresh the value on a continuing basis.

To create a new initialization block → Click New. To add a default initializer value, type the value in the default initializer box, or click the Expression Builder button to use Expression Builder.

For static repository variables, the value you specify in the default initializer window persists. It will not change unless you change it. If you initialize a variable using a character string, enclose the string in single quotes. Static repository variables must have default initializers that are constant values → Click OK to close the dialog box.

Session Variables

Session variables are similar to dynamic repository variables and they obtain their values from initialization blocks. When a user begins a session, the Oracle BI server creates new instances of session variables and initializes them.

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 session variables
• Non-system session variables

System session variables are used by Oracle BI and Presentation server for specific purposes. They have predefined reserved names which can’t be used by other variables.

Non-system session variables are used for setting the user filters. Example, you could define a non-system variable called Sale_Region that would be initialized to the name of the sale_region of the user.

Create Session Variables

In the Administration Tool → Go to Manage → Select Variables.

In the Variable Manager dialog, click Action → New → Session → Variable.

In the Session Variable dialog, enter variable name (Names for all variables should be unique and names of system session variables are reserved and cannot be used for other types of variables).

For session variables, you can select the following options −

Enable any user to set the value − This option is used to set session variables after the initialization block has populated the value. Example - this option lets non-administrators set this variable for sampling.

Security sensitive − This is used to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD).

You can use the initialization block list option to choose an initialization block that will be used to refresh the value regularly. You can also create a new initialization block.

To add a default initializer value, enter the value in the default initializer box or click the Expression Builder button to use Expression Builder. Click OK to close the dialog box.

The administrator can create non-system session variables using Oracle BI Administration tool.

Presentation Variables

Presentation variables are created with creation of Dashboard prompts. There are two types of dashboard prompts that can be used −

Column Prompt

Presentation variable created with column prompt is associated with a column, and the values that it can take comes from the column values.

To create a presentation variable go to New Prompt dialog or Edit Prompt dialog → Select Presentation Variable in the Set of a variable field → Enter the name for the variable.

Variable Prompt

Presentation variable created as variable prompt is not associated with any column and you need to define its values.

To create a presentation variable as part of a variable prompt, in the New Prompt dialog or Edit Prompt dialog → Select Presentation Variable in the Prompt for field → Enter the name for the variable.

The value of a presentation variable is populated by the column or variable prompt with which it is created. 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.

Initialization Blocks

Initialization blocks are used to initialize OBIEE variables: Dynamic Repository variables, system session variables and non-system session variables.

It contains SQL statement that are executed to initialize or refresh the variables associated with that block. The SQL statement that are executed points to physical tables that can be accessed using the connection pool. Connection pool is defined in the initialization block dialog.

If you want the query for an initialization block to have database-specific SQL, you can select a database type for that query.

Initialize Dynamic Repository Variables using Initialization Block

Default initiation string field of initialization block is used to set value of dynamic repository variables. You also define a schedule which is followed by Oracle BI server to execute the query and refresh the value of variable. If you set the logging level to 2 or higher, log information for all SQL queries executed to retrieve the value of variable is saved in nqquery.log file.

Location of this file on BI Server −

ORACLE_INSTANCE\diagnostics\logs\OracleBIServerComponent\coreapplication_obisn

Initialize Session Variables using Initialization Block

Session variables also take their values from initialization block but their value never changes with time intervals. When a user begins a new session, Oracle BI server creates a new instance of session variables.

All SQL queries executed to retrieve session variable information by BI server if the logging level is set to 2 or higher in the Identity Manager User object or the LOGLEVEL system session variable is set to 2 or higher in the Variable Manager is saved in nqquery.log file.

Create Initialization Blocks in Administrator Tool

Go to Manager → Variables → Variable Manager Dialog box appears. Go to Action menu → Click New → Repository → Initialization Block → Enter the name of initialization block.

Go to Schedule tab → Select start date and time and refresh interval.

You can choose the following options for Initialization Blocks −

Disable − If you select this option, initialization block is disabled. To enable an initialization block, right-click an existing initialization block in the Variable Manager and choose Enable. This option enables you to change this property without opening the initialization block dialog.

Allow deferred execution − This allows you to defer the execution of the initialization block until an associated session variable is accessed for the first time during the session.

Required for authentication − If you select this, initialization block must execute for users to log in. Users are denied access to Oracle BI if the initialization block doesn’t execute.

• Install App

Analytics Software

For appeals, questions and feedback about Oracle Forums, please email [email protected] . Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle has updated its online  Terms of Use  and  Community Guidelines  and introduced a  Community Integrity Policy . These changes document Community specific rules and Oracle’s content moderation practices including use of automated tools, appeals process, and Oracle’s contact details. If you object to any changes, you may request that your account be closed by contacting  [email protected] . Your continued use of Oracle Communities means that you are consenting to the updated terms.

Differentiating Presentation, Request Variables.

• Creating Analyses and Dashboards in Oracle Transactional Business Intelligence
• Report Data
• Create Analyses

Advanced Techniques: Reference Stored Values in Variables

You might want to create an analysis whose title displays the current user's name. You can do this by referencing a variable.

You can reference several different types of variable in your analyses, dashboards, and actions: session , presentation , request , and global . Content authors can define presentation, request, and global variables themselves.

About Session Variables

Session variables are initialized when a user signs in.

These variables exist for each user for the duration of their browsing session and expire when the user closes the browser or signs out. There are two types of session variable: system and non-system.

System Session Variables

There are several system session variables that you can use in your reports and dashboards.

The system session variables have reserved names so you can’t use them for any other kind of variable.

Non-System Session Variables

The non-system session variables are named and created in your semantic model.

For example, your data modeler might create a SalesRegion variable that initializes to the name of a user's sales region when they sign in.

About Repository Variables

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

Repository variables can be static or dynamic. A static repository variable has a value that persists and doesn’t change until the administrator changes it. A dynamic repository variable has a value that is refreshed by data returned from queries.

About Presentation Variables

A presentation variable is a variable that you can create as part of the process of creating a column prompt or 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.

About Request Variables

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

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

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, you must select Request Variable in the Set a variable field. Enter the name of the session variable to override in the Variable Name field.

A request variable that is created as part of a variable prompt isn’t 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 must select Request Variable in the Prompt for field. Then enter a name of the session variable that you want to override in the Variable 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.

Certain system session variables (such as, USERGUID or ROLES) can’t be overridden by request variables. Other system session variables, such as DATA_TZ and DATA_DISPLAY_TZ (Timezone), can be overridden if configured in the Oracle BI Administration Tool.

Only string and numeric request variables support multiple values. All other data types pass only the first value.

About Global Variables

A global variable 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.

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 the BI Service Administrator role can manage (add, edit, and delete) global variables.

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.

Create Global Variables

You can save a calculation as a global variable then reuse it in different analyses. By just creating a global variable, you don’t have to create a new column in the Data Modeler .

• Open the analysis for editing.
• Select Edit Formula to display the Column Formula tab.
• Click Variable and select Global .

"Base Facts"."1- Revenue"*@{global.variables.gv_qualified}

• If you’re selecting "Date and Time" as the data type, then enter the value as in the following example: 03/25/2004 12:00:00 AM
• If you’re 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
• Click OK . The new global variable is added to the Insert Global Variable dialog.
• Select the new global variable that you just created, and 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 reflect the variable more accurately.

Syntax for Referencing Variables

You can reference variables in analyses and dashboards.

How you reference a variable depends on the task that you’re performing. For tasks where you’re 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 that you use depends on the type of variable as described in the following table.

You can also reference variables in expressions. The guidelines for referencing variables in expressions are described in the following topics:

Session Variables

Presentation variables, repository variables.

You can use the following guidelines for referencing session variables in expressions.

• Include the session variable as an argument of the VALUEOF function.
• 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.

For example:

"Market"."Region"=VALUEOF(NQ_SESSION."SalesRegion")

You can use the following guidelines for referencing presentation variable in expressions.

When referencing a presentation variable, 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 isn’t populated.

To type-cast (that is, convert) the variable to a string or include multiple variables, enclose the entire variable in single quotes, for example:

'@{user.displayName}'

If the @ sign isn’t followed by a {, then it’s 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 '})

You can use the following guidelines for referencing repository variables in expressions.

• Include the repository variable as an argument of the VALUEOF function.
• Refer to a static repository variable by name.
• Refer to a dynamic repository variable by its fully qualified name.

CASE WHEN "Hour" >= VALUEOF("prime_begin") AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

OBIEE TRAINING

OBIEE , INFORMATICA, OBIA DAC TRAINING FOR DETAILS CALL 09959531832 email ID: [email protected]

OBIEE 11G Using a Presentation Variable

Using a Presentation Variable in a Static Text View

No comments:

Post a comment, popular posts.

• Timestampadd https://forums.oracle.com/forums/search.jspa?forumID=378&threadID=&q=%22timestampadd%22+&objID=f378&dateRange=all&userI...
• TimestampDiff and TimestampADD( Difference of two dates) in OBIEE How to get a difference between two dates (in terms ) of days,weeks,months what every it may be The below formula gives you no.of days b...

• Center Dashboard Prompt In OBIEE, the dashboard prompts are aligned to the left by default. One of the OBIEE developers I am working with asked me to fin...

• Aggregate Tables
• DataBase Schema
• Fusion Middleware
• New Features
• OBIEE Admin
• OBIEE Developer
• Performance
• Report Creation
• Repository Upload Issue
• Rpd Download Issue

23 February 2012

Using variables in obiee.

• Conversion Functions OBIEE Conversion Functions The conversion functions convert a value from one form to another. You can also use the VALUEOF functio...
• Using Variables in OBIEE You can reference variables in several areas of Oracle BI Enterprise Edition, including in analyses, dashboards, KPIs, actions, age...
• Database (Evaluate) Function Database Functions ( EVALUATE ) Users and administrators can create requests by directly calling database functions from either Oracle...

Blog Archive

• March 2020 (1)
• July 2019 (1)
• December 2016 (1)
• April 2015 (2)
• December 2014 (1)
• November 2014 (1)
• January 2013 (1)
• March 2012 (7)
• February 2012 (2)

About Blogger

OBIEE - Datatype of a presentation variable

Each variable in an application have a scope and a data type .

Articles Related

• OBIEE - Presentation Variables
• OBIEE - Date Datatype and Functions
• OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?

How can I get the data type of a presentation variable ?

When you set a presentation variable with a dashboard prompt, the presentation variable takes the data type of the value in the column field.

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

How to add a presentation variable in the logical sql in obiee 11g

I make a dashboardprompt and I want to make a default selection starting from an logical sql. In th options I choose as default selection "SQL Results". In my query I want to put a filter with a presentation variable. I thought to use this query:

But I don't know how to put the presentation variable. Like this code, it does not work.

• Why this convoluted approach? 9 times out of 10 it's simply a wrong way of handling things and easily done with out-of-the-box functionality. What's the goal? –  Chris Commented Mar 13, 2018 at 17:55
• I have two prompts. When I select in the first prompt (with presentation varable P_pos_id) a position identifier, I want the second prompt to be default selected by this query above. I don't want to filter the second prompt, I just want to change the selection, but I want to be able to select other things too. –  Bigjo Commented Mar 14, 2018 at 19:44
• So what's your problem? The approach will work. Technicallt that's valid. Are you using two distinct prompt objects or just two columns in one prompt? –  Chris Commented Mar 15, 2018 at 9:05
• two distinct prompts. The first time it works now, but when I change one prompt, the second prompt does not change –  Bigjo Commented Mar 16, 2018 at 12:26
• Auto-applying prompts or ones with buttons for explicit execution? –  Chris Commented Mar 16, 2018 at 18:07

Know someone who can answer? Share a link to this question via email , Twitter , or Facebook .

Your answer.

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Browse other questions tagged obiee or ask your own question .

• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Feedback requested: How do you use tag hover descriptions for curating and do...

Hot Network Questions

• What is the reason why creating a veth requires root?
• Can one repair an "Amen Chatufa"?
• How predictable are the voting records of members of the US legislative branch?
• how to label a tikz path, where the node label extends outward perpendicular to the path at a point
• Simple JSON parser in lisp
• UART pin acting as power pin
• 1 amen for 2 berachot?
• Is my encryption format secure?
• How do you "stealth" a relativistic superweapon?
• How to read data from Philips P2000C over its serial port to a modern computer?
• Guitar amplifier placement for live band
• Is Hilbert's epsilon calculus a conservative extension of intuitionist logic?
• Is supremum of a continuous function continuous either?
• What is the origin and meaning of the phrase “wear the brown helmet”?
• A man hires someone to murders his wife, but she kills the attacker in self-defense. What crime has the husband committed?
• Age is just a number!
• Short story or novella where a man's wife dies and is brought back to life. The process is called rekindling. Rekindled people are very different
• The complement of a properly embedded annulus in a handlebody is a handlebody
• Restarted my computer and the display resolution changed
• Do temperature variations make trains on Mars impractical?
• Who became an oligarch after the collapse of the USSR
• MOSFETs keep shorting way below rated current
• What majority age is taken into consideration when travelling from country to country?
• How would a culture living in an extremely vertical environment deal with dead bodies?