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

Simple MDX query Expand / Collapse
Author
Message
Posted Friday, February 1, 2013 7:48 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
Hi,

I'm trying to something that I think should be straight forward in MDX but i'm struggling. I'm new to MDX so it seems trivial but may not be....

Consider the following query:

SELECT
[Measures].[Thing Count] ON 0,
YTD([Effective Date].[Year-Week].[Week].[2013 Week 6]) ON 1
FROM
[Cube]

This gives me a result set that shows as per the attached. All i really want though is the SUM of the rows. How do i do that? It's driving me nuts because i'm sure it's a piece of cake.

Thanks,


Simon




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne


  Post Attachments 
Query.png (7 views, 3.13 KB)
Post #1414686
Posted Sunday, February 3, 2013 3:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Try something along the lines of

With Member [Measures].[Thing Count YTD] As

Sum(YTD([Effective Date].[Year-Week].CurrentMember)
, [Measures].[Thing Count] )

SELECT
[Measures].[Thing Count YTD] ON 0,
[Effective Date].[Year-Week].[Week].[2013 Week 6] ON 1
FROM
[Cube]

Mack
Post #1415061
Posted Monday, February 4, 2013 1:55 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:53 AM
Points: 702, Visits: 2,178
That's great thanks!



MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1415173
Posted Thursday, April 18, 2013 10:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:01 PM
Points: 356, Visits: 871

Try this also...
With Member [Measures].[Thing Count YTD] As

AGGREGATE(YTD([Effective Date].[Year-Week].CurrentMember)
, [Measures].[Thing Count] )

SELECT
[Measures].[Thing Count YTD] ON 0,
[Effective Date].[Year-Week].[Week].[2013 Week 6] ON 1
FROM
[Cube]

This keeps whatever it is you are counting from being double counted.
Post #1443998
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse