Solved

12 Month running total with SSRS

Posted on 2014-03-31
4
1,221 Views
Last Modified: 2014-04-07
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
0
Comment
Question by:jsgrosskopf
  • 2
4 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 39969302
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39971446
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
 

Accepted Solution

by:
jsgrosskopf earned 0 total points
ID: 39973051
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
 

Author Closing Comment

by:jsgrosskopf
ID: 39982578
Decided not to use it
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now