Solved

12 Month running total with SSRS

Posted on 2014-03-31
4
1,311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
performance query 4 43
How to target task fields of 'To-Do' task item in SharePoint 2013? 3 40
Expanding/Collapsing Tree on SharePoint List 29 62
T-SQL Query 9 34
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 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