SSRS Matrix Difference between Columns
Hi
I'm trying to calculate the difference between to columns in a matrix. Unfortunately the PREVIOUS Function seems only to work with calculating the difference between rows and not columns...
Is there a way to get this delta value?
(I know I could tanspose the columns to rows and then it would work... but unfortunately the design is not suitable in this instance...)
Design:
Month aa Month bb Month cc
Service xx Amount Amount, Delta Amount, Delta
Service yy Amount Amount, Delta Amount, Delta
Service zz Amount Amount, Delta Amount, Delta
(eg: Delta = Difference Month aa - Month bb)
any help is appreciated... Thx...
October 31st, 2011 10:18am
Hi
Lukas.BIZ,
Thanks for posting your question here. Based on my research, there is no one direct way helps
us achieve this requirement. I would suggest you using custom code, detail steps as below.
1. Add below custom code in your report.
code snippet:
Public cvalue as Integer
Public incvalue as Integer
Public counts as Integer
Public rownumbers as Integer
Public function GetMaxValue(Byval rownumber as Integer,Byval value as Integer)
incvalue = value-incvalue
IF rownumber>rownumbers Then
IF counts =0
cvalue =0
else
cvalue = incvalue
end IF
else
counts=0
cvalue =0
end IF
rownumbers=rownumber
counts=counts + 1
return cvalue
End function
2.
Right-click the column group column, select Insert Column, Inside Group right.
Type in the textbox with this
expression:
=Code.GetMaxValue(RunningValue(Fields!
Month.Value,CountDistinct,"RowGroupName"),Sum(Fields!Service.Value))
The effect just like this:
Hope it help you. If there is anything unclear, please feel free to ask.
Thanks,
Sharp Wang
Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2011 12:24am
Hi
Lukas.BIZ,
Thanks for posting your question here. Based on my research, there is no one direct way helps
us achieve this requirement. I would suggest you using custom code, detail steps as below.
1. Add below custom code in your report.
code snippet:
Public cvalue as Integer
Public incvalue as Integer
Public counts as Integer
Public rownumbers as Integer
Public function GetMaxValue(Byval rownumber as Integer,Byval value as Integer)
incvalue = value-incvalue
IF rownumber>rownumbers Then
IF counts =0
cvalue =0
else
cvalue = incvalue
end IF
else
counts=0
cvalue =0
end IF
rownumbers=rownumber
counts=counts + 1
return cvalue
End function
2.
Right-click the column group column, select Insert Column, Inside Group right.
Type in the textbox with this
expression:
=Code.GetMaxValue(RunningValue(Fields!
Month.Value,CountDistinct,"RowGroupName"),Sum(Fields!Service.Value))
The effect just like this:
Hope it help you. If there is anything unclear, please feel free to ask.
Thanks,
Sharp Wang
Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
November 2nd, 2011 7:20am
Fantastic!
Your custom code and the expression helped me to get the needed results!
I first tried your expression and became a working solution, but not exactly the expected results...
The Delta column showed me the cummulated delta after each step
January February
10 12 Delta = 2
7 7 Delta = 2
15 14 Delta = 1
So the delta was callculated, but somehow aggregated together... So I tried to implement it with the PREVIOUS() function, to get the Delta from the Delta from each Row...
=(Code.GetMaxValue(RunningValue(Fields!Month.Value,CountDistinct,"Services"),(Fields!Amount.Value)-Previous(FIRST(Fields!Amount.Value))))
And This Worked perfectly :-)
Delta Colum Results:
2
0
-1
Thanks a lot! Hopefully there will be a "Standard" functionality for this in the next Version of SSRS... :-)
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2011 11:50am
Hey Sharp Wang
Unfortunaltely the solution didnt work out, over long time... as I got the data, for three months, there turned out to be a problem in the algorithm... (two worked just fine, but three didnt work out...)
I checked the sales territory matrix from your post once again, and saw that there is a little difference from what I'm looking for!
As It seems the "Warehouse" Difference Column Values in your Matrix are built from "Value Added Reseller" Difference + "Warehouse" Amount...
What I'm looking for, is the real difference... That means "Amount B" - "Amount A"... not "Amount B" - "Difference A"
Eg:
I've tryed to adjust your Custom Code, but I'm not sure if the algorithm with the "rownumber" is the real row Number, or in fact the "column nuber" that the Running Value in the Matrix loads in to the function...
Could you please help me again, with the algorithm and the custom code?
Thanks!
December 1st, 2011 9:24am