locked
AGGREGATE() issue RRS feed

  • Question

  • Hi

    I want to aggregate two or more tuplets using either SUM or AGGREGATE function. I faced a paradoxical issue in that:

    For Example, if  I try this MDX query

    SELECT [Measures].[Sales Amount] ON 0,


    AGGREGATE ({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}) ON 1


    FROM [ProdCube]

    It doesn't work as ROWS accept only members

    In the other hand,  if I try to define the aggregation within a member as follow

    WITH MEMBER X AS
    {AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])})}

    SELECT [Measures].[Sales Amount] ON 0,
     X ON 1
    FROM [ProdCube]

    Then I face another problem. In fact, the member is considered as measure and I think that measure couldn't be positioned in the 1/ROWS position

    So how do I to make use of the aggregation functions like the AGGERGATE() and SUM()

     

    Thank you

     

     


    The complexity resides in the simplicity
    Thursday, December 30, 2010 8:17 PM

Answers

  • For Example, if  I try this MDX query

    SELECT [Measures].[Sales Amount] ON 0,


    AGGREGATE ({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}) ON 1


    FROM [ProdCube]

    Aggregate() returns a value not a set/tuple/member, so you cannot use that in Axis1.

     

    WITH MEMBER X AS
    {AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])})}

    SELECT [Measures].[Sales Amount] ON 0,
     X ON 1
    FROM [ProdCube]

    1)    The Measures hierarchy already appears in the Axis0 axis, so you cannot use it in Axis1.

    2)    The calculated member expects a value not a set, so please remove the {}.

     

    You can try this:

    WITH MEMBER X AS

    AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}, measures.defaultmember)

     

    SELECT {[Measures].[Sales Amount],x} ON 0

    FROM [ProdCube]

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Monday, January 3, 2011 6:04 AM

All replies

  • From your aggregate calculated member above I am assuming you want to create a new member that represents the union of the two product lines? if so, what you need to use is

    with member [Dim Product].[Product Line].x as
    [Dim Product].[Product Line].&[M] + [Dim Product].[Product Line].&[T]
    
    SELECT [Measures].[Sales Amount] ON 0,
     X ON 1
    FROM [ProdCube]
    
    



    Javier Guillen
    MCTS / MCITP SQL Server 2008 BI
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    • Proposed as answer by Erdem Zengin Thursday, December 30, 2010 8:42 PM
    Thursday, December 30, 2010 8:35 PM
  • Aggregate function take two parameters the first is a set and the second is a non-calculated measure.

    For example  

    with member measures.x Aggregate({[Dim Product].[ProductLine].&[M],[Dim Product].[ProductLine].&[T]},Measures.[Sales Amount])

    As far as I now, In addition to your problem. you can not use aggregate function over calculated measure because the function doesn't know the aggregation property. Instead use sum, avg etc. what is appropriate. I faced the same problem =)

    if you want to show the the total for M and T in one row. Javier is right. You can create a new member by + operator with two members you have.


    BI Developer
    Thursday, December 30, 2010 8:50 PM
  • Hi erdemz and Javier,

    In fact, I want to use AGGREGATE because of the flexibility. I can define the aggergate function later to perform more custom aggregation combination that's way I prefered using AGGREGATE over + operator

    Thank you


    The complexity resides in the simplicity
    Thursday, December 30, 2010 10:18 PM
  • The aggregate function applies to measures.  However, in your example above there are only dimension members used in the aggregate function which made me think you wanted to union them into a new calculated member.

    This new calculated member could then be used as part of the aggregate function, when relating it to a measure; such as

    Aggregate(X, [Measures].[Sales Amount])
    
    

    In that way, the new calculated member will roll up the measure based on the underlying aggregation, and it would do so for the two product line members.




    Javier Guillen
    MCTS / MCITP SQL Server 2008 BI
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Friday, December 31, 2010 2:09 AM
  • For Example, if  I try this MDX query

    SELECT [Measures].[Sales Amount] ON 0,


    AGGREGATE ({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}) ON 1


    FROM [ProdCube]

    Aggregate() returns a value not a set/tuple/member, so you cannot use that in Axis1.

     

    WITH MEMBER X AS
    {AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])})}

    SELECT [Measures].[Sales Amount] ON 0,
     X ON 1
    FROM [ProdCube]

    1)    The Measures hierarchy already appears in the Axis0 axis, so you cannot use it in Axis1.

    2)    The calculated member expects a value not a set, so please remove the {}.

     

    You can try this:

    WITH MEMBER X AS

    AGGREGATE({([Dim Product].[Product Line].&[M]),([Dim Product].[Product Line].&[T])}, measures.defaultmember)

     

    SELECT {[Measures].[Sales Amount],x} ON 0

    FROM [ProdCube]

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Monday, January 3, 2011 6:04 AM