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.
