3

Using SQL-Server 2008 R2 and Report Builder 3.0

Here is my query without the running total column:

SELECT
   CostManagement_CommitmentDetails.Id
  ,CostManagement_CommitmentDetails.LineNumber AS LineNumber
  ,CostManagement_CommitmentDetails.Description
  ,CostManagement_CommitmentDetails.CostCodeId
  ,CostManagement_CommitmentDetails.TotalCost
  ,CostManagement_CommitmentDetails.PeriodId
  ,Periods.Id AS [Periods Id]
  ,Periods.LineNumber AS [Periods LineNumber]
  ,Periods.Period
  ,Periods.FromDate
  ,Periods.ToDate
  ,Projects.ProjectName
  ,[CostManagement_Commitments].ProjectId

,'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



FROM
  CostManagement_CommitmentDetails
  LEFT OUTER JOIN Periods
    ON CostManagement_CommitmentDetails.PeriodId = Periods.Id
LEFT OUTER JOIN [CostCodes]
    ON [CostManagement_CommitmentDetails].CostCodeId = [CostCodes].Id
LEFT OUTER JOIN [CostManagement_Commitments]
    ON CostManagement_Commitments.Id = [CostManagement_CommitmentDetails].CommitmentId
LEFT OUTER JOIN [Projects]
    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

Note:

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? – mheptinstall Oct 18 '14 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 '14 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. – mheptinstall Oct 19 '14 at 18:29
  • I did not know that SQL 2008 R2 does not support ROWS – zac Oct 20 '14 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. – Kenneth Fisher Oct 21 '14 at 14:31
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
(
    SELECT
            ContactMonthStartDate
        ,   COUNT(*) AS Total
        ,   ROW_NUMBER() OVER(ORDER BY ContactMonthStartDate) AS MonthOrder

    FROM
        Clinical.ContactDetail

    WHERE
        ContactFiscalYearLongName = '2014/2015'

    GROUP BY
        ContactMonthStartDate
)

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

FROM
    ContactMonthOrdered AS c

ORDER BY
    c.MonthOrder

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
(
    SELECT
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
        , SUM(CommitmentDetails.TotalCost) AS TotalCost

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

    WHERE
        Commitments.ProjectId = 1

    GROUP BY
        Commitments.ProjectId
        , CommitmentDetails.PeriodId
)

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

FROM
    CommitmentPeriodCost

ORDER BY
    ProjectId
    , 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 '14 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 '14 at 15:11
  • off topic....how can you add an image to the add comments? – zac Oct 22 '14 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. – mheptinstall Oct 22 '14 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 '14 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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