Saturday, June 7, 2008

OBIEE Flexible Dashboards

One of our customers wanted a “flexible”dashboard. They wanted to select a x number of trucks and have an individual graph for each truck. The number of selected truck varies between 0 and 5.
We made this possible by using the RANK function in OBIEE. We made a simple report for the “licence plate” dimension:



Have a look at SQL going to the database:

If we put a filter (fi: 3) on the RANK we get:

You see that the filtering is done after getting the RANK number from the db.
Now save this report 5 times were you put a different number in each rank filter (SEL1, SEL2, SEL3, SEL4 and SEL5) .
To test this make a dashboard page with a dimension prompt and the 5 SEL reports:

Select 1 truck:

Select 3 trucks:

Now make your “real” reports and filter them “Filter based on results of another request.


Set your NO RESULTS view do a simple ‘-’

Do this also 5 times every time on a different SEL report. (RES1, RES2, RES3, RES4 and RES5)
Put the 5 RES reports to your dashboard instead of the SEL reports.

Check if it works:

Till Next Time
(this text is also published on http://knowledge.ciber.nl/)
NOTE: in some cases using RCOUNT instead of RANK works better, there is a known issue of OBIEE RANK function when numbers are strings.

No comments: