12 Month running total with SSRS

My manager wishes to see a 12 month running total on a ssrs chart. I see the running average on the data series option but do not see a running total (or 12 month running total for that matter). Can this be added? We use SQL 2012 with SQl Server Data Tools 2013.

Also, the data exist in a sharepoint list so if there is a way to add a calculated column on the list that shows 12 month rolling sum, then that would work as well

Thanks
jsgrosskopfIS ManagerAsked:
Who is Participating?
 
jsgrosskopfIS ManagerAuthor Commented:
Couldn't get it to work. Thankfully, my manager decided to use the moving average that was built in so there is no need for it now. Thanks
0
 
planoczCommented:
If you are grouping by months you could try this in the Code tab of your report.

This goes in your code tab area..

 Public Dim OldNum As String = Nothing
  Public Function RunningTotal(ByVal BatchNum As Integer,ByVal BatchPdAmt As Decimal) As Object
        Static BatchTotal As Decimal
        If BatchNum <> OldNum Then
            BatchTotal += BatchPdAmt
        End If
        OldNum = BatchNum
        Return BatchTotal
    End Function

This goes into total textbox area...
=Code.RunningTotal(Fields!BatchNum.value, Fields!BatchPdAmt.value)

This  sample I use for batch groups, So you will needt to change it to Months groups and amounts.
0
 
Nico BontenbalCommented:
I haven't tested it but I think you can do this by using the RunningValue function:
http://technet.microsoft.com/en-us/library/ms159136(v=sql.100).aspx
Use this in the values expression of your chart.
0
 
jsgrosskopfIS ManagerAuthor Commented:
Decided not to use it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.