Only summing unique values on groups RRS feed

  • 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

    • Edited by Chris Connor 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



    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
            Return TotalValue
        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

    Thanks for your help


    • Edited by Chris Connor 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

    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!!!



    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

        Return TotalValue
    End Function


    Tuesday, July 7, 2009 12:29 PM
  • Can you please send me a valid output of your report with required comments? So, that i can deliver you desired code..

    Aftab Ansari
    Tuesday, July 7, 2009 3:00 PM