When you have create a server variable, you have to reference it.
You can use them :
For the syntax, add NQ_SESSION if the variable is a session variable and see the relevant section for the function call.
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) ?
For a session variable:
VALUEOF("NQ_SESSION.Variable Name")
For a repository variable:
VALUEOF("Variable Name")
You cannot use variables to represent columns or other repository objects.
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:
SET SESSION 'APPLICATIONUSER' = 'VALUEOF(NQ_SESSION.USER)'
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
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.
VALUEOF(NQ_SESSION."Variable Name")
@{NQ_SESSION.Variable Name}
You can call a server variable by using this syntax :
@{biServer.variables['VariableName']}
Example in a dashboard text object: