A common way to set up a request variable in order to set a session repository variable is to create a dashboard prompt.
This article show you with the help of the sh schema a little example.
The goal is :
In the administration tool :
select '1995' from dual
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 BI Presentation service, create :
and add them into a dashboard.
Create a dashboard prompt with the following characteristics :
Select only the column Calendar.“Calendar Year”.
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']}
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
You can get this warning when you perform a check global consistency.
WARNINGS:GLOBAL:The repository variable, NQ_SESSION.MyYear, has no value definition.
You can leave it away.
The presentation service can also send this error :
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 23006]
The session variable, NQ_SESSION.MyYear, has no value definition. (HY000)
SQL Issued: SET VARIABLE MYYEAR='1998';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test ORDER BY saw_0
Try to use the same letter case for the name of the variable (upper and lower).
If you can get to set a number request variable because it always add quotes, it's a bug because the value is independent from the data type of your presentation variable (works for the version 10.3.4.1).
SET VARIABLE DISABLE_CACHE_HIT='1';SELECT Calendar."Calendar Year" saw_0
FROM SH ORDER BY saw_0