YTD Not working

  • Hi All

    Please see the below screen shot, YTD column is nit working. Showing the same as complete year values.

    =(Sum(Fields!Accounts_Receivable_Application_Patient_Payment_Amount.Value) - Sum(Fields!Accounts_Receivable_Unapplied_Patient_Payment_Due_To_Refund_Amount.Value))

    + Sum(Fields!Accounts_Receivable_Application_Insurance_Payment_Amount.Value)

    + Sum(Fields!Accounts_Receivable_Application_Insurance_Recoup_Amount.Value)

     

    Thanks

    • This topic was modified 1 month ago by  SSCNewbee.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • I haven't touched SSRS in forever  (looks in book). You *might* be able to use RunningValue(), but I would do this in T-SQL as it's much easier to do there. You can use a Windowing function in T-SQL to do it, like this:

    USE Tempdb;
    go

    /* Create a table to hold the data (should be indexed!) */CREATE TABLE Teest(ID CHAR(2), SaleDate DATE, Qty INT);
    GO
    /* add records, so the good folks here don't have to spend
    their time setting up your problem. */
    INSERT INTO Teest VALUES
    ('S1','01/01/2015', 5)
    ,('S1','02/01/2015', 5)
    ,('S1','03/01/2015', 5)
    ,('S1','04/01/2015', 5)
    ,('S1','05/01/2015', 5)
    ,('S1','06/01/2015', 5)
    ,('S1','07/01/2015', 5)
    ,('S1','08/01/2015', 5)
    ,('S1','09/01/2015', 5)
    ,('S1','10/01/2015', 5)
    ,('S1','11/01/2015', 5)
    ,('S1','12/01/2015', 5)
    ,('S1','01/01/2016', 5)
    ,('S2','01/01/2015', 10)
    ,('S2','02/01/2015', 10)
    ,('S2','03/01/2015', 10)
    ,('S2','04/01/2015', 10)
    ,('S2','05/01/2015', 10)
    ,('S2','06/01/2015', 10)
    ,('S2','07/01/2015', 10)
    ,('S2','08/01/2015', 10)
    ,('S2','09/01/2015', 10)
    ,('S2','10/01/2015', 10)
    ,('S2','11/01/2015', 10)
    ,('S2','12/01/2015', 10)
    ,('S2','01/01/2016',10);

    /* then the answer is easy... using a windowing function to
    create a running total -- much more flexible than SSRS */
    SELECT ID, SaleDate, Qty,
    RT = SUM(Qty) OVER (PARTITION BY ID
    ORDER BY SaleDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW)
    FROM Teest
    ORDER BY ID, SaleDate;

    having said that, it's generally good manners/practice to provide consumable data (basically a create table script... and anything required to imitate your situation).  Jeff Moden covered all this in his article, which you owe it to yourself to read. I try to follow it every time I post, and I have a post on here where I followed his instructions and half way through posting (or maybe after... it was a long time ago), I re-read my code and found the mistake. So it's a great learning tool.

    Otherwise, I think you can use RunningValue() function in SSRS:

    RunningValue function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn

    • This reply was modified 1 month ago by  pietlinden.
  • Thanks a lot. That helped and I will definitely will be adhering to what you said.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply