Creating Pivot Table-Specific Filters in Oracle BI Answers
March 6th, 2009 by Mark Rittman
If you’ve taken a look at the Sample Sales dashboards that ship with OBIEE 10.1.3.4, you may have worked out that most of the dashboard pages use a single compound view containing multiple pivot tables, charts and tables. As such, they present many different views of data to users whilst only running a single database query per page, something that’s quite useful when you’ve got expensive database queries and lots of users.
If you look at how these multiple views are created, the criteria behind the request usually brings across lots of dimension attributes, lots of measures, and each of the views picks a particular set of attributes and a particular measure and displays the results as a graph, table or crosstab. Now the customer I was working with today had seen these dashboards and wanted to do something similar, but with an interesting twist – they wanted to create views on a single measure, with a consistent set of dimension attributes, but each view needed to have its own filter, so that for example, view one showed sales for one state, view two showed sales for a grouping of other states, and view three showed sales for all the other states. So is it possible to create a set of views that use their own filters, and include them all in one single compound layout?
To start off I created a simple request, like this, that brought across sales by product name and state:
Then I created a pivot table, that listed out products and state on the rows, and quantity sold on the columns.
So what I’m looking to do now, is create three of these pivot tables, one that filters on just CA, another that filters on NY and MI, and another that filters on TX and WA. To create the first of them, I use the New Calculated Item button next to the State column in the pivot table control, like this:
Then I create a new calculated item based on the ‘CA’ column value, and call it CA, like this:
When I display this new item, along with the regular dimension table values, I get an extra “CA” entry for every CA that’s coming up in the results.
If you then bring up the menu for the State measure again, and this time select “Hide Details”, the pivot table then hides the “regular” column values for this column and only shows the one you just created, effectively filtering the pivot table on this particular state.
You can even then hide this State column if you wish by using the “Hidden” option on the same menu, to just show the products sold for your particular state selection. If you then repeat this across other pivot tables and for other state selections, you can create your single compound layout with multiple view, each with their own individual filter. In the example below, we’ve taken it one stage further and added separate graphs for each view, with labels above to show which set of states they refer to.
Not bad, and it means you can create a single request that shows data using many different filters but with just a single database query that drives it.
March 9th, 2009 at 5:49 pm
In trying out this method I’ve found that putting in totals for each of the state groupings is a problem. I’m getting totals that represent grand total of all 3 state groupings. Can’t figure way around this.
March 12th, 2009 at 12:23 pm
@ John
I am writing a new blog article on adding calculations to pivot tables (it will probably be posted next week) but whilst I had OBIEE up and running I thought I’d take a look at your question.
Firstly, good spot – the example does falter if you use sub-totals. But there are two things we can do to get around that:
If we move customer.state to be the first items in the ROWS selector in the pivot definition we can then use the “SUM” on the customer.state column this will give the correct sub-total for all CA sales. The “Rows Grand Total” button still counts the invisible items.
The other way we could show the correct totals is drag customer.state to the columns area of the pivot table and place below the measures; this time we use the rows grand total button)
Pete
April 18th, 2009 at 11:42 am
Hi Mark,
I know that you have a lot of experience in doing reports in Discoverer and guess some in OBI EE.
In my company we mainly use Discoverer as a reporting tool but we’re about to replace it with OBI EE.
The best feature of Discoverer was pivoting and possibility to have a lot of page items on the top. So once the users get an resultset they’re able to slice it very quickly by changing values in those page items. If they aren’t interested in slicing by some page items they just put or choose //ALL//.
All those manipulations were very quick and the user would always get in page items only possible combinations from the resultset.
My questions is (:)) how to manage that same kind of reports in OBIEE???
I tried to make same pivot in OBIEE, but in Pages (=Page items) I had to have something selected from the resultset. There is no //ALL// option like in Discoverer which comes by default.
I tried to make calculated item for each page with name //ALL// and formula/sum(*) and it works fine if there are 2-3 items.
However for more everything gets slower and slower, rendering of resultset is impossible.
It’s frustrating ):
Any suggestions, ’cause we have hundreds of pivot reports with many pages (= page items).
Thnx in advance,
Zoran