none
How to get the difference between two columns in a column group

    Question

  • Hi All,

    My first time here and really new to programming. I would like to get the difference between 2 columns that are inside 

    a column group.

    Here is my sample table below: The Column Group is PeriodNumber and can only choose 2. like 1 and 2.. I would like to have a third row which will simply calculate the difference between the amounts in PeriodNumber 1 and 2.

                                PeriodNumber          

    Account                    1                            2     

    1) Cash                10,000                15,000

    2) Receivables      12,000                11,500

    3) Equipment          5,000                  5,500

    Total Assets          27,000                32,000

    Monday, November 18, 2013 7:56 AM

Answers

  • Hi yabgestopa,

    From your description, you want to get the difference between two columns in a column group. After testing it in my environment, we can use custom code to achieve your requirement. For more details, you can refer to the following steps:

    1. Copy the custom code below and paste it to your report. (Right-click report>Report Properties>Code)
      Dim Shared Num1 As Integer
      Dim shared Num2 As Integer
      Public Function GetAmount(Amount as Integer, Type as String)
      If Type = "1" Then
      Num1=Amount
      Else
      Num2=Amount
      End If
      Return Amount
      End Function
      Public Function GetDif()
      Return Num1-Num2
      End function
    2. Right-click the second column to insert a third column with Outside Group-Right.
    3. Then use the expressions below in the matrix.
      =Code.GetAmount(Fields!Amount.Value,Fields!PeriodNumber.Value)
      =code.GetAmount(Sum(Fields!Amount.Value),Fields!PeriodNumber.Value)
      =Code.GetDif()

     

    The report looks like below.
     
    If you have any questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, November 19, 2013 2:56 AM

All replies

  • Hi yabgestopa,

    From your description, you want to get the difference between two columns in a column group. After testing it in my environment, we can use custom code to achieve your requirement. For more details, you can refer to the following steps:

    1. Copy the custom code below and paste it to your report. (Right-click report>Report Properties>Code)
      Dim Shared Num1 As Integer
      Dim shared Num2 As Integer
      Public Function GetAmount(Amount as Integer, Type as String)
      If Type = "1" Then
      Num1=Amount
      Else
      Num2=Amount
      End If
      Return Amount
      End Function
      Public Function GetDif()
      Return Num1-Num2
      End function
    2. Right-click the second column to insert a third column with Outside Group-Right.
    3. Then use the expressions below in the matrix.
      =Code.GetAmount(Fields!Amount.Value,Fields!PeriodNumber.Value)
      =code.GetAmount(Sum(Fields!Amount.Value),Fields!PeriodNumber.Value)
      =Code.GetDif()

     

    The report looks like below.
     
    If you have any questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, November 19, 2013 2:56 AM
  • Hello Katherine,

    I have tried your solution and it works for the given data sample. Thank you!

    However I encountered a problem if for example the amount for Equipment for Period 1 is 0.

    The variance calculated is 12,000-5,500 = 6,500. For some reason my code or (SSRS?) doesnt like it when Period 1 value is 0.

    Are you able to provide some insight?

    I have only slightly modified your code to match my data:

    The code is:

    Dim Shared Num1 As Double
    Dim shared Num2 As Double
    Public Function GetAmount(Amount as Double, Type as String)
    If Type = "159" Then
    Num1=Amount
    Else
    Num2=Amount
    End If
    Return Amount
    End Function
    Public Function GetDif()
    Return Num2-Num1
    End function

    The expression is:

    =Code.GetAmount(sum(Fields!EFTSL.Value),Fields!Scenario.Value)

    Note:

    I need to use sum in the above expression to get the value for each of my row items as an item can have multiple entries.

    My Column group is Scenario.

    Thank you

    Daniel

    Saturday, January 11, 2014 4:00 PM