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

Bi Server Architecture With Client

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

Obiee Answers Advanced Sql Clauses

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;”.

Obiee Set Disable Cache Hit

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

Obiee Set Variable Dashboard Prompt

With the Saw Url

OBIEE 10G - How to set a request/session variable using the Saw Url (Go/Dashboard)

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.





Discover More
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Obiee Dashboard Prompt Scope
OBIEE - Request variable

Request Variables are used to update Session Variables in the scope of a logical sql. You will find it in a OBIEE logical Sql in its SET VARIABLE part. For instance : Its aim is to set an OBI server...
Obiee11g Default Logging Level
OBIEE 10G/11G - Setting a Query Logging Level

The login level is a parameter which control the number of information that you will retrieve in the log file from nothing (level 0 - no_log_found) to a lot of information (level 5). You can enable logging...
Obiee 10g Server Variable Session Repository
OBIEE 10G/11G - The (dashboard|column) prompt

The dashboard prompt is the most used prompt to filter rapport in a dashboard but it can also set: a presentation variable variable prompt a request variable In 10g, for a repository...
Obiee Set Disable Cache Hit
OBIS - Purging / Disabling the Query Cache

How to purge the Query Cache To disable completly the query cache, you must set to the ENABLE cache parameter the value NO in the file nqsconfig. For one session, you have to set the session...



Share this page:
Follow us:
Task Runner