Prev: Calculate %
Next: Excel stops responding; then starts again
From: bquackenbush on 28 May 2010 09:13
I am trying to build relative references to an SSAS OLAP cube using the
GETPIVOTDATA function (excel 2007). There is a field in the pivot table named
"Item". I have grouped this field and named it "Item1". I renamed the values
in the grouped field so they are more descriptive than "Group1", etc. For
example, I renamed "Group6" to "NBD".

When I create the initial formula by typing "=", arrowing onto a field in
the cube and pressing enter, I get the expected result and a formula such as
this:

=GETPIVOTDATA("[Measures].[Quantity]",$A$9,"[Item].[Item]","[Item].[Item].[Item1].[GROUPMEMBER.[ProductXl_Grp_6]].[Item]].[Item]].[All]]]")

This formula references Group6, aka "NBD". Excel seems to default the
GETPIVOTDATA reference to some system name for the value in the grouped field
("ProductXL_Grp_6") instead of my renamed value. Is there a way for the
formula to reference my renamed value instead of the system value for the
grouped field? This would make it easier to build relative references and
scale the formula to the entire worksheet.

Thanks
 | 
Pages: 1
Prev: Calculate %
Next: Excel stops responding; then starts again