Business Intelligence – Oracle

Oracle BI EE – Accessing Presentation, Repository and Session Variables from Presentation layer

Posted by Venkatakrishnan J on November 14, 2007

One of the common questions that keeps coming in the forums is “How does one access the Repository and Session variables defined in the repository from BI Answers?”. Since this is again a pretty common question i thought i would blog about it since there is a documentation bug on this one. Before we get into that lets first try to understand the types of variables that we have in BI EE.

1.   Repository Variables – There are 2 types of repository variables. Those that are static and those that are dynamic(these can be refreshed at regular intervals of time). These 2 are defined in the repository.

2.   Sesion Variables   –   There are 2 types of Session Variables. System Session Variable and Non-System Session Variable. The scope of these variables is for a particular logon session. The System session variables are predefined and we can override these predefined variables. These are defined in the repository.

3.   Presentation Variables   –   These variables are defined in the Presentation layer and their scope is within a dashboard.

Remember that Repository Variables can be assigned in 2 ways. One is by direct assignment within the repository(this is not possible for session variables) and the other is via Initialization Blocks. There is a small concept that we should understand for using these variables in Init Blocks. We will see that later. First lets us try to understand accessing static Repository Variables. Lets create a simple Repository variable called City and assign the value ‘ALAMEDA’ to it.


In order to access this Repository variable from Answers, we must use the following syntax.


In our case we would use



Now, Session variables can be assigned only via Init Blocks. In our case lets try to create a simple Session variable called setUser1 and the value for this variable would be obtained from a simple SQL statement

select ‘sample’ from dual

So, basically we are trying to assign setUser1 to the value sample. Also, lets specify a default value for the variable which is sample1. So, if the Init Block fires properly it would set the variable to sample else it will be set to sample1.



Now in order to access this variable from BI Answers we will be using the following syntax.


In our case, we would be using



Remember, if your underlying data in the database keeps on changing, your session variables (if you had used the SQL similar to the one showed above) would not refresh every time. In order to enable the refresh you need to add a dummy :USER in the where clause of your SQL query. Something like this

select ‘sample’ from dual where ‘:USER’ != ‘ ‘

If you want to use Presentation variables in the where clause of your report, you need to have the syntax below


For example,



20 Responses to “Oracle BI EE – Accessing Presentation, Repository and Session Variables from Presentation layer”

  1. majendi said

    Good to see the changed method being talked about. Any ideas how you put variables into the print section on a dashboard? You can put the variables in a text block, providing you have set HardenXss to False, but not in the print footer/header.

  2. Bob said

    Where are the session variables actually stored on the server? I am using Red Hat 64 bit.

  3. Venkatakrishnan J said

    The session variables are actually inside the repository. One can access them only via Answers or the Admin tool.

  4. Bindu said


    I tried to access the session variable in narrative view and I got an error message “expression is invalid”.

  5. Bindu said

    Never mind, I was missing a quote. It worked perfect.

  6. Peter said

    Hi Venkat.
    is it true, that variables cannot be used to change column headings dynamically? I am currently try to do this but haven’t found a solution that. All entries in custom headings seem to be interpreted as pure text.

  7. rye said

    Hi Venkat, is there a way to format presentation variables i.e. date? Can you show a sample? Thanks!

  8. Vern said

    I’m trying to use a presentation variable in the report header using @{myVariable} This works great on the dashboard, but if the user selects print (to HTML or PDF) or download to Excel, then the variable name is displayed literaly “@{myVariable}” instead of being substituted. I’ve tried this under and Is there a workaround for this?


  9. Turribeach said

    I almost lost 1/2 day trying to get this to work. In the end I found that in my system I could only get the variable with the following expression:


    I am not sure why the single quotes did not work, but double quotes did the trick.

  10. Venkatakrishnan J said

    @Turri – Which version are you on? And where did you use this? Was it in Answers report formula or header or static view etc?

  11. Trent Graham said

    This is awesome information on AQUIRING variable values!
    I am trying to do something simular … I want to pass the current dashboard page to a ‘user feedback’ request/page so users of our application can provide feedback on the dashboard pages as the navigate thru them. I have the writeback working and am able to catch the Username (using USER()) but there doesn’t seem to be a current/previous dashboard page session variable I can aquire/set.
    Is there a way to tap into the table shown in settings-administration-Manage Sessions … below Cursor Cache?

  12. Mark said

    Venkat, I certainly do appreciate you. Let’s see, the most recent documentation available from Oracle is from December 2006, 17 months ago. And all that time it simply hasn’t been important enough to Oracle to update it to correct many “documentation errors” such as how to reference repository variables correctly as you have shown us above.

    THANK YOU!! Whatever department handles documentation ought to be ashamed to pick up a paycheck every month.

  13. Mark said

    Venkat, can you show us an example of using the [FORMAT] option at the end of a variable reference? The manual NEVER shows an example of HOW to do it, and I think I’ve tried every conceivable possibility. I’m specifically trying to apply a custom date format to a date column.

  14. Jill said

    Venkat – I am trying to pass a refresh date to my dashboard requests…I have everything set up however, it’s returning repeated values.

    IE: Data is current through: 2008 / 052008 / 052008 / 052008 / 052008 / 052008 / 052008 / 052008 / 052008 / 05

    I believe that we have the repository variable set up correctly. Note: I’m calling the repository variable in my criteria section as the first column and then in the Narative section doing an ‘@1’. I tried putting a MAX(VALUEOF(“VAL_EMEA_CURR_MTH_VAR”)) around the variable in the criteria however, that did not seem to do anything. Is there something I’m missing or how to I get it to just return one date?

  15. Alex said

    I have the same problem with repeating values.

  16. pramod said

    HI venkat
    In this blog u had mention

    3. Presentation Variables – These variables are defined in the Presentation layer and their scope is within a dashboard.

    I need to use presentation variable in one page and now in other page of same dashboard i want to use this variable as a filter.

    I had tried but cant found a way of using 1 presentaion variable for different pages in dashboard


  17. green_tea said

    Hi Venkat,

    Sorry may be it’s a little Out of topic. I’m a newbie in BIEE and I have no idea with my problem. I need to get session ID that showed in Session Management. Do you know how to get it?

  18. hari said

    HI All,
    Can I get Session Variable in Repository Variable using Variable Manager? Or Can I get initialization block from session variable using repository variable?

  19. Greg said

    This article is simple, to the point and the only one I have seen that does not presuppose the variable exists before using it in a report. Well done.

  20. […] Oracle BI EE – Accessing Presentation, Repository and Session Variables from Presentation… […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: