Thursday, April 11, 2013

OBIEE - selecting session variables using presntation variable

I believe you know everything about OBIEE variables: Session Variables, Repository Variables, Presentation Variables and Request Variables. If you don't, they are lots of good material about it. For example HERE , HERE or HERE.

I had a special need: we created several Session Variables with Row Wise initialization, so each had several values. We wanted the user to be able to select the Session Variable, to be used in the query, from a prompt.   
In my case in each Session Variables they were several dates and I wanted the user to be able to select in a Dashboard Prompt, one of the lists. (In normal condition I can make a regular "date between" filter. This was not such case.)

So assuming we have a regular Session Variable named "last7", I could create a filter in the criteria such as:

"Time"."Date" = VALUEOF(NQ_SESSION."last7")

But I want the user to be able to select: "last1" or "last7" or "last30". What can we do?

A business partner I worked with, Yeal Uziel from Vision BI, had a great idea: pass the session variable name using Presentation Variable.

So we created a Presentation Variable named LastN:
 As you probably remember calling this presentation variable syntax is:@{LastN}. Or with default value:@{LastN}{"last7"}.

So what we did was combining the 2.  We took the Session Variable filter:

"Time"."Date" = VALUEOF(NQ_SESSION."last7") 

and added the presentation variable instead:

"Time"."Date" = VALUEOF(NQ_SESSION.@{LastN}{"last7"})

 And thats it. It's working. Now user can select the Session Variables in the prompt!




1 comment:

  1. Hi ,

    My requirment is need to filter initialization block(session variable) based on the selection of dashboard prompt.I have create presesntation variable and i passed the same to inialization block but it didnt work Please let me know any suggesion

    fiscal_year_wid=VALUEOF(NQ_SESSION.@{PV_YEAR})

    ReplyDelete