Calculated members display in the pivot table fine when I use a cube from MS
olap services provider, for example FoodMart Sales. Here is mdx that the
pivot table uses:

"SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({ [Store].[All Stores]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Sales] WHERE
([Measures].[Profit])"

The AddCalculatedMembers method is responsible for ensuring the calculated
members are displayed in the pivot table. However, when I open a cube in a
pivot table using a 3rd party provider, the mdx that the pivot table
generates is:

"SELECT NON EMPTY HIERARCHIZE({DrillDownLevel({[PRODUCT].[All PRODUCT]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [SALES] WHERE
([Measures].[QTY])"

The only difference is that the AddCalculatedMembers method is not in the
mdx, and therefore, the calculated members do not appear in the pivot table.
I manually added the AddCalculatedMembers method to the mdx and executed it
outside of Excel and it indeed returned the calculated members. Why is the
AddCalculatedMembers method added to the pivot table mdx for the MS cube,
but not for the 3rd party cube?

I'm using Excel XP (2002). I read a lot of posts where people were having
the exact same symptoms (calculated members appear in the filter drop-down,
but not in the pivot table) in Excel 2000 and that it was supposed fixed in
XP and later -- I suppose the AddCalculatedMembers method was the solution.
However, it only appears fixed for MS cubes.

I checked Excel 2007 to see if things were any better. There is a new pivot
table option called "Show caluclated members from OLAP server" that adds or
removes the AddCalculatedMembers method from the mdx. However, this option
is disabled for the pivot table connecting to the 3rd party cube! So, it
appears there is still no way to get calculated members from the 3rd party
provider, only from the MS provider. Is there a good reason to not make
this option available all the time!?

Thanks,
Casey

Had to contact MS tech support to resolve:
RESOLUTION:

1. Excel queries the provider for DBPROPSET_MDX_EXTENSIONS through the IDB
Properties Interface to see whether they are supported.

2. If it gets a positive answer, it checks value of
DBPROP_MSMD_MDX_CALCMEMB_EXTENSIONS.

3. If either of these returns a false, we will not make AddCalculatedMembers
available.

The AddCalculatedMembers method is not used by default because it is not in
the OLEDB spec. Fortunately, Excel queries the provider to see if it
supports the method. If the provider surfaces the two properties above, the
AddCalculatedMembers method will be used in the pivot table generated mdx,
and thus, calculated members will appear in the pivot table.

Fyi... the ONLY thing I found online regarding these properties was the
header file they are defined in, msmd.h.

Casey

"Casey" <casey.smith (AT) sas (DOT) com> wrote