MDX: Sum values with different dimensions (SQL Server Analysis Services 2008R2)

Question

Hello,
I would like to sum year-to-date data plus budget data from now on until the end of the year. My problem looks like this:

With --calculating the sums over time
Member [Time].[AllTime].[ytd] As aggregate([Time].[AllTime].[Month].[Jul 21]:[Time].[AllTime].[Month].[Jan 22]), VISIBLE=0
Member [Time].[AllTime].[rest] As aggregate([Time].[AllTime].[Month].[Feb 22]:[Time].[AllTime].[Month].[Jun 22]), VISIBLE=0

Select
( {[Info].[InfoID].&[act],[Info].[InfoID].&[bud]}
* {[Time].[AllTime].[ytd],[Time].[AllTime].[rest]} )
On Columns,
{[Measures].[X]} On Rows
From [Sales]

This is the result:

act act bud bud
ytd rest    ytd rest

X 2 4 8 16

How can I get 2+16=18 in columns? How can I calculate (actytd+budrest)=forecast?

(My problem behind is that I have to feed the columns of my frontend with mdx statements for ytd and rest separately from Excel ranges. But this is the next problem when I know what the complete mdx statement should look like.)

Thank you
arno

Answer 1

What you want is an arbitrary-shaped set, and it by definition cannot be expressed as a crossjoin. So instead of

{ [Info].[InfoID].&[act],[Info].[InfoID].&[bud] } 
* { [Time].[AllTime].[ytd],[Time].[AllTime].[rest] } 

you should use

{ ( [Info].[InfoID].&[act], [Time].[AllTime].[ytd] ) 
, ( [Info].[InfoID].&[bud], [Time].[AllTime].[rest] ) } 

Answer 2

Hi @arno sebastian ,

Please check below code:

With --calculating the sums over time  
Member [Time].[AllTime].[ytd] As aggregate([Time].[AllTime].[Month].[Jul 21]:[Time].[AllTime].[Month].[Jan 22])  
Member [Time].[AllTime].[rest] As aggregate([Time].[AllTime].[Month].[Feb 22]:[Time].[AllTime].[Month].[Jun 22])  
Member [Info].[InfoID].&[Forcast] AS aggregate({([Info].[InfoID].&[act],[Time].[AllTime].[ytd]),  
([Info].[InfoID].&[bud],[Time].[AllTime].[rest])})  
Member [Info].[InfoID].&[Actual] AS aggregate({([Info].[InfoID].&[act],[Time].[AllTime].[rest]),  
([Info].[InfoID].&[bud],[Time].[AllTime].[ytd])})  
Select  
{[Info].[InfoID].&[Forcast],[Info].[InfoID].&[Actual] }  
On Columns,  
{[Measures].[X]} On Rows  
From [Sales]  

--

With Member  
[Date].[Calendar].[ytd] AS  
aggregate([Date].[Calendar].[Month].&[2012]&[1]:[Date].[Calendar].[Month].&[2012]&[6])  
Member  
[Date].[Calendar].[rest] AS  
aggregate([Date].[Calendar].[Month].&[2012]&[7]:[Date].[Calendar].[Month].&[2012]&[12])  
 
Member  
[Product].[Category].[Forcast] AS  
aggregate( {([Product].[Category].&[1],[Date].[Calendar].[ytd])  
,([Product].[Category].&[4] ,[Date].[Calendar].[rest])} )  
Member  
[Product].[Category].[Actual] AS  
aggregate( {([Product].[Category].&[1],[Date].[Calendar].[rest])  
,([Product].[Category].&[4] ,[Date].[Calendar].[ytd])} )  
Select  
{[Product].[Category].[Forcast],[Product].[Category].[Actual]}  
On Columns,  
{[Measures].[Order Quantity]} On Rows  
FROM [Adventure Works NEW];  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you Zoe,

however, your MDX is mixing products and time. I changed the mdx to my cube of 2008R2 Adventure Works:

With Member
[Date].[Calendar].[ytd] AS
aggregate([Date].[Calendar].[Month].&[2007]&1:[Date].[Calendar].[Month].&[2007]&[6])
Member
[Date].[Calendar].[rest] AS
aggregate([Date].[Calendar].[Month].&[2007]&[7]:[Date].[Calendar].[Month].&[2007]&[12])

Member
[Product].[Category].[Forcast] AS
aggregate( {([Product].[Category].&1,[Date].[Calendar].[ytd])
,([Product].[Category].&[4] ,[Date].[Calendar].[rest])} )
Member
[Product].[Category].[Actual] AS
aggregate( {([Product].[Category].&1,[Date].[Calendar].[rest])
,([Product].[Category].&[4] ,[Date].[Calendar].[ytd])} )

--Totals
Select
{[Product].[Category].[Forcast],[Product].[Category].[Actual]}
On Columns,
{[Measures].[Order Quantity]} On Rows
FROM [Adventure Works];

--Details
Select
{[Date].[Calendar].[ytd], [Date].[Calendar].[rest]}*
{[Product].[Category].&1, [Product].[Category].&[4]}

On Columns,
{[Measures].[Order Quantity]} On Rows
FROM [Adventure Works]; --Version: Adventure Works DW 2008R2

Results Totals:

Results Details:

Pls. check the colours, it is adding bikesytd + accessoriesrest.