f 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
Posts
