Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Updating Session Variables from Dashboards using Presentation Variables

Posted by Venkatakrishnan J on February 25, 2008

This question keeps repeating so many times that i thought this in itself deserves a seperate blog entry. There are 2 parts to this question. They are

1. Updating Session Variables from Answers
2. Updating Session Variables using Presentation Variables from a Dashboard Prompt

The first one is pretty straightforward. Lets start with a simple example. Lets create a simple session variable called Test_Var and make that variable to be initialized from an init block. Enable that variable to be set by any user. If you are using row-wise initialization then by default the variable will allow any user to set the variable.

      

Now, go to Answers and create a report which will show the Session Variable.

      

      

      

Now, as you see the session variable has a value of Test. Now, in order to update this variable, go to the Advanced Tab->Advanced SQL Clauses in Answers. And enter the below formula in the Prefix section (Do not click on Set SQL. This is executed when you move away from this tab)

SET VARIABLE Test_Var=’Test1′;

      

Now, also ensure that you are bypassing Presentation Services Cache.

      

If you go to the Results tab, you should notice that your Session Variable Value would have changed.

      

This has a lot of use cases. For example, you can set LOGLEVEL variable dynamically for every report. I shall also be blogging about other possible use cases later this week. This was pretty straightforward. But what if we want to set these variables from a dashboard prompt. In order to do this, lets create a simple dashboard prompt on any column and lets make it to set a presentation variable named Test

      

Now go back to the report that we created above and navigate to Advanced Tab. Now in the prefix type in the below formula

SET VARIABLE Test_Var=’@{Test}’;

      

Now, save this report and go to the dashboard and include the prompt and the report that we created. Enter any value in the Edit Prompt and click on go. You would notice that the Session Variable Value changes based on the value that was entered in the dashboard prompt.

      

This is again very useful for making update calls from the presentation layer to the BI Server.

About these ads

21 Responses to “Oracle BI EE 10.1.3.3/2 – Updating Session Variables from Dashboards using Presentation Variables”

  1. ia said

    Hi Venkatakrishnan,

    Is this actually saving the values to the repository or only overriding the value of a session variable in that specific request?

    I’ve tested it with LOGLEVEL and had the desired override (LOGLEVEL=2) for a specific request but for all the other requests LOGLEVEL still had a default value (LOGLEVEL=0) coming from the repository initiation block I created.

  2. Venkatakrishnan J said

    It overrides the value for that session. It would revert back to the older value set in the RPD once you log out and log back in.

  3. [...] out by Swapan of the BI EE forums today here. If you had gone through my previous blog entry here, i would have shown you how to go about updating the session variables from within Answers. Also, i [...]

  4. Very interesting and useful post.
    Thx, your blog in my RSS reader now ;)

  5. David Andersen said

    I can’t get this syntax:

    SET VARIABLE Test_Var=’@{Test}’;

    to work. In my report it displays as the literal: @{Test} with no substitution. Is there by change an error in this post regarding the syntax of the SET VARIABLE command with a Presentation variable? I can SET VARIABLE with static text (the first part of this post).

    Thanks.

  6. Reg said

    Venkat,

    Awesome document, very very useful.. However I as well am getting the same error as above. It’s simply showing the @{blah} being returned instead of the actual value which is being passed from the presentation var being set by the prompt. Any ideas?

    Thanks!
    -Reg

  7. David Andersen said

    Could this be an OBI version issue? I’m trying this on version 10.1.3.3.0. Your article references 10.1.3.3.2.

  8. Venkatakrishnan J said

    David – I dont think this is a version issue. SET VARIABLE Test_Var=’@{Test}’; — In this command remove the single and type them manually again. My blog converts single and double quotes into some special characters.

  9. David Andersen said

    Venkat – I actually keyed my SET statement directly, so there is no issue copying the wrong characters from your blog. I haven’t yet tried to do this on a higher version but I suspect this may be the problem. Nothing else makes sense.

  10. Sid said

    Venkat,
    thanks for sharing this information. Is there a way to specify a mix of SQL and presentation variable together and get the session variable? For example, I can have a product key session variable and I can allow customers to choose one product description and set the session variable based on an SQL and product description presentation variable.

  11. Reg said

    Venkat,

    This was a very nice mini paper you did. I was the one who posted above about not being able to set variables, and it was related to being product version 10.1.3.3.0, it does work in 10.1.3.3.2 or higher it seems.

    However I’ve come across another problem and I would like to see if anyone else has as well had the same problem. When you turn on report links and use the download data option you are simply left with the variable specified in your pre-sql opperation. As well if you were to set this var in on answers doc and right below in another answers doc refer to the exact same var, it doesn’t seem to hold it for a “session” seems more of a per request type variable. Ideas? Input?

    -Reg

  12. mirenh said

    Can multiple values be assigned to the variable, so it can be used in the where clause with the in operator?, like this:
    SELECT emp WHERE deptno IN (’10′, ’25′, ’30′)
    being the values ’10′, ’25′, ’30′ the result of a query, vg some report, prompt, etc…

  13. Karina said

    Hello,
    I would like to pay your attention to the issue regarding the return value when posting SET VARIABLE Test_Var=’@{Test}’, and waiting for the value of Test variable but taking literal display – @{Test}.

    Does someone find the solution for this problem?

    Thanks!

  14. Rizwan said

    hi the literal is displayed when u run the report standalone… that is you open the report directly from the folder.

    The value is displayed only when the report is opened on a dashboard.

    Regards

    RIZWAN SM

  15. Robert said

    Hello,

    In my report it displays as the literal: @{Test}.
    Where be the problem? I have the version 10.1.3.3.0

    Thanck.

  16. Iza said

    Hi Venkat,
    Thank you for your blog – I find answers to most of my questions here.
    However right now I’m stuck with session (or request) variables and passing them to reports.
    I want to create a dashboard page with dashboard parameter for period and a list of requests (expanding one folder in dashboard). I want to open those requests already filtered for the period set on dashboard prompt.
    I’m using version 10.1.3.4.0. and there is option for dashboard prompt to set “Request variable” which as I understand is the same as session varible in Administrator. I have created session variable ‘Period’ and dummy init block on dual table. I also have created simple report to show valueof variable. The dashboard prompt seems to update session variable (it is displayed in the report which is run directly in dashboard), but variable value is set to default whenever I open report from list. I need the variable to stay the same whole session unless I change it in dashboard prompt – may be there is some option in init block which I miss?

  17. Neo said

    Hi Venkat,
    Thank you for your blog,
    The session variable was set at moment of refresh and not for entire session…why? thank you for yout reply.

  18. Varun Malhotra said

    Hi Venkat

    Nice blog. I always check your blogs and got lot of information from them. I hope you wil help me with the below requiement.

    Dashboard Page 1 (D1) contents:
    Dashboard Prompt (DP1)
    Title (T1)
    Report (R1)
    Narrative (N1)

    Dashboard Page 2 (D2) contents:
    Dashboard Prompt (DP2)
    Title (T2)
    Report (R2)

    Present Condition: Value of DP1 is used in T1 and R1.
    Requiement: N1 calls D2 and value of DP1 needs to be passed to T2 and R2.

  19. Tomorrow said

    Can you set multiple variables in Prefix? I tried and no success.

  20. Ultrecht said

    Hi. Can you set variable with column value?

  21. Michelle said

    Hello,

    I am a new user to OBI and am a bit confused to the presentation and session variables. I have shown a report using both Presentation Variables and Session Variables. Both have been configured correctly, but the Presentation Variable method does not yield the grand totals correctly.

    We want users to be able to use Presentation Variables, and not always have to do RPD modification for Session Variables.

    The reason is that aggregation happens at the highest level when using Presentation Variables. It is not possible to get the results that we are looking for with Presentation Variables.

    Thank you!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: