YTD Not working

Question

Post reply

YTD Not working

SSCNewbee

Right there with Babe

Points: 788
More actions
June 18, 2024 at 2:13 pm

#4429431
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 5 months ago by SSCNewbee.

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

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

pietlinden SSC Guru Points: 63503 More actions · Answer 2

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 5 months, 1 week ago by pietlinden.

SSCNewbee Right there with Babe Points: 788 More actions · Answer 3

Thanks a lot. That helped and I will definitely will be adhering to what you said.