About
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 :
- to create a year session variable
- to include it in a “where clause” of the content tab of a logical table source
- to create a dashboard prompt to set up this session variable
- and to test it on a little answer with only the year as column
Articles Related
How-to
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
Support
The repository variable, NQ_SESSION.MyYear, has no value definition.
In the repository
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.
In the presentation service
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).
DISABLE_CACHE_HIT always with quotes
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