Order and limit results in MDX

Question

I am trying to get the first top 5 Products by Sales for every "City", ordered descending by "Sales" but my code does not work properly. The query finds the top 5 products but not the top 5 for every product, and the ORDER DESC doesn't work either. I use Mondrian. Any ideas?

WITH
    SET [PRODUCTS] AS 
        Head(
            ORDER(Filter([Product].[Product].Members, not isEmpty([Measures].[Sales])),[Measures].[Sales], DESC)
        ,5
        )
SELECT
    NON EMPTY {[Measures].[Sales]} ON COLUMNS,
    NonEmptyCrossJoin([Markets].[City].Members, [PRODUCTS]) ON ROWS
FROM
    [SteelWheelsSales]

whytheq whytheq · Accepted Answer · 2016-03-07T16:10:58

Here is an example similar to your situation against AdvWrks:

WITH 
  SET [AllCountries] AS [Country].[Country].MEMBERS 
  SET [AllStates]    AS [State-Province].[State-Province].MEMBERS 
  SET [Top2States] AS 
    Generate
    (
      [AllCountries]
     ,TopCount
      (
        (EXISTING 
          [AllStates])
       ,3
       ,[Measures].[Internet Order Count]
      )
    ) 
  MEMBER [State-Province].[All].[RestOfCountry] AS 
    Aggregate({(EXISTING {[AllStates]} - [Top2States])}) 
SELECT 
  {[Measures].[Internet Order Count]} ON COLUMNS
 ,{
      [AllCountries]
    * 
      {
        [Top2States]
       ,[State-Province].[All].[RestOfCountry]
       ,[State-Province].[All]
      }
  } ON ROWS
FROM [Adventure Works];

It gives these results:

So if I attempt to adapt the above and simplify it, as you do not require a RestOf.. category I get something like this:

WITH 
  SET [AllCities]    AS [Markets].[City].MEMBERS
  //SET [AllPRODUCTS]  AS NONEMPTY([Product].[Product].MEMBERS, [Measures].[Sales])
  //alternative if NonEmpty not implemented in Mondrian..
  SET [AllPRODUCTS]  AS 
     FILTER(
         [Product].[Product].MEMBERS
        ,not isEmpty([Measures].[Sales])
     )
  SET [Top5PRODUCTS] AS 
    Generate
    (
      [AllCities]
     ,TopCount
      (
        (EXISTING 
          [AllPRODUCTS])
       ,5
       ,[Measures].[Sales]
      )
    ) 
SELECT 
  NON EMPTY 
    {[Measures].[Sales]} ON COLUMNS
 ,

    [AllCities] 
   *[Top5PRODUCTS]
   ON ROWS
FROM [SteelWheelsSales];

Order and limit results in MDX

1 Answers