locked
Nested aggregate? RRS feed

  • Question

  • Hi all -
    I'm using SSRS 05 and I believe what I am trying to accomplish is a nested aggregate. In a table I have this expression to show a date difference.

    =IIF((Sum(IIF(Fields!ProgDescr.Value =

    "Typed",1,0)) = 1),(Fields!ProgDt.Value - Fields!RecvDt.Value),"-")

    This part is working fine. I am trying to get the average of this field in the Month Grouping.

    Any help is appreciated.

    Lee -

    Thursday, March 18, 2010 9:31 PM

Answers

  • 2008 R2 supports nested aggregates
    a workaround for 2005 involves including your first aggregate within your query where each row will also include the sum of that monthly grouping:

    select ...., (select sum(val) from foo where month(dateval) = month(t.dateval))
    from foo t
    where ...

    ofcourse for your situation it will be a bit more complicated moving the if-else expression to sql
    • Marked as answer by The_Lee Friday, March 19, 2010 7:09 PM
    Thursday, March 18, 2010 9:52 PM
  • You could write custom code to do this but that would be more complicated than just moving it to the query.  More on custom aggregates @ Robert Bruckner's blog here: http://blogs.msdn.com/robertbruckner/archive/2008/07/20/Using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

    This feature has been requested often which led to its inclusion in SQL 2008 R2.

    • Marked as answer by The_Lee Friday, March 19, 2010 7:09 PM
    Friday, March 19, 2010 1:33 AM

All replies

  • 2008 R2 supports nested aggregates
    a workaround for 2005 involves including your first aggregate within your query where each row will also include the sum of that monthly grouping:

    select ...., (select sum(val) from foo where month(dateval) = month(t.dateval))
    from foo t
    where ...

    ofcourse for your situation it will be a bit more complicated moving the if-else expression to sql
    • Marked as answer by The_Lee Friday, March 19, 2010 7:09 PM
    Thursday, March 18, 2010 9:52 PM
  • Thanks for the reply -

    Is moving it to the query the only option? Seems like such a simple task to accomplish within the table.

    Lee -

    Thursday, March 18, 2010 10:03 PM
  • You could write custom code to do this but that would be more complicated than just moving it to the query.  More on custom aggregates @ Robert Bruckner's blog here: http://blogs.msdn.com/robertbruckner/archive/2008/07/20/Using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

    This feature has been requested often which led to its inclusion in SQL 2008 R2.

    • Marked as answer by The_Lee Friday, March 19, 2010 7:09 PM
    Friday, March 19, 2010 1:33 AM