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

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

How to set a request variable

With a dashboard Prompt

In a OBIEE logical SQL statement

In an Answer

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 ...
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}';

In a Dashboard prompt

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

With the Saw Url

Support

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.


Powered by ComboStrap