Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX and help merging 2 statements


MDX and help merging 2 statements

Author
Message
Terry300577
Terry300577
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1839 Visits: 508
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]
) ) ) )


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search