[OBIEE 10g] OBIEE VALUELISTOF(NQ_SESSION.VAR_NAME)

OBIEE VALUELISTOF(NQ_SESSION.VAR_NAME)

On of the lesser known (and documented) features is the use of VALUELISTOF(NQ_SESSION.VAR_NAME) in the RPD.

As far as I know it’s only available in a session init block.

Let’s assume we have a row wise initiate variable called GeoArea:

This one is populated from our authorisation database and tells the system which areas a user is allowed to see.

Since we can only use single row session variables in the rest of the OBIEE system we have to transform it to a single string.

For this we can use the oracle LISTAGG function (see: http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions087.htm)

We make a new init block with:

SELECT LISTAGG ( COUNTRY_NAME, '; ')  WITHIN GROUP (ORDER BY AREA)
from (select distinct  AREA , COUNTRY_NAME from  SAMP_ADDRESSES_D ) T
WHERE AREA IN (VALUELISTOF(NQ_SESSION.GeoArea))

If we take a peek in the log we see that BI-server is translating this to:

SELECT LISTAGG ( COUNTRY_NAME, '; ')  WITHIN GROUP (ORDER BY AREA)  from (select distinct  AREA , COUNTRY_NAME from  SAMP_ADDRESSES_D ) T

WHERE AREA IN ('West','North','Central','South','South America','Northern','Middle East','East','North Africa','North America','Africa','Europe','Eastern')

The result we put in a session variable called GEO_COUNTRY_LIST.

Be sure to set the order in which the session variables should be loaded: