Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX and help merging 2 statements Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 8:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:01 AM
Points: 1,726, Visits: 486
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]
) ) ) )

Post #1485827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse