MDX and help merging 2 statements

• Terry300577

  Hall of Fame

  Points: 3391

  More actions

  August 19, 2013 at 8:06 am

  #151272

  Hi all,

  i am fairly new to MDX and SSAS. Recently i have been asked to produce a report which requires a Starting value for each monthly period and then the movement within each period ending up at the starting value for the next month.

  I can produce this in two separate MDX see below, but i am flummoxed on how to bring these results together in one dataset in SSRS without basically forming the data physically in a database and pulling trough into the warehouse. The queries are slightly different since they both use a different Measure and then one uses a couple more Dimensional filters.

  any help would be most appreciated and i hope someone here can help or offer some useful advice on other ways to possibly get to what i need to:

  WITH MEMBER [Member1] AS AGGREGATE( ({[SRD Date].[Base Report Date].& [1]},{[SRD Date].[Current Year].& [Yes]}), [Measures].[Amount])

  SELECT NON EMPTY { [member1] } ON COLUMNS

  , NON EMPTY {([SRD Date].[Year].[Year].ALLMEMBERS

  * [SRD Date].[Month Key].[Month Key].ALLMEMBERS

  * [SRD Date].[Month Of Year].[Month Of Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION

  , MEMBER_UNIQUE_NAME ON ROWS FROM (

  SELECT (

  - { [Resource].[Category].& [Support]

  , [Resource].[Category].& [Bolt-on] }

  ) ON COLUMNS

  FROM (

  SELECT ([Exclude Test Accounts]) ON COLUMNS

  FROM (

  SELECT ([OnlyUnitsAndItems]) ON COLUMNS

  FROM (

  SELECT ([ExcludeNonReportableMonths]) ON COLUMNS

  FROM [Cube1]

  ) ) ) )

  WITH MEMBER [Member2] AS AGGREGATE( ( {[SRD Date].[Current Year].& [Yes]}), [Measures].[Amount Of Movement])

  SELECT NON EMPTY { [Member2] } ON COLUMNS

  , NON EMPTY {([SRD Date].[Year].[Year].ALLMEMBERS

  * [SRD Date].[Month Key].[Month Key].ALLMEMBERS

  * [SRD Date].[Month Of Year].[Month Of Year].ALLMEMBERS

  * [SRD].[Movement Type].[Movement Type].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION

  , MEMBER_UNIQUE_NAME ON ROWS FROM (

  SELECT (

  - { [Resource].[Category].& [Support]

  , [Resource].[Category].& [Bolt-on] }

  ) ON COLUMNS

  FROM (

  SELECT ([Exclude Test Accounts]) ON COLUMNS

  FROM (

  SELECT ([OnlyUnitsAndItems]) ON COLUMNS

  FROM (

  SELECT ([ExcludeNonReportableMonths]) ON COLUMNS

  FROM [Cube1]

  ) ) ) )