Add Calculated Members in Pivot Table from SSAS OLAP Cube

[potter_ricky]

Hi,

I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.

I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.

Is this possible or should I just look to calculate this consolidation by formulas in the model.

Thanks in advance,

Ricky

Excel 2007
Win7 Pro

 

[potter_ricky]

I should add that the Cube is being returned into Excel in a pivot table, and its in the pivot table that I'd like to add the calculated member.