none
SSRS embedded code scope question

    Question

  • Can someone please provide some insight about how embedded code works in terms of scope?  I am trying to show moving average values on charts using the custom code from http://msdn.microsoft.com/en-us/library/aa964128%28SQL.90%29.aspx.  My chart has an actual series group, which has multiple values.  To be specific, my data series is profit per unit; my series group is division; and I have time on the X axis.  The "division" series group can have multiple values such as "US", "Europe", and "Asia".  Looking at the moving average custom code from the above link, I really wonder how the global variables, such as "queue" and "queueSum", are handled across the multiple series group values.  Can it correctly separate the queue for division "US" from the queue for division "Europe" and "Asia"? 

    Also, I have multiple charts ont he same report and they all use the moving average custom code.  Does SSRS use different instances of the global variables so that one chart is calculated separately from another? 

    Friday, September 17, 2010 3:38 PM

All replies

  • Hi,

    I never make embeded code work in terms of scope successfully. Generally, we use the report services built-in aggregation functions to do it, just as the solutions mentioned in the link you provided. So, if possible, please clarify your reuqirement (attach the picture to illustrate) here and it might be able to be arrived using reporting services built-in aggregation methods instead of custom code.

    thanks,
    Jerry

    Tuesday, September 21, 2010 7:40 AM
    Moderator
  • I found a solution myself.  It appears that the global variables do have global scope for the entire report.  So if a chart has multiple series groups, it does not work properly.  The original code could mix up the  values for the different series groups.  I have updated the code to the following for my own need:

     

    Class QueueValues
        Public queueSum As Double = 0
        Public queueFull As Boolean = False
        Public queue As New System.Collections.Generic.Queue(Of Double)
    End Class

    Private queueLength As Integer = 6

    Private myhash As New System.Collections.HashTable

        Public Function MovingQueue(ByVal currentValue As Double, ByVal currentTeam As String) As Object

            Dim removedValue As Double = 0
            Dim currentQueueValues As QueueValues

            If Not myhash.ContainsKey(currentTeam)  Then
                myhash.Add(currentTeam, New QueueValues)
            End If

            currentQueueValues = myhash(currentTeam)

            If currentQueueValues.queue.Count >= queueLength Then
                removedValue = currentQueueValues.queue.Dequeue()
            End If

            currentQueueValues.queueSum += currentValue
            currentQueueValues.queueSum -= removedValue
            currentQueueValues.queue.Enqueue(currentValue)
            If currentQueueValues.queue.Count < queueLength Then
                Return Nothing
            ElseIf currentQueueValues.queue.Count = queueLength And currentQueueValues.queueFull = False Then
                currentQueueValues.queueFull = True
                Return currentQueueValues.queueSum / queueLength
            Else
                Return (currentValue - removedValue) / queueLength
            End If
        End Function

     

    As you can see, I created a private class to hold the queue related values, and then use a hash table to store instances of the class, one per team.  This way I get to make sure that the values are not mixed up between teams, which are my series groups.

     

    I don't know how to attach a picture.  But the chart basically has multiple lines, each representing moving average values for a team.

    Tuesday, September 21, 2010 5:08 PM
  • How does your "myhash" get garbage collected so it is not sticking around in memory forever? 


    Thanks,

    MC


    MC
    Tuesday, September 06, 2011 10:07 PM