sql server reporting services: 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_Date
GH
Detail 123456 Open Suspend 1/1/2006 12:00
Detail 123456 Suspend Open 1/2/2006 12:00
Detail 123456 Open Suspend 1/3/2006 1:00
Detail 123456 Suspend Open 1/3/2006 2:00
GF 123456
GH
Detail 999999 Open Suspend 2/1/2006 12:00
Detail 999999 Suspend Open 2/2/2006 12:00
Detail 999999 Open Suspend 2/3/2006 1:00
Detail 999999 Suspend Open 2/3/2006 2:00
GF 999999

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 Function

End 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 Sub
End 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

Hello kmishle,

Thank you for using MSDN Managed Newsgroup Support.

From your description, my understanding of this issue is: You want to use
Shared Variables in Group Footers and the result is not as your
expectation. If I misunderstood your concern, please feel free to let me
know.

Based on your scenario, you could add an textbox which represent the total
count in the table detail row so that the last one is the total sum of the
group. Then you could refer the last one in the Group Footer.

To refer the last detail row, you could use the following expression:

=Last(ReportItems!(your text box name).Value)

Please let me know if this information is helpful. Thank you!

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Thanks for the response Wei,

I'm afraid that won't solve my problem. See, I'm not actually looking for a
count. I just used the count as an example of the symptom. Ultimately I
need to create an array variable using information from the details rows and
then run some calculations on that filled array from the group footer. I
need to be able to loop through the values in the array and add up the total
amount of time the ticket was in a 'Suspended' status.

[quoted text, click to view]