locked
New to MDX: "Total" column to the right hand side RRS feed

  • Question

  • Hi All,

    currently i am constructing a UI that displays the data directly from a MDX statement. I realize when i use [xx].[xx].members, the "total" column is always before the descendants, I actaully need it after the descendants, is there a way to do that in MDX?

    also, let's say i am given a range of a dimension, and i am applying that range with a Filter in MDX, will the "total" column be adjusted for the values of the filtered columns?

    Tuesday, April 20, 2010 7:14 PM

Answers

  • Hello,

    MDX language is very powerfull. If you know the required result, you can prepare it directly in MDX select statement. To achieve behaviour similar to Cube browse, use subcube:

    select
    	[Time].[Month-Year].Members on columns,
    	[Measures].[IncidentCount] on rows
    from 
    (
    	select
    	[Time].[Month-Year].[Month].&[2005]&[7]:[Time].[Month-Year].[Month].&[2007]&[8] on columns
    	from [Cube]
    )
    
    MP.
    • Marked as answer by Makubex Thursday, April 22, 2010 2:35 PM
    Thursday, April 22, 2010 8:07 AM

All replies

  • Re: Total

    {Descendants([Date].[Calendar].[Year].&[2009],[Date].[Calendar].[Month]),[Date].[Calendar].[Year].&[2009]}

    This will give you months of 2009 and total (2009) as the last column.

    Re: Range

    Yes, total will adjust based on the values of the range specified.

    Tuesday, April 20, 2010 8:10 PM
  • Hello,

    Expression [Dimension].[Hierarchy].Members returns a set of all members of given hierarchy including 'All' member if present (which is probably your 'total'). The order of members in returned set is given by design of the dimension and the hierarchy structure. If you need to change the order of members in a set, use Order function. If you only need to move the 'All' member to the end of the set, you can construct two sets, one for all members with the exception of 'All' member and the other with only 'All' member, and then use Union function to join them:

    select
    [Measures].[SomeMeasure] on columns,
    Union(
    		Except(
    			[Date].[Y - W - D].Members
    			, {[Date].[Y - W - D].[All]}
    		)
    		, {[Date].[Y - W - D].[All]}
    	) on rows
    from [Some Cube]
    

    'All' member is regular dimension member. If it is included in a set and the set is then filtered using Filter function, All member is returned if it fulfils the filter condition.

    Hope this will help.

    MP.

    Tuesday, April 20, 2010 8:25 PM
  • this is what i have been thinking of, so there is no configuration inside SSAS to achieve the same thing.

    Now i am confused about the Filter function, I found some examples online but i still ddont't understand the idea.

     

    I am trying to filter a date rate, so for example [date].[y-m].[2006].[3] to [date].[y-m].[2009].[5], how can i do that with filter?

    i tried doing this but that doesnt work at all:

    Filter([date].[y-m], [date].[y-m] > [date].[y-m].[2006].[3])

     

    thx for the advice

    Tuesday, April 20, 2010 9:49 PM
  • this is what i have been thinking of, so there is no configuration inside SSAS to achieve the same thing.

    Now i am confused about the Filter function, I found some examples online but i still ddont't understand the idea.

     

    I am trying to filter a date rate, so for example [date].[y-m].[2006].[3] to [date].[y-m].[2009].[5], how can i do that with filter?

    i tried doing this but that doesnt work at all:

    Filter([date].[y-m], [date].[y-m] > [date].[y-m].[2006].[3])

     

    thx for the advice

    Tuesday, April 20, 2010 9:50 PM
  • If you need a range of members, use this expression:

    [date].[y-m].[2006].[3] : [date].[y-m].[2009].[5]

    Filter function filters a set and returns only tupples fulfilling given condition. For every tupple in the set, the boolean expression is evaluated and if true, given tupple is returned in the resulting set.

    MP.

    Tuesday, April 20, 2010 10:05 PM
  • Hello,

    Expression [Dimension].[Hierarchy].Members returns a set of all members of given hierarchy including 'All' member if present (which is probably your 'total'). The order of members in returned set is given by design of the dimension and the hierarchy structure. If you need to change the order of members in a set, use Order function. If you only need to move the 'All' member to the end of the set, you can construct two sets, one for all members with the exception of 'All' member and the other with only 'All' member, and then use Union function to join them:

    select
    
    [Measures].[SomeMeasure] on
     columns,
    Union
    (
    		Except
    (
    			[Date].[Y - W - D].Members
    			, {[Date].[Y - W - D].[All]}
    		)
    		, {[Date].[Y - W - D].[All]}
    	) on
     rows
    
    from
     [Some Cube]
    
    

    'All' member is regular dimension member. If it is included in a set and the set is then filtered using Filter function, All member is returned if it fulfils the filter condition.

    Hope this will help.

    MP.

    I think this would put all of the  'all' at the end, correct? in that case this isn't what i am looking for, thanks anyways
    Tuesday, April 20, 2010 10:09 PM
  • If you can post the result you have now together with your MDX query and the result you want to have, hopefully I can help you more...
    Tuesday, April 20, 2010 10:12 PM
  • this is what i have now

     

    Select (
    	{DESCENDANTS([Time].[Month-Year].[2006-01-01],1,AFTER),	
    	[Time].[Month-Year].[2006-01-01],
    	DESCENDANTS([Time].[Month-Year].[2007-01-01],0,AFTER),
    	[Time].[Month-Year].[2007-01-01],
    	[Time].[Month-Year]}
    	,{[Measures].[IncidentCount]})
     on columns, 
     {DESCENDANTS([Company Tree].[Company Tree].[All].[Main Company(MAIN)],1)
     ,[Company Tree].[Company Tree].[All].[Main Company(MAIN)] }on rows from [Cube]
     ;
    

    So it is showing the time with "all" after the descendants. Notice the "All" from the last element in the first xaxis doesn't reflect the total of 2006 and 2007, but the 'All' of all data.

    My next step in implementation is to create a filter so that the user can define a time range, i.e. from 2005-07 to 2007-8. so, this is the output i am looking for, on the xaxis

     

    july august ... dec 2005 Jan ... Dec 2006 Jan...Aug 2007 Total

     

    is it possible?

    or do i have to construct the MDX properly by fitting each month together

    Thanks for your promptly help

    Wednesday, April 21, 2010 3:59 AM
  • Helo,

    Now I undestand your needs. The question here is why do you construct UI displaying results from MDX query. It is possible of course, but doesn't make sence, because this is already done - you can create a report to show and group the data in required layout and then present the report in your UI using ReportViewer control.

    If you really want to get all the required data and total from MDX, try something like this:

    with
    	set MonthSet as ([Time].[Month-Year].[Month].&[2005]&[7]:[Time].[Month-Year].[Month].&[2007]&[8]
    	member [Measures].[Total] as Sum(MonthSet, [Measures].[IncidentCount])
    select
    	MonthSet on columns,
    	{[Measures].[IncidentCount], [Measures].[Total]} on rows
    from [Cube]

    MP.

    Wednesday, April 21, 2010 8:50 AM
  • The reason is because I am not using ASP.net with my web application. I am required to construct a dynamic web application at which the user is able to choose their time interval, and display the desired measure and  y-dimension

    What if the 'Total' is not a Sum of all the months but some calculation of the months. i.e. Rate

    So there is no generic way to create the x axis so that the parents are behind the descendants?

    ie. Jan Feb March.... 2008 Jan Feb March 2009 All

    I need a dynamic way to create the x-axis so that the All will display the data of [Time].[Month-Year].[All] but subsetted to the interval i have selected, I guess this is asking too much. Same as the 2009 and 2008 if they are subsetted (i.e. from april to dec 2008)

     

    Its almost like how the browser view is done in SSAS, where if i were to create a filter for the Time Dimension, the "Grand Total" will adjust automatically

    • Edited by Makubex Wednesday, April 21, 2010 2:37 PM Add reference to SSAS
    Wednesday, April 21, 2010 2:13 PM
  • Hello,

    MDX language is very powerfull. If you know the required result, you can prepare it directly in MDX select statement. To achieve behaviour similar to Cube browse, use subcube:

    select
    	[Time].[Month-Year].Members on columns,
    	[Measures].[IncidentCount] on rows
    from 
    (
    	select
    	[Time].[Month-Year].[Month].&[2005]&[7]:[Time].[Month-Year].[Month].&[2007]&[8] on columns
    	from [Cube]
    )
    
    MP.
    • Marked as answer by Makubex Thursday, April 22, 2010 2:35 PM
    Thursday, April 22, 2010 8:07 AM