OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?

In the repository

The session variable

In the administration tool :

• go to Manage/Variable in the menu
• the variable manager open
• click on the node : Session/Variables/Non System
• and create a session variable MyYear with for instance this default initialization string for the oracle database

SELECT '1995' FROM dual

Setting the session variable

In the logical table source

In the logical table source TIMES Fact from the logical fact table Times, let's fill in the where clause with the repository session variable.

"orcl SH"."".SH."TIMES Fact".CALENDAR_YEAR =  VALUEOF(NQ_SESSION."MyYear")

In a physical table of type select

In the BI Presentation Service

In BI Presentation service, create :

• a dashboard prompt
• and a report

and add them into a dashboard.

The dashboard prompt

Create a dashboard prompt with the following characteristics :

• Column : Calendar.”Calendar Year”
• Control : Edit Box
• Set Variable : Request Variable and the value MyYear

The report

Select only the column Calendar.”Calendar Year”.

The dashboard

To see the value of the server variable, I have had a text box dashboard below the dashboard prompt with this code :

Session Variable My Year : @{biServer.VARIABLES['MyYear']} 

The result

Then when you set for instance the dashboard prompt to 1996 and hit the go button, you will retrieve this result :

The value of the repository session variable is not changed. The new value is only valuable for the query. It's why you can't see in the session manager a new value for the session.

The obiee logical sql request doesn't contain any filter :

SET VARIABLE MyYear='1996';SELECT Calendar."Calendar Year" saw_0 FROM SH ORDER BY saw_0

The logical request doesn't contain any filter :

-------------------- Logical Request (before navigation):

RqList  distinct 
    Times.Calendar Year as c1 GB
OrderBy: c1 asc

But the final query, send in the database, contains one.

-------------------- Sending query to database named orcl SH (id: <<103865>>):

select distinct T3883.CALENDAR_YEAR as c1
from 
     SH.TIMES T3883 /* TIMES Fact */ 
where  ( T3883.CALENDAR_YEAR = '1996' ) 
order by c1