MDX help required output

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  November 12, 2014 at 7:12 am

  #147179

  Hi,

  May anybody help me with this output scenario using mdx. I am just close to it but could not find complete solution.

  Precisely what I want is Years on rows and Months on columns from Date dimension but as well I want two calculated members for each year in side by side of months. I have achieved first part. YTD is total sum for whole respective year and ITD is Running total of YTD for each year.

  ex.

  JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberYTDRORITDROR

  CY 2005$37,871.05 $40,495.34 $37,915.44 $41,066.36 $43,519.47 $60,442.23 $261,309.90 $261,309.90

  CY 2006$47,739.73 $44,065.34 $51,530.82 $53,095.38 $53,884.50 $54,141.09 $40,029.21 $43,680.12 $28,037.36 $33,231.22 $26,807.61 $46,185.12 $522,427.50 $783,737.40

  CY 2007$35,109.22 $39,127.23 $38,845.99 $40,511.95 $45,021.81 $44,383.94 $70,933.51 $67,793.08 $80,820.65 $86,435.97 $95,758.49 $138,543.02 $783,284.85 $1,567,022.25

  CY 2008$107,219.60 $116,998.39 $118,472.41 $128,700.04 $150,265.40 $155,948.89 $4,067.25 $781,671.98 $2,348,694.23

  CY 2009$2,348,694.23

  Thanks

  Ashok

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  November 12, 2014 at 7:13 am

  #1757977

  You will just love it...

  WITH

  MEMBER [Date].[Month of Year].YTDROR AS

  Aggregate

  (

  Except

  (

  [Date].[Month of Year].MEMBERS

  ,[Date].[Month of Year].[All Periods]

  )

  )

  MEMBER [Date].[Month of Year].ITDROR AS

  IIF

  (

  [Date].[Calendar].CurrentMember

  IS

  [Date].[Calendar].[Calendar Year].MEMBERS.Item(0)

  ,[Date].[Month of Year].YTDROR

  ,

  [Date].[Calendar].CurrentMember.PrevMember

  +

  [Date].[Month of Year].YTDROR

  )

  SET [Months] AS

  Except

  (

  [Date].[Month of Year].MEMBERS

  ,[Date].[Month of Year].[All Periods]

  )

  SELECT

  {

  [Months]

  ,[Date].[Month of Year].YTDROR

  ,[Date].[Month of Year].ITDROR

  } ON COLUMNS

  ,[Date].[Calendar].[Calendar Year].MEMBERS ON ROWS

  FROM [Adventure Works]

  WHERE

  [Measures].[Internet Tax Amount];

  Thanks

  Ashok

• PB_BI

  SSCoach

  Points: 17463

  More actions

  November 13, 2014 at 6:28 am

  #1758204

  Firstly:

  http://formatmdx.azurewebsites.net/

  🙂

  Secondly, can you explain what you are trying to achieve with this query in plain terms?

  ---

  I'm on LinkedIn

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  November 13, 2014 at 7:16 am

  #1758216

  I have edited my post. Pls check. I love MDX Studio for formatting. 🙂

• PB_BI

  SSCoach

  Points: 17463

  More actions

  November 13, 2014 at 7:24 am

  #1758219

  You don't need those Unions:

  {[Months]

  ,[Date].[Month of Year].YTDROR

  ,[Date].[Month of Year].ITDROR

  } ON COLUMNS

  You didn't explain what you were trying to do?

  ---

  I'm on LinkedIn

• Ashok Teotia

  SSCrazy

  Points: 2407

  More actions

  November 13, 2014 at 7:29 am

  #1758222

  Thanks for suggestion! I have removed Union.