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:
This variable we can use for a data restriction on the Logical Table Source of our address table.
Till Next Time