locked
Problem: Aggregate Functions cannot be especified as nested aggregates- RRS feed

  • Question

  • Hi everyone.

    I am making a matrix report with SQL RS 2008 R2. I added a Total Column and I modified the function as show bellow:

    =Sum( 
    IIf(Fields!idPregunta.Value<>"16", Fields!Resultado.Value, Last(Fields!Resultado.Value))
    )

    I have the next error message:

    "An error occurred during local report processing. The definition of the report is invalid. The value expressionfor the textrun uses a First, Last or Previous aggregate in an outer aggregate. These aggregate functions cannot be specified as nested aggregates."

    What alternativa can I use to solve this problem? This is a dynamic report.

    Thanks.

    Monday, June 16, 2014 8:22 PM

Answers

  • The reason for this restriction is that it doesn't make sense to embed a first, last, or previous into another aggregate. Ususally this happens because we are trying to do something else (that does make sense) but are expressing it incorrectly.

    In this case, your formula would SUM up the individual Resultado values. When idPregunta = 16 it would substitute the Resultado value from the last record for the current records resultado value. So if you have these records:

    idPregunta Resultado
    3                 4
    16               12
    12                11
    16                3
    7                 22

    The result of your formula (if it worked) would be 81 (4+22+11+22+22). Is that what you actually want? If so then you have to get creative. You need to abstract the "Last" operation from your formula. If You add a sibling textbox (We'll say its name is LastResultadoTextBox) in the same scope as your total cell, you can set its value to =Last(Fields!Resultado.Value). Now set your total formula to:

    =Sum(IIfFields!idPregunta.Value<>"16", Fields!Resultado.Value, ReportItems!LastResultadoTextBox.Value)

    I tested it in my local environment successfully. 


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Edited by Tim Pacl Monday, June 16, 2014 10:18 PM
    • Proposed as answer by Katherine Xiong Tuesday, June 17, 2014 3:43 AM
    • Marked as answer by MazingerJ Tuesday, June 17, 2014 1:45 PM
    Monday, June 16, 2014 10:13 PM