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

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 gravatar image

Chris H
62 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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 gravatar image

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 ♦♦
(comments are locked)
10|1200 characters needed characters left

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 gravatar image

1 1

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered May 17, 2012 at 02:11 PM

brlipe53 gravatar image


brlipe53, You have to name the "ColumnGroupByYear" to whatever your column grouping name is.
May 29, 2013 at 10:03 PM stg25
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

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



asked: Feb 06, 2010 at 03:34 PM

Seen: 9453 times

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