Interesting post here from Allan Folting on the Excel blog discussing some workarounds for common problems that arise when using Excel 2007 as a client tool for AS2005:
It explains why you can’t select individual calculated members on non-measures dimensions, which in my opinion is the biggest barrier for the use of Excel 2007 as a front end because it effectively rules out the use of time utility dimensions. The workaround discussed is to use an older version of the pivot table but you lose as much as you gain from doing this; luckily a ‘future solution’ is promised. I guess this will come in a future service pack for Excel because apparently (changing the subject slightly) we’re not getting a SP3 since not enough people have asked for it (see here for more details). Pretty stupid, eh? Esepcially given the fact that SP2 was a bit of a disaster from an Analysis Services point of view with a whole load of bugs and performance regressions appearing. If you’d like to see a SP3 then vote here on Connect:
Last of all, talking of Excel 2007 BI, it seems like you can now put Excel directly on top of SAP Netweaver BI:
Good from the point of view of industry-wide support for MDX, I suppose, but on the other hand it does eat away at one of the unique selling points of the MS BI stack.
I think that the limitations in Excel 2007 is more basic than that. You cannot put a basic fact measure, or a calculated measures, on rows or columns in Excel 2007. You must drop all measures in the value pane in the Pivot tables. If you cannot do this how should you be able to support calculated members? In this way they have avoided some problems and good features that are present with tools like ProClarity. Excel 2007 is more of an advanced report tool for MS cubes than a flexible SSAS2005 client.
If I am wrong I would appreciate it!
After you\’ve dropped your measures into the Value pane, a Values \’dimension\’ appears which you can move between Rows and Columns; you can\’t drag it onto the Filter axis, stupidly, but if you only select one measure then you get the same effect. It\’s not ideal and I agree it would just be easier to have the Measures dimension treated like every other dimension, but it\’s not impossible to do what you want.
I cannot see a value dimension in my Excel 2007 but I will give it another try. After you drop a measure in the value pane you will see that in the dimension tool?
Anyway, thanks for the answere.