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

• 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.

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.

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