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

Need Help on MDX Query.. Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 9:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 21, 2014 9:50 PM
Points: 386, Visits: 2,356
I have grouped by state and now i want the totals in the same dataset for country which is nothing but total of all states...Can you please help me with that..

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #1392240
Posted Thursday, December 6, 2012 10:09 AM
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
Not sure I really follow you issue.

Do you have MDX you can share - it might make your issue easier to visualise?

Mack
Post #1393633
Posted Thursday, December 6, 2012 2:45 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 21, 2014 9:50 PM
Points: 386, Visits: 2,356


WITH

MEMBER [Measures].[Month_to_Show] AS CASE WHEN [Date].[YQM].CURRENTMEMBER.MEMBER_CAPTION = "2011" THEN "2011 AVERAGE" ELSE [Date].[YQM].CURRENTMEMBER.MEMBER_CAPTION END

MEMBER [Measures].[Year_2011_AvgUnits] AS SUM([Date].[YQM].[Year].&[2011],[Measures].[Exf Units])/10

MEMBER [Measures].[Share] AS CASE WHEN [Date].[YQM].CURRENTMEMBER.MEMBER_CAPTION = "2011" THEN 1 ELSE [Measures].[Exf Units]/[Measures].[Year_2011_AvgUnits] END

MEMBER [Measures].[Units] AS CASE WHEN [Date].[YQM].CURRENTMEMBER.MEMBER_CAPTION = "2011" THEN [Measures].[Year_2011_AvgUnits] ELSE [Measures].[Exf Units] END

SELECT
NON EMPTY { [Measures].[Month_to_Show],
[Measures].[Units],
[Measures].[Share],
[MEASURES].[Year_2011_AvgUnits] } ON COLUMNS,
NON EMPTY ( {[Date].[YQM].[Year].&[2011], [Date].[YQM].[Month]} ) ON ROWS,
{[Product].[Product Name].[Product Name]* [Account].[Ora Acct State].[Ora Acct State] } ON PAGES
FROM (SELECT ( {strtoset(@Year),[Date].[YQM].[Year].&[2011]}, strtoset(@Product)) ON COLUMNS FROM [CUBE]



Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #1393760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse