Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to calculate the pecentage of parent with multidimension

Отвечено how to calculate the pecentage of parent with multidimension

  • Tuesday, December 04, 2012 8:54 AM
     
      Has Code
    Sign In to Vote
    0

    hi experts,

    I want to change one of my MDX queries from count to percentage with many dimensions, the original MDX likes below: 

    SELECT CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Date].[Year].[Year]})}), 
				Hierarchize({DrilldownLevel({[Date].[Month].[Month]})})), 
				Hierarchize({DrilldownLevel({[Date].[Day Of Month].[Day Of Month]})})), 
				Hierarchize({DrilldownLevel({[License].[Activation Status].[Activation Status]})}) ) ON COLUMNS, 
NON EMPTY {([Measures].[Session Count])} ON ROWS    
FROM [Cube Name] 
WHERE ([Application Info].[Serialization State].[Serialized],
		{ [Date].[Year - Month - Day].&[2012].&[10] },
		{[License].[License Type].&[SUBSCRIPTION]})

    And the result when executing the MDX above  likes this:


    2012 2012 2012 2012 2012 2012 2012 2012
    10 10 10 10 10 10 10 10
    1 1 1 1 2 2 2 2
    Activated Serialized Trial Unlicensed Activated Serialized Trial Unlicensed
    Session Count 4089 104 null 41 3825 97 null 24

    And we want to show the percentage like this:


    2012 2012 2012 2012 2012 2012 2012 2012
    10 10 10 10 10 10 10 10
    1 1 1 1 2 2 2 2
    Activated Serialized Trial Unlicensed Activated Serialized Trial Unlicensed
    Session Count 96.58 2.46 0.00 0.97 96.93 2.46 0.00 0.61

    I can only use MDX for percentage of parent with single dimension, but have no idea on how to calculate percentage with multiple dimensions. anyone can help me? Really appreciate it.



    • Edited by JLiu0728 Tuesday, December 04, 2012 9:00 AM
    •  
     

All Replies

  • Tuesday, December 04, 2012 9:58 AM
     
     Answered Has Code
    Sign In to Vote
    0

    to all, 

    Finally I got the result as I expect after going through many threads in the forum. Here is the MDX I wrote: 

    WITH MEMBER [MEASURES].[Users Percentage]
AS iif(
([Measures].[Session Count],
Axis(0).Item(0).item(Axis(0).item(0).count-1).Dimension.currentmember.Parent)=0,
null,
[Measures].[Session Count]/
([Measures].[Session Count],Axis(0).Item(0).item(Axis(0).item(0).count-1).Dimension.currentmember.Parent) *100),
FORMAT_STRING = "00.00"

select  CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Date].[Year].[Year]})}), 
				Hierarchize({DrilldownLevel({[Date].[Month].[Month]})})), 
				Hierarchize({DrilldownLevel({[Date].[Day Of Month].[Day Of Month]})})), 
				Hierarchize({DrilldownLevel({[License].[Activation Status].[Activation Status]})}) ) ON COLUMNS, 
NON EMPTY {([Measures].[Users Percentage])} ON ROWS    
FROM [Cube Name] 
WHERE ([Application Info].[Serialization State].&[Serialized],
		{ [Date].[Year - Month - Day].&[2012].&[10] },
		{[License].[License Type].&[SUBSCRIPTION]})


    Thanks all for your interesting. 

    Here is a good thread we can read for this issue: http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/thread/60FECDED-F13D-4945-98A1-3DD04D820F17.

    --

    Thanks, 
    Justin