[OBIEE 10g] Variables In Direct Database Requests

 

Variables in direct database requests

In Answers we have possibility to write SQL directly to the database.

This is how to use repository, session and presentation variables in direct database request and whether this is possible or not and compare this with normal Answers request.

Repository initialization block:
select to_char(min(time_id), 'dd.mm.yyyy') from sales

Repository variable (dynamic):
rv_test_date_to_char

Session initialization block:
select 'Photo' from dual

Non-system session variable:
CAT, enable any user to set the value checked, without default initializer

Dashboard prompt fields:
PRODUCTS.PROD_CATEGORY, drop-down list, set request variable CAT
CHANNELS.CHANNEL_DESC, drop-down list, set presentation variable pv_channel_desc

The first one re-sets session variable and the second one sets presentation variable.

Normal Answers request columns and filter:
PRODUCTS.PROD_CATEGORY
VALUEOF(NQ_SESSION.CAT)
VALUEOF(rv_test_date_to_char)
'@{pv_channel_desc}'
PRODUCTS.PROD_CATEGORY is prompted

 

Direct database request:

SQL statement:select
'VALUEOF(NQ_SESSION.CAT)' session_variable,
'VALUEOF(rv_test_date_to_char)' repository_variable,
'@{pv_channel_desc}{Internet}' presentation_variable,
channel_desc
from channels
where channel_desc='@{pv_channel_desc}{Internet}'

We see inside the statement what is the syntax for referencing variables, for that I know that works correctly.

Now if we put all three objects in the dashboard page at initial we get this:

 

We change values from the prompt and re-set session and presentation variable:

Everything works fine in the direct database request except we cannot view new value of non-system session variable no mather how many times we refresh (re-set) it, it only takes value that we defined in the initialization block code. New (refreshed) value affects only Answers request.