locked
Nested aggregates problem RRS feed

  • Question

  • I have data containing the number of employees for the companies of a corporation, grouped in a table by company, and for each group the last record is displayed as this is the current total.

    For instance in January, Company1 had 10 employee and in February it had 20, i.e. the current total should be the one for February = 20.

    I need to display a count of these current totals.

    This is a simple example:

     

    Group                                  Value

    Company1

    TotalEmployees January       10

    TotalEmployees February      20

    Current total:                         20                  <= using the Last() function

     

    Company2

    TotalEmployees January       15

    TotalEmployees February      25

    Current total:                         25                  <= using the Last() function

     

    Total employees                  ??                  <= should be (20+25=45)

     

    What I want now is to show a sum of the aggregated current total. The “Total employees” should be computed by adding each group's last record (for instance, in this example the grand total should be 20 + 25 = 45). However, I can't nest aggregate functions. What can I do?

    Furthermore the total count should be on the row for the group i.e. it should not be in the footer, making it a little more difficult as totals are calculated before the rows are processed.

    Corporation:           Count of all employees in the corporation
        Company:            Count of all employees in the company
    Monday, May 4, 2009 11:46 AM

Answers

  • Hi NickOlaz,

     

    You are looking for handling aggregates of aggregates. Unfortunately, this is not supported so far. Generally speaking, the processing sequence of reporting services engine is: table header, table footer, group header, group footer, then details. This is the reason why we cannot use the expression like sum of sum. I suggest you should change the expression to avoid the expression with aggregates of aggregates. But if aggregates of aggregates is the only way to achieve that, you have to face to the challenge of aggregates of aggregates. You can try the blow steps:

     

    1 Add a “fake” in dataset like this

    union

    select '_fake' as company, '_fake' as value, ………

     

    2 Sort the data by descending, so the fake data will show at the last group because of the character ‘_’.

     

    3 Please copy the following code to the custom code area:

     

    Dim Aggregation as Decimal

      

    Function AddValue(ByVal NewValue As Decimal) as Integer 

         Aggregation  = Aggregation  + NewValue 

    return Aggregation 

    End Function 

      

    Function GetAggregation () as object  

    GetAggregation  = Aggregation 

    Aggregation  = 0 'Reset the group 

    Return  GetAggregation 

      

    End Function  

     

    4 Delete the footer of group1, and then right-click the handle of group1’header, insert a row above. Here's the structure:

     

    Group 1 new Header

    Group 1 Header
                Group 2 Header

    details
                Group 2 Footer

     

    handles

                                            (Table header )

    1

    =Previous(Fields!group1.Value)

    =code.GetAggregation()

    1

    = (Fields!group1.Value)

     

    2

     =Fields! Group2.Value 

    =code.AddValue(1)

    ==

    details

    details

     

            (Table footer)

           

     

     













    5 Right-click the handle of the new header, click the properties
    (not edit group). In Properties window, set the visibility to be=iif(Previous(Fields!company.Value) is nothing, true,false)”.

     

    6 Right-click the handle of other rows, click the properties (not edit group). In Properties window, set the visibility to be“=iif(=Fields!group1.Value = "_fake", true,false)”,

     

     

    There is the only one workaround I can think of. Using custom code to store the value and then return the value in the footer. For more information please see these two similar threads, both of them achieved handling aggregates of aggregate:

    http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/0a08b6f3-330e-47ea-b04f-6cd9c9aaedcb

    http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/11c69475-289f-48a7-ba39-b976689bbf42

     

    Hope this helps.

    Raymond

    Wednesday, May 6, 2009 7:28 AM

All replies

  • Hello,

    Try this expressions

    =Sum(Fields!FieldName.Value,"GroupCompany") -- to get 20 , 25

    =Sum(Fields!FieldName.Value,"GroupCorparation") -- to get 20 + 25


    Hope its clear and helpful...


    Yes It will give u the total sum. Not a proper solution for the requirement... Sorry for it...

    Pavan http://www.ggktech.com
    Monday, May 4, 2009 12:01 PM
  • I'm afraid that will return the sum of all records i.e.: 10+20+15+25.

    I need the sum of the fields where i used the Last() function to get the current number of employees.

    I have tried with a calculated field in the dataset, but calculated fields can't use aggregates as well :(

    Then i tried making som custom code that stores the count in a global variable, actually allowing me to get the right number.
    However when trying to set this calculated value into the company row i am rejected as the field now is Read Only. I believe this has something to do
    with the RS model of execution.

    So I'm feeling pretty stuck here, I can't imagine being the only person with this issue.

    Any help is appreciated.
    Monday, May 4, 2009 12:20 PM
  • Hi NickOlaz,

     

    You are looking for handling aggregates of aggregates. Unfortunately, this is not supported so far. Generally speaking, the processing sequence of reporting services engine is: table header, table footer, group header, group footer, then details. This is the reason why we cannot use the expression like sum of sum. I suggest you should change the expression to avoid the expression with aggregates of aggregates. But if aggregates of aggregates is the only way to achieve that, you have to face to the challenge of aggregates of aggregates. You can try the blow steps:

     

    1 Add a “fake” in dataset like this

    union

    select '_fake' as company, '_fake' as value, ………

     

    2 Sort the data by descending, so the fake data will show at the last group because of the character ‘_’.

     

    3 Please copy the following code to the custom code area:

     

    Dim Aggregation as Decimal

      

    Function AddValue(ByVal NewValue As Decimal) as Integer 

         Aggregation  = Aggregation  + NewValue 

    return Aggregation 

    End Function 

      

    Function GetAggregation () as object  

    GetAggregation  = Aggregation 

    Aggregation  = 0 'Reset the group 

    Return  GetAggregation 

      

    End Function  

     

    4 Delete the footer of group1, and then right-click the handle of group1’header, insert a row above. Here's the structure:

     

    Group 1 new Header

    Group 1 Header
                Group 2 Header

    details
                Group 2 Footer

     

    handles

                                            (Table header )

    1

    =Previous(Fields!group1.Value)

    =code.GetAggregation()

    1

    = (Fields!group1.Value)

     

    2

     =Fields! Group2.Value 

    =code.AddValue(1)

    ==

    details

    details

     

            (Table footer)

           

     

     













    5 Right-click the handle of the new header, click the properties
    (not edit group). In Properties window, set the visibility to be=iif(Previous(Fields!company.Value) is nothing, true,false)”.

     

    6 Right-click the handle of other rows, click the properties (not edit group). In Properties window, set the visibility to be“=iif(=Fields!group1.Value = "_fake", true,false)”,

     

     

    There is the only one workaround I can think of. Using custom code to store the value and then return the value in the footer. For more information please see these two similar threads, both of them achieved handling aggregates of aggregate:

    http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/0a08b6f3-330e-47ea-b04f-6cd9c9aaedcb

    http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/11c69475-289f-48a7-ba39-b976689bbf42

     

    Hope this helps.

    Raymond

    Wednesday, May 6, 2009 7:28 AM
  • Hi Raymond,

    Thanks for your reply.

    I've also come to believe that the only approach is a workaround, the desired functionality is not possible in the construction of a report.
    Your answer addresses the problem, and I have made a smiliar approach as stated in my entry, however the execution model prevents me from setting the value in the textbox (readonly). I believe your apporach will suffer from the same limitations.

    However I have implemented a solution, basically a new field in the dataset, containing the value 0 (zero) if there are newer records with the same corporation/company. This will result in only the last (latest) row having the actual count of employees i.e. i get my total count by sum() of this new field. Voilà!


    /NickOlaz

    Friday, May 15, 2009 9:49 AM