Need help with a Running Total Issue 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 &lt;= @ToPeriod) and [CostManagement_Commitments].ProjectId IN (@Projectlisting)AND 'COM' IN (@RecordTypes)









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 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 &lt;= 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 &lt;= CommitmentPeriodCost.PeriodId
    ) AS RunningTotal

FROM
    CommitmentPeriodCost

ORDER BY
    ProjectId
    , PeriodId