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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics