SQL Reporting services 05 - custom code sum display on table header

Question

I have a view retrieving data like

Course Attendee if-student shadow-tutor course-max

CS1     steve      no           mark        5

CS1     anna       yes          dan         5

and after retrieving, I select the course, count(attendee), shadow-tutor, course-max where if-student = Parameters!if_student. The result would be like:

Course shadow-tutor Actual# max#

CS1       mark         1

          dan          1
        CS1 total:     2      5

in the report, I used Sum(Fields!Actual.Value) and Fields!Max.Value for the total column. And at the header, I wanted use Sum(Fields!Actual.Value) and Sum(Fields!Max.Value) as the total. However it does not work. the Sum(max) will return me the sum of all cols, in this case, it returns 10 instead of 5.

I tried to use a custom code as following:

Public Dim total_cmax As Integer = 0

Public Function sum_cmax(cmax As Integer) As Integer
   total_cmax = total_cmax + cmax
   Return cmax
End Function

Public Function get_cmax(obj As Object) As Integer
   obj.Value =  total_cmax
End Function

But interestingly, when I use the get_cmax in table, it always return me 0. I have to create a textbox with get_cmax below the table which would generate the correct answer for me.

Any solution for this? I tried lots of method but no one works. I just want the sum can be displayed at the beginning but not the end.

Answer 1

Well I will post some methods that I've tried. Some of them are from webs (1 week research), and some are from myself.

1,define a scope

=SUM(Fields!Max_Number, "table1_Courses")

or

=RunningValue(Fields!Max_Number.Value,SUM,"table1_Courses" )

This does not work since it still trying to sum the hidden values though they are not even displayed.

2, Aggregation functions

=SUM(Fields!Max_Number.Value)/RowNumber("table1_Courses")

This displayed as same as Fields!Max_Number.Value, however since SSRS05 does not support nested aggregation, you cannot sum it again on the header.

3, Using ReportItems

 =Sum(ReportItems("course_max").Value)
 =ReportItems("total_max").Value

Well, the Aggregation function on ReportItems could only be used on header/footer. And you cannot set a reference from body. Seems MS make them running separately so you just cannot refer each other.

Another issue is, when the report is not in the same page, it will only return the result for the current page. E.g. you have 200 in total, 150 on 1st page and 50 on second, then in the 1st page the Sum(ReportItems("course_max").Value) shows 150 but not 200.

4,Using custom code.

Public Dim total_cmax As Integer = 0

Public Function sum_cmax(cmax As Integer) As Integer
   total_cmax = total_cmax + cmax
   Return cmax
End Function

Public Function get_cmax(obj As Object) As Integer
   obj.Value =  total_cmax
End Function

Cannot imagine I need to write such a simple code for such a simple function for MS! Well it works but not perfect.

The get_cmax function cannot be used before the sum_cmax, otherwise it would run the get_cmax very first which will return 0, and all get_cmax on the page (even after the 'sum_cmax') will return 0. Again, MS does not give you any chance to change the order of rendering. So you have to put the get_cmax after the table, AND, outside of your table.

For now this is the only solution I could find. Just wonder if anyone could find a way to display the value at the header. Putting a =ReportItems("total_max").Value before the table will return you a 0. So I really cannot figure out a way to fix this.

Hope this helps.