Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Accumulated values in reports

Posted by thomasivarssonmalmo on July 22, 2007

When you corporate reports in MDX you have to accumulate values for different time periods like month-to-date, year-to-date and parallelperiod year-to-date and month-to-date. This is a frequent report requirement but not a very advanced problem in MDX.  
 
The only advanced thing with it is that I use calculated members and not calculated measures. It means that they are placed under the date.calendar user hierarchy and not not with measures as a parent.
 
I have not seen many examples on the web so i thought it can be useful for you, not that advanced MDX-Geeks, to see an example. You can copy and paste this MDX into management studio or ProClarity professional 6.3 and run in on the Adventure Works cube:
 
WITH MEMBER [Date].[Calendar].SalesParallelPeriodYTD
AS Sum(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].[Date].&[1104])),[Measures].[Internet Sales Amount])
 
MEMBER [Date].[Calendar].SalesParallelPeriod
AS Sum(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].[Date].&[1104]),[Measures].[Internet Sales Amount])
 
MEMBER [Date].[Calendar].SalesThisYearYTD
AS Sum(YTD([Date].[Calendar].[Date].&[1104]),[Measures].[Internet Sales Amount])
 
MEMBER [Date].[Calendar].SalesThisYearMTD
AS Sum(MTD([Date].[Calendar].[Date].&[1104]),[Measures].[Internet Sales Amount])
 
MEMBER [Date].[Calendar].SalesParallelPeriodMTD
AS Sum(MTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].[Date].&[1104])),[Measures].[Internet Sales Amount])
 
Select {[Date].[Calendar].SalesParallelPeriod,[Date].[Calendar].SalesParallelPeriodYTD,[Date].[Calendar].SalesThisYearYTD,
[Date].[Calendar].SalesThisYearMTD,[Date].[Calendar].SalesParallelPeriodMTD} On Columns,
{[Measures].[Internet Sales Amount]} On Rows
From [Adventure Works];
 
This MDX is not the answere to all scenarious but can work as a start. I have hard coded july 8:th 2004([Date].&[1104]) as the date member in these calculated members. It is possible to create these calculated members in the calculations tab in the cube editor in BIDS but then you will have to change the syntax to Create Member CurrentCube.[Date].[Calendar] .
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: