Need Help on MDX Query..

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Need Help on MDX Query..

Need Help on MDX Query..

Rate Topic

Display Mode

Topic Options

Author

Message

Sri8143

Posted Monday, December 03, 2012 9:14 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:06 PM
Points: 358, Visits: 2,117

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

Mackers

Posted Thursday, December 06, 2012 10:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 117, Visits: 460

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

Sri8143

Posted Thursday, December 06, 2012 2:45 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:06 PM
Points: 358, Visits: 2,117

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 »

Permissions