Creating a pivot table view with mixed granularity

I’m finally posting a solution to the problem I’ve identified a while ago related to a specific Pivot Table View. I think the final outcome should look something like this image:

To get this result – I’ve used the “New calculated column” menu on the pivot table (circled in red):

OBIEE pivot table menu

Then, what you really need to have in the Columns is the most granular level column (sounds redundant – in this case it’s a Date column). I used Excel to quickly generate formulas, such as this for Q1. It’s really easy to generate these formulas below. I hope it’s pretty self-explaining – you add things up in a granular level – this way you can combine things – that you wouldn’t be able to combine otherwise:

The same logic works for Q2,3,4. For individual dates, it would be the same idea:

And for months, it’s the same logic:

To sum up – by using the “calculated item” feature of pivot table view – you can create reports with mixed granularity on them. The pros are that you’ll be able to support legacy views (in some instances it’s absolutely critical to mimic existing view). The cons are that it involves some manual entry / data manipulation and that it’s not very flexible (I couldn’t find out how to use variables in this example).

Last thing – it seems as first column gets added on the left and the consequent ones to the right – keep this in mind while designing this view – since I’ve noticed you can’t really manipulate with the locations of the calculated columns.

6 thoughts on “Creating a pivot table view with mixed granularity”

just one add-on: on order to make that calculation more dynamic you can always use the “$” notation to reference the nth value in the pivot.

I.e. “$1 + $2 + $3 + $4 + … + $n”

If you know the number of dimension members your query will fetch (more or less precisely) you won’t face any big issues. If you write “$1 + $2 + $3″ and your query only fetches 2 members then the calc will wotk nevertheless.

Thanks for the tip Christian,
Can we put navigation on calculated items? For eg. I have grouped 10 rows of my output using calculated items and named it “Group 1″, now I require a drill on “group 1″ which takes me to some other report (basically showing the individual names which form Group 1), is this possible? Can any one help me out with this please?