SSRS Runningvalue totals per quarter

36 Views Asked by At

Currently I am trying to do a proof of concept for MTD sales and YTD sales and am using the ssrs expression RunningValue and I can get the YTD correct by month but can not get the total to work with the quarter column group in my matrix. It always ends up being the previous months total and not all months totaled like it is in the MTD column.

This picture is an example of what I get with the MTD totals and YTD being correct until it gets to the Q1 value

What I get

enter image description here

This is what the Q1 total should equal out to.

enter image description here

ColumnGroup3 is the month column that I am showing, and I've tried to set the running value scope to QT,DataSet1(name of dataset), and nothing.

=RunningValue(Fields!ExtendedPrice.Value, Sum, "QT") 

Along with that I have tried to hard code it but it always ends up being Septembers total with anything I try.

DECLARE @Fiscal INT = '2024';
                WITH Fiscal AS (
SELECT        MIN(StartDate) OVER (PARTITION BY FiscalYear) AS START, MAX(EndDate) OVER (PARTITION 
      BY FiscalYear) AS ENDa, AccountingPeriodID, StartDate, DATEPART(month,StartDate) AS MONTH, DATEPART(year, StartDate) AS YEAR
FROM bi.GLFiscalYearComposite
GROUP BY StartDate, FiscalYear, EndDate, AccountingPeriodID
HAVING        (FiscalYear = @Fiscal)),QUARTER AS(SELECT 
            CASE WHEN MONTH BETWEEN 7 AND 9 THEN 'Q1'
            WHEN MONTH BETWEEN 10 AND 12 THEN 'Q2' 
            WHEN MONTH BETWEEN 1 AND 3 THEN 'Q3'
            WHEN MONTH BETWEEN 4 AND 6 THEN 'Q4'
            ELSE NULL END AS QT,
            CASE WHEN MONTH BETWEEN 7 AND 9 THEN 21000
            WHEN MONTH BETWEEN 10 AND 12 THEN 73500
            WHEN MONTH BETWEEN 1 AND 3 THEN 381500
            WHEN MONTH BETWEEN 4 AND 6 THEN 357000
            ELSE NULL END AS YTDQTGOAL
            ,GLBATCH.AccountingPeriodID
       FROM Fiscal INNER JOIN
            GLBATCH ON Fiscal.AccountingPeriodID = GLBATCH.AccountingPeriodID INNER JOIN
            CUSORDER ON GLBATCH.GLBatchID = CUSORDER.OrderBatchID)
SELECT  QT,MONTH,SUM(CUSORDER.ExtendedPrice) OVER(PARTITION BY MONTH ORDER BY QT) AS RUNNING_TOTAL
FROM GLBATCH INNER JOIN
                Fiscal ON GLBATCH.ACcountingPeriodID = Fiscal.AccountingPeriodID INNER JOIN
                QUARTER ON GLBATCH.ACcountingPeriodID = QUARTER.AccountingPeriodID  INNER JOIN
                CUSORDER  ON GLBATCH.GLBatchID = CUSORDER.OrderBatchID 
GROUP BY ExtendedPrice,QT,START,todate,YEAR,SNUM,MONTH
HAVING  CUSORDER.date > START
ORDER BY QT

So I've tried

SUM(ExtendedPrice) OVER(PARTITION BY MONTH ORDER BY QT) AS RUNNING_TOTAL

AND

SUM(ExtendedPrice) OVER (partition BY YEAR,MONTH
     ORDER BY QT ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL

If anyone has any idea of a way to do this within the matrix or a better way to run this code I would appreciate any help!

0

There are 0 best solutions below