Creating named set

Question

I am trying to create a named set that would return sum of selected members for any measure. When I deploy I am getting error

function expects a tuple set expression for the argument. A string or numeric expression was used.

Please review my code and suggest solution. I have a dimension named Uline and this named set should sum selected 4 members of this dimension for any measure

    sum({[Uline].[CU - (46)],
         [Uline].[FRU - (45)], 
         [Uline].[GL - (38)],
         [Uline].[PU - (44)]},[Measures].CurrentMember)

whytheq whytheq · Accepted Answer · 2015-04-01T21:34:14

Better to use Aggregate rather than Sum if you wish it to be available for any measure.

Also no need to include the Measures hierarchy inside the function.

So try something simpler like this:

Aggregate(
  {
   [Uline].[CU - (46)],
   [Uline].[FRU - (45)], 
   [Uline].[GL - (38)],
   [Uline].[PU - (44)]
  }
)

Please note though that this will be a new member in an existing hierarchy, like this:

WITH MEMBER [Uline].[AggSet] AS
  Aggregate(
    {
     [Uline].[CU - (46)],
     [Uline].[FRU - (45)], 
     [Uline].[GL - (38)],
     [Uline].[PU - (44)]
    }
  ) 
SELECT
  [Measures].[SOMEMEASUREinCUBE] ON 0,
  [Uline].[AggSet] ON 1
FROM [CubeName];

I don't play around with the actual cube scripts myself but I'd guess the above could be implemented like this:

CREATE MEMBER CURRENTCUBE.Uline.AggSet  AS 
     'Aggregate(
        {
         [Uline].[CU - (46)],
         [Uline].[FRU - (45)], 
         [Uline].[GL - (38)],
         [Uline].[PU - (44)]
        }
      )'

If you'd rather create a custom set than a calculated member then try the following:

CREATE SET CURRENTCUBE.[AggSet]  AS 
        {
         [Uline].[CU - (46)],
         [Uline].[FRU - (45)], 
         [Uline].[GL - (38)],
         [Uline].[PU - (44)]
        }   
 ,
 Display_Folder = 'Sets';

The curly braces {} mean SET in mdx.

In the context of a script this looks like the following:

WITH SET [AggSet] AS
    {
     [Uline].[CU - (46)],
     [Uline].[FRU - (45)], 
     [Uline].[GL - (38)],
     [Uline].[PU - (44)]
    }
SELECT
  [Measures].[SOMEMEASUREinCUBE] ON 0,
  [AggSet] ON 1
FROM [CubeName];

Creating named set

1 Answers