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
The folders commonly used for presentations are often priced higher than the plain folders that would be used in a classroom or office setting. Presentation folders are located with the regular folders but are distinguishable by quality and price.
ReplyDeletewow..this is cool tutorial to understand it, i like this post
ReplyDelete