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?

asked Feb 06 '10 at 03:34 PM in Default

Chris H
62 ● 1 ● 1 ● 2

(comments are locked)

3 answers:

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))

answered Feb 08 '10 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 '10 at 06:25 AM Fatherjack ♦♦

(comments are locked)

I have noticed the calculation of the public funtion is wrong!

The Formular should be (CV-PV)/PV!

answered Apr 15 '10 at 11:22 AM

Rklaas
1 ● 1

(comments are locked)

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.

answered May 17 '12 at 02:11 PM