MDX SSAS Rank rownumber function

Question

I have an existing SSRS report out of T-SQL query and I am trying to recreate it using MDX queries on SSAS cube. I am stuck with rewriting Row num and rank logic to MDX.

It is written as:

SELECT ceil((ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY YEARMONTH))/12)

Rank1 in the SQL. Can someone tell me if this can be done using MDX? In the cube, PRODUCT and YEARMONTH are coming from separate dimensions.

Thank you for your help!

Thank you. I am able to get the rank function for each record but I need the same number to be displayed for the first 12 YEARMONTHs within a product. Can you suggest me how to do that please? — lakshru, Apr 18 '17 at 16:46

score 0 · Answer 1 · answered Apr 17 '17 at 21:24

0

There is the Rank() function. For example:

with
Dynamic Set OrderedSet as
Order(
    NonEmptyCrossJoin(
       [Date].[Year].[Year].Members,
       [Product].[Product].[Product].Members,
       [Measures].[Invoice Count],
       2
    ),
    [Measures].[Invoice Count],
    BDESC
)

Member [Measures].[Rank] as
Rank(
        ([Client].[Client].Currentmember,[Date].[Year].CurrentMember),
        OrderedSet
)

select {[Measures].[Invoice Count],[Measures].[Rank]} on 0,
non empty OrderedSet on 1
from [BI Fake]
where ([Date].[Day].&[20160120])

You can read in details about it from my blog post.

answered Apr 17 '17 at 21:24

Danylo Korostil

1,464
2
10
19

Thank you. I am able to get the rank function for each record but I need the same number to be displayed for the first 12 YEARMONTHs within a product. Can you suggest me how to do that please? – lakshru Apr 18 '17 at 16:46
Could you please post a wished output? – Danylo Korostil Apr 19 '17 at 07:05

score 0 · Answer 2 · answered Apr 19 '17 at 07:23

You can use Generate to repeat ranks like this:

WITH 
  SET [SalesRank] AS 
    Generate
    (
      [Customer].[Customer Geography].[State-Province]
     ,Order
      (
        NonEmpty
        (
          (
            [Customer].[Customer Geography].CurrentMember
           ,[Product].[Product Categories].[Subcategory]
          )
         ,{[Measures].[Internet Sales Amount]}
        )
       ,[Measures].[Internet Sales Amount]
       ,BDESC
      )
    ) 
  MEMBER [Measures].[CategoryRank] AS 
    Rank
    (
      (
        [Customer].[Customer Geography].CurrentMember
       ,[Product].[Product Categories].CurrentMember
      )
     ,Exists
      (
        [SalesRank]
       ,[Product].[Product Categories].CurrentMember
      )
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[CategoryRank]
  } ON 0
 ,[SalesRank] ON 1
FROM [Adventure Works];

It results in this:

MDX SSAS Rank rownumber function

2 Answers2