MDX query to order (and topfilter) results after/with a crossjoin

0
votes

I would like to order a set of results in an MDX query which also includes a crossjoin.

I have the following measures and dimensions:

  • [Measures].[Starts]
  • [Framework].[Framework Name]
  • [Framework].[Pathway Name]

I would like to create a list of the (corresponding) Framework and Pathway names that correspond to the top 25 numbers of [Measures].[Starts].

I have managed to output a FULL list of results using:

select [Measures].[Starts] on COLUMNS,
NON EMPTY CrossJoin(
Hierarchize({DrilldownLevel({[Framework].[Pathway Name].Children})}), 
Hierarchize({DrilldownLevel({[Framework].[Framework Name].Children})})
) on ROWS
from [DataCube]

to create the following example output: example table required

However, I need it to be sorted by the starts in descending order (and preferably only keep the top 25 results). I have tried almost everything and have failed. A google search didn't find any results.

2
ssasmdxcross-join

2 Answers

0
votes

Did you stumble across the TopCount function?

select [Measures].[Starts] on COLUMNS,
NON EMPTY 
    TopCount
        (
        CrossJoin
            (
            Hierarchize({DrilldownLevel({[Framework].[Pathway Name].Children})}), 
            Hierarchize({DrilldownLevel({[Framework].[Framework Name].Children})})
            ), 
        25, 
        [Measures].[Starts]
        ) on ROWS
from [DataCube]

Here's the msdn link.

H2H

0
votes

For efficiency it is better to order the set before using the TopCount function:

WITH 
SET [SetOrdered] AS
  ORDER(
     {DrilldownLevel([Framework].[Pathway Name].Children)} 
    *{DrilldownLevel([Framework].[Framework Name].Children)}
    ,[Measures].[Starts]
    ,BDESC
  )
SET [Set25] AS
  TOPCOUNT(
     [SetOrdered]
    ,25
  )
SELECT 
  [Measures].[Starts] on 0,
NON EMPTY 
  [Set25] on 1
FROM [DataCube];