SSRS Determining The Difference Between Adjoining Columns of Data in a Report

 0 I've built a matrix report showing the total sales of a given product with respect to the sales year and month which are the column fields of the report. The function aggregate sum is very powerful in determining the running total with respect to a given scope. However, my challenge is to find the difference between sequential and adjoining columns of data for a given scope. i.e. what is the change in total sales from month to month? I've searched endlessly for information regarding and believe that this seeming basic question has to have been answered before. Any ideas? more ▼ asked Feb 06, 2010 at 03:34 PM in Default Chris H 62 ● 1 ● 1 ● 2 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 I was obsessed in finding the answer to this question and I believe I've found it within the microsoft library for custom code. Taken directly; here's their explaination for a very similar type of question. Reference link text Use a custom code function to return the value for the expression. The following example returns the percentage difference between a current value and a previous value. This can be used to calculate the difference between any two successive values and it handles the edge case of the first comparison (when there is no previous value) and cases whether either the previous value or the current value is null (Nothing in Visual Basic). Public Function GetDeltaPercentage(ByVal PreviousValue, ByVal CurrentValue) As Object If IsNothing(PreviousValue) OR IsNothing(CurrentValue) Then Return Nothing Else if PreviousValue = 0 OR CurrentValue = 0 Then Return Nothing Else Return (CurrentValue - PreviousValue) / CurrentValue End If End Function The following expression shows how to call this custom code from a text box: =Code.GetDeltaPercentage(Previous(Sum(Fields!Sales.Value),"ColumnGroupByYear"), Sum(Fields!Sales.Value)) more ▼ answered Feb 08, 2010 at 01:44 AM Chris H 62 ● 1 ● 1 ● 2 Good find Chris, make sure you come back and mark your answer as accepted to get the rep you have worked for. Feb 08, 2010 at 06:25 AM Fatherjack ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 I have noticed the calculation of the public funtion is wrong! The Formular should be (CV-PV)/PV! more ▼ answered Apr 15, 2010 at 11:22 AM Rklaas 1 ● 1 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 I tried this function in a textbox within a column group and I got the error message "The use of a Previous aggregate function in a TablixCell within Tablix ‘Amounts’ is not supported." Any suggestions?Thanks. more ▼ answered May 17, 2012 at 02:11 PM brlipe53, You have to name the "ColumnGroupByYear" to whatever your column grouping name is. May 29, 2013 at 10:03 PM stg25 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x559
x154
x58

asked: Feb 06, 2010 at 03:34 PM

Seen: 9534 times

Last Updated: May 29, 2013 at 10:03 PM