CODEWITHSUNDEEP
ssasmdx
Terry White
Terry White

Reputation: 21

MDX and combine two similar

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]
) ) ) )

Upvotes: 1

Views: 104

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Just combine both WITH clauses (without a comma or anything in between!), and list both members in the columns axis:

WITH MEMBER MEMBER [Member1] AS AGGREGATE( ({[SRD Date].[Base Report Date].& [1]},{[SRD Date].[Current Year].& [Yes]}),  [Measures].[Amount])
            MEMBER [Member2] AS AGGREGATE( ( {[SRD Date].[Current Year].& [Yes]}),  [Measures].[Amount Of Movement])
SELECT NON EMPTY {  [Member1], [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]
) ) ) )

I hope I did not miss any important other difference between both queries.

Upvotes: 1

Related Questions