Rolling totals in SQL Reporting Services SSRS

Posted on 2011-10-04
Last Modified: 2012-05-12
I have a rolling total sorted by most recent date:

10/1/2011 - 5 - 5
10/3/2011 - 4 - 9
10/5/2011 - 10 - 19
10/7/2011 -  5 - 24

I have criteria already setup that if my running total exceeds 10, it highlights the row.  In my example above, the date 10/5/2011 is when I would be over 10.

I'm using RunningValue to do the Sum and that is working fine, but how can I identify the value of 10/5/2011 in my example and display that in the parent group?
Question by:HECOJohnW
    LVL 18

    Accepted Solution

    This is best done in the SQL query returning the results.

    Create a temporary table to hold the values you've listed above, include a blank column to hold the "threshold date".
    Here is a link on running totals in SQL;

    Find the threshold date using something similar to;

        Declare @Threshold DateTime
        Select @Threshold = Min(Date) From @TempTable Where RunningTotal >= 10
        Update @TempTable
            Set ThresholdCrossed = @Threshold

    This will place the date the threshold was crossed onto every record. Making it available to your parent group just by using the Threshold field from the query.

    Hope that helps?
    LVL 100

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction Earlier I wrote an article about the new lookup functions ( 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…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now