Using multiple values row wise session variables in dashboard prompts and requests ~ OBIEE

In this post I'll show how to use multiple values session variable in the dashboard prompt and how to re-set it and use it in the request.

There are some questions in OTN forum like this one http://forums.oracle.com/forums/thread.jspa?threadID=962729&tstart=15 about using multiple values in variable and how to put them in the dashboard prompt as predefined values.

First at all the only way to achieve this is using multiple values row-wise session variable in initialization block. You are able to use it only in the WHERE condition of the SQL Results of the dashboard prompt and not in the prompt edit column formula.

If we do this in prompt edit column formula we get error:

The session variable, NQ_SESSION.VARIABLE, is defined as Row-Wise Initialization. It is restricted to usage with equality comparisons.

The same error we get if we make the same in the Answers edit column formula.

So if you intend to use it in the WHERE condition a mandatory thing is to have table in the presentation layer with the same data, to filter the same data that already exists in the table.

At this point I ask myself the question why would we use variable with multiple values at all? We could simply hardcode any value or any set of values in the SQL Results in WHERE condition of the dashboard prompt.

Note that you cannot use multi-select control and have possibility to set presentation or session variable from the dashboard prompt. So in this example we'll use drop-down list control and leave possibility to set session or presentation variable.

So if you have for example SQL Results like:

SELECT PRODUCTS.PROD_NAME FROM "Normal model" where PRODUCTS.PROD_CATEGORY=('Hardware', 'Electronics')

you could simply replace it with:

SELECT PRODUCTS.PROD_NAME FROM "Normal model" where PRODUCTS.PROD_NAME=VALUEOF(NQ_SESSION.PRODUCTS)

Where PRODUCTS session variable is a multiple values session variable that we get from the row wise initialization block.

With using multiple values in variable the advantage is that you don't harcode value in the dashboard prompt and the end user don't see the code and if you want to change set of values you do that in the RPD be changing the code of your initialization block.

So lets take one simple example of using multiple values row wise session variable.

Example one

Initialization block:

select 'PRODUCTS', prod_name from
(
select prod_name as prod_name from sh.products
where prod_category in ('Hardware', 'Electronics')
)

We use row-wise initialization:

Variable test:

So, this is our list of the products that we'll use in the dashboard prompt and request. We use only products that are in the list, for the Hardware and Electronics product group.

Report in Answers:

Dashboard prompt:

SQL Results:

SELECT PRODUCTS.PROD_NAME FROM "Normal model" where PRODUCTS.PROD_NAME=VALUEOF(NQ_SESSION.PRODUCTS)

Our list:

We put dashboard prompt and request on the dashboard page.

In the list we have only 15 products and at initial request start we get more products than we have in the list because the prompt is not filled with any products in the list and we get all products from the PRODUCTS table (we use IS PROMPTED in the request):

The same is for All Choices.

The query is:

select T21473.PROD_NAME as c1,
sum(T20550.QUANTITY_SOLD) as c2
from
SALES T20550,
PRODUCTS T21473
where ( T20550.PROD_ID = T21473.PROD_ID )
group by T21473.PROD_NAME
order by c1

How to get only products that are in the list, at initial start, not all products from the table?

1. Duplicate initialization block that sets PRODUCTS row-wise session variable:

In the new block write:

select 'PRODUCTS2', prod_name from
(
select prod_name as prod_name from sh.products
where prod_category in ('Hardware', 'Electronics')
)

2. In the dashboard prompt turn off All Choices:

In the SQL Results add the line:

union all
select case when 1=2 then PRODUCTS.PROD_NAME else 'All Choices' end From "Normal model"

3. Set request (session) variable PRODUCTS2 in the dashboard prompt:

4. In the Answers report remove IS PROMPTED and add in advanced convert this filter to SQL option:

(VALUEOF(NQ_SESSION.PRODUCTS2)='All Choices' and PRODUCTS.PROD_NAME=VALUEOF(NQ_SESSION.PRODUCTS)) or PRODUCTS.PROD_NAME = VALUEOF(NQ_SESSION.PRODUCTS2)

Explanation what we did:

At initial, the report goes to OR part and returns all products (all products that we have defined in the list). For that we use PRODUCTS2 session variable (cloned). We harcode All Choices in the prompt, and initialize it so we need to re-set session variable PRODUCTS2 with this value. If PRODUCTS2 are All Choices we use original PRODUCTS session variable to get again all rows (first part). And if we choose single value from the prompt we re-set PRODUCTS2 session variable and use the OR part of the query.

We could resolve this by set presentation variable in the dashboard prompt pv_products and use only this code in the report filter:

(
('@{pv_products}' like '%pv%' or '@{pv_products}' ='')
and PRODUCTS.PROD_NAME=VALUEOF(NQ_SESSION.PRODUCTS)
)
or PRODUCTS.PROD_NAME = '@{pv_products}'

If you are using presentation variable remove union all part from the SQL Results and leave All Choices box as enabled on the dashboard prompt:

Test

Initial start:

Only products that are included in the prompt list.

NQQuery.log:

Now, set value to Bounce and GO:

If you now look the NQQuery.log you'll see that we set PRODUCTS2 session variable with choosen value (Bounce):

The same result we get if we use the combination of presentation variable pv_products and multiple values row wise session variable.

Now lets look another example, more complex.

Example two

For the UserA and UserB we'll read product groups from our row-wise initialization table (in this example it is more like LOV table) and populate it into row-wise initialization block that return product list for each user and product group.

I'll use the row wise initialization table like in my previous post http://108obiee.blogspot.com/2009/03/external-table-authentication-and-row.html

Create four new entries for the user UserA and UserB in the table:

Initialization block:

Leave duplicated block from previous example and update the code inside:

When used, these blocks and coresponding row-wise session variables will return all products that users can see, UserA sees only products in the Hardware and Electronics and UserB Software/Other and Photo product group.

We used the same request with filter and dashboard prompt like in previous example.

Test

Log in with UserA:

Initial start (all products visible for UserA, 15 products):