CLASSIC: named set in MDX top 2 by dimension

Question

SSAS 2012 Multidimensional DW has

Measures: Line total,
Dimension with hierarchy: [Division] - [Sales Person].

I am trying to make set of top 2 salespersons by divisions. This code sample gives me the correct result in SSMS:

    with set [f] as 
     Generate(
       { [Sales Person].[Hierarchy].[All] . children }, 
            [Sales Person].[Division].CurrentMember *
                TopCount(
                EXISTING  [Sales Person].[Sales Person Id].children 
                        ,2 
                ,[Measures].[Line Total] 
                )
            ) 
            SELECT [Measures].[Line Total]  ON COLUMNS,
              [f]
   ON ROWS  from     [Adventure Works]

it's working fine. It will give me top 2 by division. However, if i want to make named set in cube based on previous MDX thus:

 create dynamic set CURRENTCUBE.[f] as Generate(
       { [Sales Person].[Hierarchy].[All] . children }, 
            [Sales Person].[Division].CurrentMember *
                TopCount(
                EXISTING  [Sales Person].[Sales Person Id].children 
                        ,2 
                ,[Measures].[Line Total] 
                )
            )

it will give me no error but this named set will not show in the browser. Validation of the MDX code in the calculations tab passes ok.

Meff Meff · Accepted Answer · 2013-04-17T15:45:59

You say it won't show in the browser, have you allocated it a display folder? If not, it may not be where you expect to see it.

Can you use it in an MDX query OK?

CLASSIC: named set in MDX top 2 by dimension

1 Answers