Using SQL-Server 2008 R2 and Report Builder 3.0

Here is my query without the running total column:

  ,CostManagement_CommitmentDetails.LineNumber AS LineNumber
  ,Periods.Id AS [Periods Id]
  ,Periods.LineNumber AS [Periods LineNumber]

,'COM' as RecordType

,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.[CostCodeId], 1) AS Group1
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 1) AS Group1Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 2) AS Group2
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 2) AS Group2Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 3) AS Group3
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 3) AS Group3Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 4) AS Group4
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 4) AS Group4Description
    ,dbo.[GetCostCodeDetailNumber](CostManagement_CommitmentDetails.CostCodeId, 5) AS Group5
    ,dbo.[GetCostCodeDetailNumberWithDescription](CostManagement_CommitmentDetails.CostCodeId, 5) AS Group5Description

    ON CostManagement_CommitmentDetails.PeriodId = Periods.Id
    ON [CostManagement_CommitmentDetails].CostCodeId = [CostCodes].Id
LEFT OUTER JOIN [CostManagement_Commitments]
    ON CostManagement_Commitments.Id = [CostManagement_CommitmentDetails].CommitmentId
    ON [Projects].Id = [CostManagement_Commitments].ProjectId

Where (Period <= @ToPeriod) and [CostManagement_Commitments].ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)

Dataset produced from Query

Dataset Graph in Report Dataset Matrix in Report Dataset Graph in Report using subset date range Dataset Matrix in Report using subset date range

Running totals work fine if my dataset is from the start date to end date for both the Graph and Matrix. I run into trouble when I want to show just a subset of all the dates say a 3 month window. The Matrix works fine in keeping the running total values from the start of the project even though those months are not displayed in the Matrix, but the Graph sets the Running totals (cumulative) to zero which displays incorrect totals. The Matrix has all the correct totals, the Graph does not. Category in the Graph does not have a visibility option. Looking for a solution either Graph wise or by adding a running cumulative total column that I can then graph because of the current graph limitations for Category.

If you look at the last Graph is has 2.4 million for the cumulative total for the yellow line but it should be 5.5 million for period 07-1403


We are restricted from creating any Stored Procedures. The Bars in the Graph are Period/Month totals only. The Line in the Graph is the cumulative running total.

All help is greatly appreciated.

  • You say you are unable to create any stored procedures, does this also mean you cannot modify your underlying query?
    – mhep
    Oct 18, 2014 at 0:24
  • we can create the query inside the report using Report Builder 3.0 to create the dataset. When I try to use ROWS with the Over Clause and either Partition or Order By it does not like the ROWS. If I can use the Over Clause with Order By Rows Preceding and Current Row that would resolve everything. Just looking for a solution that works inside Report Builder 3.0.
    – zac
    Oct 19, 2014 at 14:52
  • Your syntax may be incorrect, the function is ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) not ROWS. SQL 2008 R2 does not support ROWS PRECEDING but there are ways to achieve the same result without it.
    – mhep
    Oct 19, 2014 at 18:29
  • I did not know that SQL 2008 R2 does not support ROWS
    – zac
    Oct 20, 2014 at 2:47
  • 1
    Here is a great post by @AaronBertrand on the subject. sqlperformance.com/2012/07/t-sql-queries/running-totals Essentially it boiled down to using a cursor (or loop of your choice). Lots of detail and proof in the post though. Oct 21, 2014 at 14:31

1 Answer 1


You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column.

Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER function so you get a running total for the different projects/record types.

If you are able to post some sample data I can provide you with an example using your data structures.

WITH ContactMonthOrdered AS
        ,   COUNT(*) AS Total
        ,   ROW_NUMBER() OVER(ORDER BY ContactMonthStartDate) AS MonthOrder


        ContactFiscalYearLongName = '2014/2015'


    ,   Total
    ,   MonthOrder
    ,   (
            SELECT  SUM(Total)
            FROM    ContactMonthOrdered
            WHERE   MonthOrder <= c.MonthOrder
        ) AS RunningTotal

    ContactMonthOrdered AS c


UPDATED The query below is derived from the example query you posted in your question. Provided I understand your schema correctly, this will give you the total for each period for a project and also a running total across the periods for a project. The assumption is that the sequence of PeriodId is meaningful.

;WITH CommitmentPeriodCost AS
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

        CostManagement_Commitments AS Commitments
        LEFT JOIN CostManagement_CommitmentDetails AS CommitmentDetails
            ON  Commitments.Id = CommitmentDetails.CommitmentId

        Commitments.ProjectId = 1

        , CommitmentDetails.PeriodId

    , PeriodId
    , TotalCost
    , (
            SELECT  SUM(TotalCost)
            FROM    CommitmentPeriodCost AS RunningCost
            WHERE   ProjectId = CommitmentPeriodCost.ProjectId
                    AND PeriodId <= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal


    , PeriodId
  • SELECT Distinct ProjectId ,PeriodId ,SUM(TotalCost) Over (Partition By PeriodId) AS [Running Total] FROM CostManagement_CommitmentDetails LEFT OUTER JOIN CostManagement_Commitments ON CostManagement_CommitmentDetails.CommitmentId = CostManagement_Commitments.Id Where ProjectId = 1 Group By ProjectId,PeriodId, TotalCost
    – zac
    Oct 22, 2014 at 15:06
  • This is just a test to try to get the running totals not just for the month/periodid but I need another column that rolls up from month/periodid so month 1 = month 1, month 2 = month 1 + 2, month 3 = 1 + 2 + 3.....etc
    – zac
    Oct 22, 2014 at 15:11
  • off topic....how can you add an image to the add comments?
    – zac
    Oct 22, 2014 at 15:13
  • What you are asking for sounds like the same thing as what I provided in my solution. I have posted another query using your table/column name. In the example query you posted in the comment above why are you GROUPING on TotalCost? Also why are you using a DISTINCT after GROUPING the data? Without some sample data it is difficult to fully understand your question.
    – mhep
    Oct 22, 2014 at 16:38
  • The ProjectId field comes from another table which is linked via a LEFT OUTER JOIN CostManagement_Commitments ON CostManagement_CommitmentDetails.CommitmentId = CostManagement_Commitments.Id
    – zac
    Oct 22, 2014 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.