OBIEE - How and where can I set a Request variable (SET VARIABLE) ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

A request variable is an OBI Server Session Variable but with the scope of the request. The value of the session variable will not be change for the session.

You can set it up in three main ways :

  • in a dashboard prompt with the help of the SET VARIABLE column.
  • by adding manually the SET VARIABLE clause in a logical SQL statement (into an answer, Dashboard prompt, Issue Sql, …)
  • with the Go Url
Advertising

3 - How to set a request variable

3.1 - With a dashboard Prompt

3.2 - In a OBIEE logical SQL statement

3.2.1 - In an Answer

3.2.1.1 - With a value

In an answer, in the advanced tab, you have the section “Advanced Sql Clauses”.

In the prefix field, you can add a “SET VARIABLE MYVARIABLE=MyValue;”.

Example with DISABLE_CACHE_HIT (to disable a cache hit):

SET VARIABLE DISABLE_CACHE_HIT=1;SELECT "Customer"."Name" FROM SH ...
3.2.1.2 - With a presentation variable

You can then use the value of a presentation variable to initialize a request variable.

Example of prefix with multiple presentation variables and request variables:

SET VARIABLE MyRequestVariable1='@{MyPresentationVariableName1}{DefaultValue}', 
MyRequestVariable2='@{MyPresentationVariableName2}{DefaultValue}';
Advertising

3.2.2 - In a Dashboard prompt

SET VARIABLE DISABLE_CACHE_HIT=1, MY_SESSION_VARIABLE=MyValue;
SELECT Calendar."Calendar Year" 
FROM SH

3.3 - With the Saw Url

4 - Support

4.1 - Repository variable

If you try to set a value for a repository variable, you get this error :

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. 
[nQSError: 43099] maxYear is a repository variable. You can only set the value of a session variable. 
(HY000) SQL Issued: SET VARIABLE maxYear='1996';SELECT Calendar."Calendar Year" saw_0 FROM SH WHERE 
Calendar."Calendar Year" = 1996 ORDER BY saw_0

You must use a session variable.