Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Rolling totals in SQL Reporting Services SSRS

Posted on 2011-10-04
3
Medium Priority
?
423 Views
Last Modified: 2012-05-12
I have a rolling total sorted by most recent date:

DATE - VALUE - RUNNING TOTAL
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?
0
Comment
Question by:HECOJohnW
2 Comments
 
LVL 18

Accepted Solution

by:
chrismc earned 2000 total points
ID: 36962730
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;
     http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

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?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37144331
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

564 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