Hi folks,I’m relatively new to both SQL Reporting Services as well as .Net programming. I come from a Crystal Reports background and I’m running into a problem duplicating a common technique used in my old Crystal reports.The simplified description of the problem I’m having is this… it seems that public shared variables are evaluated in the wrong order when accessing them from code in grouped tables. It seems that the group header is evaluated first, then the group footer and finally all the detail rows. As a result, if I modify a shared variable in the details section of a grouped table, that modification isn’t recognized in the group footer.Here are the details of what I’m trying to accomplish…I have added a table to my report which consists of helpdesk ticket information. I've grouped the table by ticket number. For each ticket number, I need to evaluate an audit table to determine how much time the ticket spent in suspend status. To do this, I've added my audit table fields (Old_value, New_Value, Change_date) to the details of the table.So a typical grouping might look like this:---------Ticket---Old_value---New_Value---Change_DateGH--------------------------------------------------------- Detail---123456---Open--------Suspend-----1/1/2006 12:00Detail---123456---Suspend-----Open--------1/2/2006 12:00Detail---123456---Open--------Suspend-----1/3/2006 1:00Detail---123456---Suspend-----Open--------1/3/2006 2:00GF--------------------------------------------------------- GH--------------------------------------------------------- Detail---999999---Open--------Suspend-----2/1/2006 12:00Detail---999999---Suspend-----Open--------2/2/2006 12:00Detail---999999---Open--------Suspend-----2/3/2006 1:00Detail---999999---Suspend-----Open--------2/3/2006 2:00GF--------------------------------------------------------- The total suspend time for each ticket should ultimately be 25hrs in this case.I’m using a custom assembly in this report to do many of my business calculations (which are used in many different reports). In this assembly, I’ve created the following public structure:Public Structure SuspInfo 'Public Members Public OldValue As String Public NewValue As String Public AuditType As String Public AuditDate As Date Public Overrides Function ToString() As String Return OldValue & " - " & NewValue & " - " & _ AuditType & " - " & AuditDate.ToString End FunctionEnd structure
I’ve also created the following collection class:Public Class SuspCollection Inherits Collections.CollectionBase 'Add Susp to the collection Public Sub Add(ByVal newSusp As SuspInfo) Me.List.Add(newSusp) End Sub Public Sub Remove(ByVal oldSusp As SuspInfo) Me.List.Remove(oldSusp) End Sub 'Item property to read or update a Suspinfo ‘at a given position in the list Default Public Property Item(ByVal index As Integer) As SuspInfo Get Return Me.List.Item(index) End Get Set(ByVal value As SuspInfo) Me.List.Item(index) = value End Set End Property 'New implementation of the clear class Public Shadows Sub Clear() 'Clear the collection base MyBase.Clear() End SubEnd Class
In the detail row of the table, I call the following function: Public Function AddSuspInfo(ByVal OldValue As String, _ ByVal NewValue As String, ByVal auditdate As DateTime) If OldValue = "Suspended" Or NewValue = "Suspended" Then 'declare Suspinfo record Dim objNewSuspInfo As SuspInfo objNewSuspInfo.OldValue = OldValue objNewSuspInfo.NewValue = NewValue objNewSuspInfo.AuditDate = auditdate If OldValue = "Suspended" Then objNewSuspInfo.AuditType = "Unsuspended" Else objNewSuspInfo.AuditType = "Suspended" End If objSuspInfoArray.Add(objNewSuspInfo) Else Return Nothing End If Dim txtSuspArray As String = "" For Each susprecord As SuspInfo In objSuspInfoArray txtSuspArray = txtSuspArray & susprecord.ToString & vbCrLf Next Return txtSuspArray End Function
The function not only builds the array of suspend info objects but it also displays the array in text form so I can see what is happening to the array in each detail row. I’ve also included the following function to validate that the array is populated properly. Public Function showarraycount() Return objSuspInfoArray.Count End Function
So, using the first ticket in the table above as a sample, the showarraycount function should return the number 4 in the group footer. If I call the function from the detail row, it increments the count correctly and ultimately, on the last detail row, the function returns 4. This shows that my code for defining and populating the collection is working correctly. However, if I call the function from the group footer, it returns 0 instead of 4. It appears that the objSuspInfoArray is being re-instantiated in the group footer eventhough I’ve defined it as a public shared object.I thought that I may have overcomplicated things so I created the following simple function to be used as imbedded code within the report properties sheet. Public Shared intTest As Integer Public Shared Function AddIntTest() intTest = intTest+1 Return intTest End Function
I then called that function in the group header, detail row, and group footer. I would have expected the function to increment the shared variable by one each time it was called regardless of where in the table it was called. That’s certainly not the result I got.For the first ticket number, the group header returned 1 which is what I expected. In the first detail row though the function returned 3!? It skipped 2 entirely. The remaining three detail rows incremented the variable as expected (4, 5, and 6). Then, in the group footer, the value went back to 2. In the next ticket’s group header the value went back to where the details of the first ticket left off and returned 7. The first detail row of the second ticket returned 9, 10, 11, and 12 (again, skipping 8). And finally, the group footer of the second ticket returned 8.So it looks like the report is evaluating the group header first, then the group footer, then the detail rows. That’s why the first detail row seems to skip a number… it’s because the report is evaluating the footer of the table before it evaluates the details. This seems extremely counterintuitive. So, how can I resolve this issue. I really need to set a shared variable in the details row and use that information in the footer. It’s the only way I can think of to evaluate the total suspend time of a ticket. I had planned on populating an array in the details section then building a function to be used in the group footer which would loop through that array and add up all the suspend time. This clearly won’t work if the report MUST evaluate the group footer before the details rows.Crystal reports has a feature called “Whileprintingrecords” which lets you evaluate a function after all data has been queried and before displaying the results. Using the “Whileprintingrecords” feature I’m able to force Crystal reports to populate the array in the details secion BEFORE it uses that array in the group footers. Is there a similar capability in SSRS? Is there an alternative method I’m not considering? Is this a bug or is it an intentional design plan?I’ve search all over the web for any info on this an it appears that many people have similar isseus but I’ve yet to find a suitable solution. Any help you could offer would be much appreciated. 
Using Shared Variables in a Group Footer
Posted - 2006-07-31 : 16:51:23