About
When you have create a server variable, you have to reference it.
You can use them :
- in BI Server
- in Delivers
- in a logical sql statement through a client connexion.
For the syntax, add NQ_SESSION if the variable is a session variable and see the relevant section for the function call.
Articles Related
In BI Server
You can modify the value of the session variable with a dashboard prompt. This article gives an How-to : OBIEE - How to set a server variable with the session type via a dashboard prompt (with a request variable) ?
Syntax
For a session variable:
VALUEOF("NQ_SESSION.Variable Name")
For a repository variable:
VALUEOF("Variable Name")
Repository Objects
You cannot use variables to represent columns or other repository objects.
Connection Pool
OBIEE - Connection Pool: The picture below show a repository variable call but you can use in the same way the session variable syntax.
You can find an example of a dynamic essbase connection pool in this post of christian berg:
Connection script
SET SESSION 'APPLICATIONUSER' = 'VALUEOF(NQ_SESSION.USER)'
Opaque View / Select Statement
In the bookshel Version 10.1.3.2, it's written that Only repository variables can be used in the definition. An error will generate if a session variable is used in the opaque view.
With the version 10.1.3.4, a warning is generated:
WARNINGS: GLOBAL: The repository variable,
NQ_SESSION.MYYEAR, has no value definition.
But BI Server process it without problem.
-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/users/administrator/_portal',SAW_DASHBOARD_PG='MyYear',
SAW_SRC_PATH='/users/administrator/MyYear/Answers',MYYEAR='2001';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test
ORDER BY saw_0
+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16
-------------------- General Query Info:
Repository: Star, Subject Area: Test, Presentation: Test
+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16
-------------------- Logical Request (before navigation):
RqList distinct
TIMES_VIEW.calendar_year as c1 GB
OrderBy: c1 asc
+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16
-------------------- Execution plan:
RqList <<150282>> [for database 3023:156:orcl SH,31] distinct
TIMES_VIEW.calendar_year as c1 GB [for database 3023:156,31]
Child Nodes (RqJoinSpec): <<150293>> [for database 3023:156:orcl SH,31]
(select time_id, calendar_year
from times
where
calendar_year > VALUEOF(NQ_SESSION.MYYEAR)) as T6563
OrderBy: c1 asc [for database 3023:156,31]
+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16
-------------------- Sending query to database named orcl SH (id: <<150282>>):
select distinct T6563.calendar_year as c1
from
(select time_id, calendar_year
from times
where
calendar_year > 2001) T6563
order by c1
in the Name of a Catalog or Schema
You can use a variable to specify the names of catalog and schema objects. For example, you have data for multiple clients and you structured the database so that data for each client was in a separate catalog. You would initialize a session variable named Client, for example, that could be used to set the name for the catalog object dynamically when a user signs on to the Oracle BI Server.
The Dynamic Name tab is not active unless at least one session variable is defined.
In Presentation Service
Logical SQL in Answers
VALUEOF(NQ_SESSION."Variable Name")
In Delivers
@{NQ_SESSION.Variable Name}
Other places
You can call a server variable by using this syntax :
@{biServer.variables['VariableName']}
- The S of Server must be in uppercase. biServer is good where biserver is not good.
- variables is plural. variables is good where variable is bad
- Don't forget the quotes which enclose the Variable Name.
Example in a dashboard text object: