Named Set in Excel Pivot Table Displays as Enumeration

Pardon me if this is really an Excel 2007 Pivot table question. I have a named set defined in the cube as:

CREATE SET CURRENTCUBE. [Team Building Overall]

AS {[Question].[Short Name].[Q-02],

[Question].[Short Name].[Q-03],

[Question].[Short Name].[Q-05],

[Question].[Short Name].[Q-06],

[Question].[Short Name].[Q-08],

[Question].[Short Name].[Q-13],

[Question].[Short Name].[Q-17],

[Question].[Short Name].[Q-18],

[Question].[Short Name].[Q-21],

[Question].[Short Name].[Q-30],

[Question].[Short Name].[Q-31]};

And can display measure values for this set as whole in Proclarity with:

SELECT { [Measures].[Average]} ON COLUMNS ,

{ [Corporate Hierarchy].[Hierarchy].DEFAULTMEMBER } ON ROWS

FROM [360 Training Survey]

WHERE ([Team Building Overall], [Test Month].[Test Month].&[200704] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

In Excel 2007, unfortunately I can not use this set as a filter as I can in Proclarity. Rather, the best I can accomplish is to display this as 11 different columns, one for each member of this named set.

Any suggestions ?

Anyone ?

I figure I can create a calculated measure in the cube named [Team Building Overall Average] which is defined as ([Team Building Overall],[measures].[Average]), but this seems a bit klutzy, especially given that I have eight sets and six measures, thus resulting in a total of 48 calculate measures I need to create.

|||

You could create a measure independant calculated member in the Question dimension as follows.

CREATE MEMBER CurrentCube.[Question].[Short Name].[Team Building Overall] AS AGGREGATE([Team Building Overall])

If the only reason that you have the sets is for filtering you could put the set definition inline in this memeber, otherwise you could leave them separate.