Creating Pivot Table-Specific Filters in Oracle BI Answers

Mark Rittman

Mar 6, 2009 • 3 min read

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.

Filterview1

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:

Filterview2

Then I created a pivot table, that listed out products and state on the rows, and quantity sold on the columns.

Filterview3

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:

Filterview4

Then I create a new calculated item based on the 'CA' column value, and call it CA, like this:

Filterview5

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.

Filterview6

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.

Filterview7

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.

Filterview8

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.

Sign up for more like this.