Aggregate functions cannot be nested inside other aggregate functions RRS feed

  • Question

  • Hi everyone,

    I have this problem.
    I'm using table, and in field i use an expression this is the expression:
    IIf(Sum(Fields!Quantity.Value)<Fields!Target.Value,0,(Sum(Fields!Quantity.Value)- Fields!Target.Value)*Fields!Commission.Value

    And then the footer I want to SUM the result of each expression.
    But when I use SUM with this expression :
    SUM(IIf(Sum(Fields!Quantity.Value)<Fields!Target.Value,0,(Sum(Fields!Quantity.Value)- Fields!Target.Value)*Fields!Commission.Value)

    It's give me this error :
    Error    1    The Value expression for the textbox 'textbox66' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue).  Aggregate functions cannot be nested inside other aggregate functions.

    What should I do?
    If someone has an idea, suggestion or sample code for such expression it will be great!

    Thanks in advance!

    Wednesday, August 1, 2007 10:12 AM

All replies

  • I have an idea but i don't know how to do it.

    The idea is to save the value in a variable using code.

    This is my code that doesn't work

    Code Snippet

    Private sumData as integer=0

    Public Function saveValue(byval Data as Integer)As Integer

    return Data
    End Function

    Public Function getData()As Integer
    return sumData
    End Function

    And in my table field I use this expression

    =Code.saveValue(IIf(Sum(Fields!Quantity.Value)<Fields!Target.Value,0,(Sum(Fields!Quantity.Value)- Fields!Target.Value)*Fields!Commission.Value)

    And in footer I use this expression

    The result in field detail is work

    ex:  10

    But in the field footer it give value 0 (it should give 55)

    Wednesday, August 1, 2007 11:02 AM