# Only summing unique values on groups • ### Question

• Hi

I have a report that displays classes and attendance to those classes and are grouped by Month,location, week, course and class. I need to sum the capatisy for each class at the end of each of those groups. When I perform the sum function obviously I get a running total of capatisy. So . . .

June 09
Training Centre1
1st - 5th
Electrical Course
Class 1 (Capatisy = 8)
Student Name      Booked      Attended      Cancelled
Student1                  1                1
Student2                  1                1
Student3                  1                0                  1
Student4                  1                1
Total     4      Total   3         Total  1
Course Capatisy Total = 32 (should be 8)

Plumbing Course
Class 1 (Capatisy = 8)
Student Name      Booked      Attended      Cancelled
Student5                  1                1
Student6                  1                1
Student7                  1                0                  1
Student8                  1                1
Student9                  1                1
Total     5      Total   4         Total  1

Course Capatisy Total = 40 (should be 8)
Week Total = 72 (should be 16)

I have look for a few days now just can't seem to find a solution. I was wondering if an adaptation of the following link would work but not sure where to start. http://social.msdn.microsoft.com/Forums/en-US/vsreportcontrols/thread/36e7e127-56bb-4c82-8958-ed04820431bb

Thank You

Chris
• Edited by Saturday, July 4, 2009 10:15 AM missing info
Saturday, July 4, 2009 10:13 AM

### All replies

• hi Chris,
do not use  Sum function for showing Course Capatisy Total. simply use Fields!Fieldname.value.

Aftab Ansari
Monday, July 6, 2009 11:03 AM
• Thanks for that, sorry I didn't make it clear I do need to total at each following group so I would need  course capaticy, week capaticy, training centre capaticy then finally total month capaticy

Regards

Chris

Monday, July 6, 2009 11:08 AM
• 1. place following code in custom code in report:
Function SubTotal(ByVal Value As Integer) As Integer(,)
Static TotalValue(1, 0) As Integer
TotalValue(0, 0) = Value
TotalValue(1, 0) += Value
End Function

2. put expression =code.SubTotal(Fields!Fieldname.value)(0,0) for Course Capatisy Total
3.put expression =code.SubTotal(Fields!Fieldname.value)(1,0) for showing grand total of Course Capatisy Total

Hope it will work....

Aftab Ansari
Monday, July 6, 2009 11:23 AM
• Hi Aftab

Sorry but that didn't work quite right. There is an issue I can see . . .

1) The capatisy is a running total and also needs to be reset after each course, week, training centre, and month (I guess a few more rows for each total will correct this).

I think I will have a bash at fixing the problem as it seems quite simple to manipulate your code

Chris

• Edited by Monday, July 6, 2009 1:51 PM corrections
Monday, July 6, 2009 1:45 PM
• Ok I have now tried to manipulate the code but I am unable to reset the totals when needed. It seems you cannot reset the fields at key points (group footers). This seems simple concept but I am unable to figure it out.

Any help would be appreciated

Chris
Monday, July 6, 2009 3:13 PM
• Well after a night of frustration I have decided to go down the route of a sub report, so the capacity will be correct on all the grouped fields and the sub report will hold the student data. This way the Capacity is added at the correct points. A barbaric solution to a simple issue ah well it gets it done!!!

Regards

Chris

Tuesday, July 7, 2009 10:46 AM
• Hi Chris,

Have you tried to use manipulated code for resetting the total on change of group.

you have solved your problem by putting subreport but it consume a lot of resources  as it will call the subreport for every group.

do you want me to manipulate the code for you or you want to continue with subreport concept?

Aftab Ansari
Tuesday, July 7, 2009 11:53 AM
• Ultermatly I would love to solve this without the subreport as you say less stress on the server. If you could send over the code I would greatly appreciate it. When I tried it seemed to reset all the groups. All I did is send a group code through to the function then resetting that total relating to that group. Didn't seem to work. Code was something like

Function SubTotal(ByVal Value As Integer,Optional GroupID AS String = "") As Integer(,)
Static TotalValue(4, 0) As Integer
'0 = Practical
'1 = Course
'2 = Week
'3 = Training Centre
'4 = Month

TotalValue(0, 0) = Value
TotalValue(1, 0) += Value
TotalValue(2, 0) += Value
TotalValue(3, 0) += Value
TotalValue(4, 0) += Value

'Reset at the end of parent group

IF GroupID = "Course" Then
TotalValue(0, 0) = 0

ELSEIF GroupID = "Week" Then
TotalValue(1, 0) = 0

ELSEIF GroupID = "Training Centre" Then
TotalValue(2, 0) = 0

ELSEIF GroupID = "Month" Then
TotalValue(3, 0) = 0

END IF

• 