OBIEE 12c REPORT BURSTING
Something came up this week that I was surprised hadn’t come up before. And I was looking around thinking there must be an obvious way to do this but I couldn’t find it.
The idea was to use a single report that could be sent to different people with an appropriate filter to give them only the content they needed: i.e. bursting. You can do this is BI Publisher but I’ve never tried it in OBIEE itself.
All we needed was a variable that we could set with a parameter at run time but that doesn’t seem to be an option in the Agent screen so I had to be more creative.
The report I’ve used here is Sales by Region and the idea is to filter by region based on the recipient. It’s also a Direct SQL query as we were trying to make this work for operational reports:
So, to do this I set up two new users in Weblogic, “North” and “South”. These could be real users (with a Region lookup table etc.) but then again, why not just set one up for each region?
The next step was to create a Session Variable to set the Region for each user. Because I’m users generic region users I could do this using a simple case statement in the Init Block:
select CASE
when upper(‘VALUEOF(NQ_SESSION.USER)’)= ‘NORTH’ then ‘North’
when upper(‘VALUEOF(NQ_SESSION.USER)’) = ‘SOUTH’ then ‘South’
else ‘All’
END
from dual
The target variable was called vRegion so I edited the report SQL to use it as a WHERE clause:
select *
from sales
where (region = ‘VALUEOF(NQ_SESSION.vRegion)’
OR ‘VALUEOF(NQ_SESSION.vRegion)’ = ‘All’ );
I tested this by logging in as the new users and it worked perfectly. The USER session variable is set upon log in and that in turn sets the vRegion value.
So, now I just needed to get the Agent side set up. I created a new agent and changed the ‘Run As‘ setting to ‘Recipient’.
Then I picked the analysis to deliver and added the two new users to the Recipients list:
Now when I run the Agent it runs the analysis as each of the users on the list and sets the USER and vRegion session variables for each one.
So, logged in as ‘South’ I get:
And as ‘North’ I get:
No Comments