none
[Forum FAQ] How to calculate the increment between two columns in Column Group? RRS feed

  • 일반 토론

  • Introduction

    In a Reporting Services report, if you add a column group on the tablix, the tablix would look like the screenshot below when previewing report.

    Then the requirement is that add a column after each month to calculated increment on the basis of previous month amount. If we use the expression
    =Fields!Amount.Value-Previous(Fields!Amount.Value)
    in that column directly, we will get the error
    The use of a Previous aggregate function in a TablixCell within Tablix 'TablixName' is not supported.

    This error occurs when you use a Previous in the cell that belong to a column group and a row group. When using this function in that cell, RS cannot to decide to get column previous value or row previous value. So you cannot use a Previous in the cell that belong to a column group and a row group.

    In this article, I will demonstrate how to calculate the increment between two columns in Column Group.

    Solution

    Since Previous function is not supported in that cell, then you can pass the amount of each month to custom code and store the values on a queue, and calculated the increment between each month in the custom code and return it to that column. Copy the custom code below and paste it to your report. (Right-click your report>Report Properties>code).

    Private queueLength As Integer = 2 Private queueSum As Double = 0 Private queueFull As Boolean = False Private idChange As String="" Dim queue As New System.Collections.Generic.Queue(Of Integer) Public Function CumulativeQueue(ByVal currentValue As Integer,id As String) As Object

    Dim removedValue As Double = 0 If idChange <> id then ClearQueue() idChange = id queueSum = 0 queueFull = False CumulativeQueue(currentValue,id) Else If queue.Count >= queueLength Then removedValue = queue.Dequeue() End If queueSum += currentValue queueSum -= removedValue queue.Enqueue(currentValue) If queue.Count < queueLength Then Return Nothing ElseIf queue.Count = queueLength And queueFull = False Then queueFull = True Return (queueSum-currentValue-currentValue) Else Return (queueSum-currentValue-currentValue) End If End If End Function

    public function ClearQueue() Dim i as Integer Dim n as Integer = Queue.Count-1 for i=n To 0 Step-1 queue.Dequeue() next i End function

    Then refer to the steps below to achieve the requirement.

    1. Right-click the handle of the column group>Insert Column>Inside Group-Right
    2. Type the expressions below to the inserted column.
      =Code.CumulativeQueue(Fields!Amount.Value,Fields!Type.Value)
    3. The design surface looks like the screenshot below.
    4. The report looks like below.

    Besides, you can use this custom code to calculate the average value for previous few month. Such as, if you want to calculate previous 3 months average value, you can change the queue length to 3 and return queueSum/queueLength. Please refer to the code and screenshots below.

    Private queueLength As Integer = 3 Private queueSum As Double = 0 Private queueFull As Boolean = False Private idChange As String="" Dim queue As New System.Collections.Generic.Queue(Of Integer) Public Function CumulativeQueue(ByVal currentValue As Integer,id As String) As Object Dim removedValue As Double = 0 If idChange <> id then ClearQueue() idChange = id queueSum = 0 queueFull = False CumulativeQueue(currentValue,id) Else If queue.Count >= queueLength Then removedValue = queue.Dequeue() End If queueSum += currentValue queueSum -= removedValue queue.Enqueue(currentValue) If queue.Count < queueLength Then Return Nothing ElseIf queue.Count = queueLength And queueFull = False Then queueFull = True Return queueSum/queueLength Else Return queueSum/queueLength End If End If End Function

    public function ClearQueue() Dim i as Integer Dim n as Integer = Queue.Count-1 for i=n To 0 Step-1 queue.Dequeue() next i End function



    Applies to

    Microsoft SQL Server 2005
    Microsoft SQL Server 2008
    Microsoft SQL Server 2008 R2
    Microsoft SQL Server 2012

        

    Please click to vote if the post helps you. This can be beneficial to other community members reading the thread.

    2014년 5월 30일 금요일 오전 3:31