One of out early adopter customers noticed this different behavior between Office Web Components (OWC) and Excel, when connected to the same Analysis Services cube, so thought I would share to hopefully save others some head scratching and offer a (lame) workaround. As OWC is no longer used in Project Server 2010 we expect many users will now have Excel as a main part of their reporting story so worth understanding what is going on here.
The issue comes from the fact that OWC and Excel pull the information from Analysis Services in a different way, which means that OWC builds up relationships that Excel can only infer. As an example I will use Projects and Resources, and my cube is actually created from Project Server 2007, so that I can show the behavior in OWC and also Excel 2010 – but the same issue would exist if I used a 2010 cube as my target.. The MSP_PORTFOLIO_ANALYZER cube does not have a direct relationship between projects and resources, but obviously is does have resource data relating to work that builds up within projects. In my simple example I will just show projects, resource and actual work.
So in OWC we see something like this:
and I can expand the ‘+’ signs to show the resources:
The same thing in Excel looks like this, and I have cut it short – but you will get the idea. I don’t have the ‘+’ sign so cannot expand and contract (apart from unselecting and reselecting the Resource List dimension). It does have the structure, but not the expand/collapse.
*** Update 2/9/2011 *** I should point out that the following workaround isn't meant to be a serious option as the whole idea of using PivotTables is to have the dynamic slice and dice capability - the following steps would need to be repeated every time you wanted to make a slight "what-if" change. No sane person would want to do this - just wanted to clarify I was not (quite) insane when I suggested it...
A work around is to build a PivotTable based on the PivotTable, but first you need to change you original PivotTable above by changing a couple of settings:
First right-click and select PivotTable Options and the display column.
Select the Classic PivotTable layout and click, then the Totals and Filters Tab and uncheck both Show grand totals boxes - then OK.
Then right-click a project name, and select Field Settings and the Layout and Print tab and select the Repeat item labels (new in 2010!), On the Subtotals & Filters Tab select None - then OK.
Your PivotTable should now look something like this.
If we now insert a new PivotTable, and use this as our source table (from the second line) we can get to this:
Which is pretty close to our OWC starting point. I guess you could do some automation, but honestly this does take away from the flexibility of the PivotTable if you start adding extra dimensions to the original one – and if you had a time dimension across the top that would again add some challenges.
I’d be interested to hear if I am missing something and if there is a better solution – and we are looking to see if we could perhaps do something in the cube to start with so that Excel gives us what we need a little easier. One interesting observation is that SQL Management Studio also gives the expand/collapse option when browsing data in the cube.