question

Chris H avatar image
Chris H asked

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?

t-sqlssrsmeta-asksscfunctions
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Chris H avatar image
Chris H answered

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rklaas avatar image
Rklaas answered

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

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

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

brlipe53 avatar image
brlipe53 answered
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.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

stg25 avatar image stg25 commented ·
brlipe53, You have to name the "ColumnGroupByYear" to whatever your column grouping name is.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.