Solved

12 Month running total with SSRS

Posted on 2014-03-31
4
1,235 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

896 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