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?
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:
answered Feb 08, 2010 at 01:44 AM
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, 2012 at 02:11 PM