Reporting Services – How to get sum of values using reportitems!textbox1.value

reporting-services

I am using custom code to get the sum of values using reportitems!textbox1.value because I know I cannot use it without custom code because of the error 'Aggregate functions can be used only on report items contained in page headers and footers'. I am using a table inside a list with the list group 'list1_Details_Group'. I am succeeding in getting the sum with the following code but it is not giving the sum per 'list1_Details_Group'.
What I want is to have the total of the values per the 'list1_Details_Group'
My code is:

 Public sum_of_crew As Integer = 0 
    Public Function SumUp(ByVal value As Integer) As Integer
    sum_of_crew = sum_of_crew + value 
    Return sum_of_crew 
    End Function

I am calling this function from a textbox inside my table detail row with the expression which accumulates the value:

=Code.SumUp(ReportItems!textbox134.Value)

I then have another textbox inside the list outside the table with the following expression which shows the sum:

=Code.sum_of_crew

This textbox which is supposed to show the accumulated sum as per the last value in the table detail row is giving the correct sum but only for the first of the group values as the second of the group values are including the value of the first of the group values. The value does not start at 0 again after the value of the first of the group values.
Please help!

Best Answer

I have played around and did the following which is working for me and I hope it will help somebody else.

I created a group named 'table1_Group1' inside my table by right clicking on the detail row on the detail icon on the left (not on the fields) and choosing 'Insert Group' then on the 'General' tab at the 'Group on:' I chose on the Expression dropdownlist the same field I used at my List Detail Group. I only left 'Repeat group footer' checked. The reason for the group in the table is to include the rownumber of the group in my code to reset the value to 0 each time the group row number starts at 1. I then changed my code to this:

Public sum_of_crew As Integer = 0
Public Function SumUp(ByVal value As Integer,ByVal value2 As Integer) As Integer
If value2 = 1 then
sum_of_crew = 0
End If
sum_of_crew = sum_of_crew + value
Return sum_of_crew
End Function

I am calling this function from a textbox inside my table detail row with the expression which accumulates the value which I changed to include the rownumber of the group I have created inside my table:

=Code.SumUp(ReportItems!textbox134.Value,RowNumber("table1_Group1"))

I then have another textbox inside the list outside the table with the following expression which shows the sum:

=Code.sum_of_crew

With the above changed code and expressions the total value per group is now showing at the bottom of each group by using reportitems!.....value instead of fields!.....value. Credit to Daren with an answer on a forum who gave me direction.

Related Topic