Link to home
Create AccountLog in
Avatar of Comitto
Comitto

asked on

Ranking Report using Reporting Services/Analysis Services

I am new to MDX and Reporting Service and I am trying to create a Sales report presenting 2 different rankings. One is the ranking on YTD based on "Amount", and in the same report with same sorting as on YTD it sould calculate ranking for the month.

I should look similar to the attachment I uploaded.

Can anyone help me with the MDX to create such a query? User generated image
CUSTNAME, COUNTRY and EMPLOYEE is member properties of CUSTID
Amount is a standard measure in cube
% should be calculated as a the members percentage of the total amount of all members within either YTD or Month
Rank is calculated on Largest Amount in period should be 1, second largest in period should 2, etc…
Avatar of Emes
Emes
Flag of United States of America image

here is a sample of mdx order

-- Use ORDER  (will sort within customer)
 SELECT   [Measures].[Internet Sales Amount]
  ON COLUMNS,
  NON EMPTY
  ORDER(
     ( [Customer].[State-Province].CHILDREN,
      [Product].[Subcategory].CHILDREN ),
                  [Internet Sales Amount],DESC)
                        ON ROWS
FROM [Adventure Works]
Avatar of Comitto
Comitto

ASKER

I been messing a bit around and below still need some adjustment before im there. Any suggestions?

WITH SET [OrderedSet] AS
    ORDER([Customer].[Default].[Level 05].members, [Measures].[Value],
        bDESC)
        
        SET [OrderedSet2] AS
    ORDER(([Customer].[Default].[Level 05].members) ,
         Sum(PeriodsToDate([Time].[Default].[Year],[Time].[Default].currentmember ), [Measures].[Value]),
        bDESC)
 
    MEMBER [Measures].[Rank] AS
     RANK(([Customer].[Default].currentmember),
              [OrderedSet])
      MEMBER [Measures].[Rank2] AS
     RANK(([Customer].[Default].currentmember),
              [OrderedSet2])
      MEMBER [Measures].[ValueYTD] AS
     Sum(PeriodsToDate([Time].[Default].[Year],[Time].[Default].currentmember ),
          [Measures].[Value])    

SELECT
   {[Measures].[Value], [Measures].[Rank], [Measures].[ValueYTD], [Measures].[Rank2]} ON 0,
   {[OrderedSet]} ON 1
 
FROM
[Sales]
ASKER CERTIFIED SOLUTION
Avatar of Comitto
Comitto

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mike McCracken
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.