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

Group By MDX for YTD Expand / Collapse
Author
Message
Posted Tuesday, October 15, 2013 3:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:48 AM
Points: 49, Visits: 252
HI,

I'm New to MDX querying, so looking for some help here :

This is the query that got generated out of profiler :


SELECT CROSSJOIN( { [Fiscal_Year].[2014] },
{ [Period].[Jun], [Period].[Jul] },
{ ADDCALCULATEDMEMBERS([Function].[All Function].Children) })
ON 0 ,
NON EMPTY CROSSJOIN( { [Fund].[10]}, { [Account_Code].[ABCD] }) ON 1
FROM [TFA_Budget2]
WHERE ( [Measures].[Value])


2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014
Jun Jun Jun Jun Jun Jun Jul Jul Jul Jul Jul Jul
A B C D E F A B C D E F
10 ABCD 10 20 30 40 50 60 10 20 30 40 50 60



Result Set

2014 2014 2014 2014 2014 2014
Total Total Total Total Total Total
A B C D E F
10 ABCD 20 40 60 80 100 120







So we total sum(A) for Jun and JUly and put it in Total.

Thanks in advance.




Post #1504999
Posted Wednesday, October 16, 2013 3:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 1,001, Visits: 2,577
Hi,

Try something like this

With Member [Period].[Total] as ( [Period].[Jun] + [Period].[Jul])
SELECT CROSSJOIN( { [Fiscal_Year].[2014] },
{ [Period].[Total] },
{ ADDCALCULATEDMEMBERS([Function].[All Function].Children) })
ON 0 ,
NON EMPTY CROSSJOIN( { [Fund].[10]}, { [Account_Code].[ABCD] }) ON 1
FROM [TFA_Budget2]
WHERE ( [Measures].[Value])

Post #1505120
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse