Is there anyone that has experience working with user client side calculated
members in Excel 2007 Pivot Tables?

What we would like to implement is an Excel add-In that lets the end user
create client side calculated members, calculated measures and sets in Excel
2007 Pivot tables based on SSAS 2005 cubes. All this in a user-friendly way.
The base code for doing this using the CalculatedMembers.Add function is
fairly well documented, for example here
http://blogs.msdn.com/excel/archive/...votTables.aspx,
there is also a MS Add-in that allows the user to write MDX code to add
calculated members here http://www.codeplex.com/OlapPivotTableExtend

As our user base is not used to writing MDX code we have build a drag and
drop interface, that allows the user to build the calculated members without
having to know the underlying MDX. All this works beautifully, the problem is
only that the object library that exposes the CaclulatedMembers.Add method is
very tricky to use. It will only accept some of the MDX code supplied even if
the MDX itself is correct. There are also cases when the Add function works,
but the calculated member cannot be found anywhere in the pivot table. (and
yes, the Show Calculated members property is set to True). Also, it seems
impossible to create a Set even if following the MS described limitations
(Excel 2007 only support named sets consisting of members from a single
hierarchy).

The Add method only returns the generic 1004 error code so it is of no use
in helping us find the reason the method fails.

€¢ Is there anyone that has any experience with the CaclulatedMembers.Add
function and could give us some hints on what the limitations are?

€¢ Is there a way to find the under-lying error code other than the generic
1004.