I would like to display current month, year to date and previous year to date on ssrs

Question

Would like to calculate year to date and previous year to date current month on sql view so that to simply data presentation on ssrs to make it run faster. Is there a way to write a view which can perform this ?

Alan Schofield Alan Schofield · Accepted Answer · 2020-09-29T18:13:53

Ignoring the fact that I think you have some errors in your Previous YTD summary numbers..

I recreated the data as per your example

CREATE TABLE #t (TransDate date, Customer varchar(30), Amount float)

INSERT INTO #t VALUES
('2020-09-21', 'Customer 1', 200),
('2020-09-22', 'Customer 2', 300),
('2020-08-03', 'Customer 2', 450),
('2020-08-04', 'Customer 1', 1200),
('2019-09-14', 'Customer 1', 859),
('2019-02-05', 'Customer 2', 230),
('2019-07-26', 'Customer 2', 910),
('2019-11-17', 'Customer 1', 820)

Then the following statement will produce what you need. It is NOT the most elegant way of doing this but it will convert to a view easily and was all I could come up with in the time I had.

SELECT 
    m.Customer
    , m.MTD as [Current Month]
    , y.YTD as [Current YTD]
    , p.YTD as [Previous YTD]
FROM (
        SELECT Customer, Yr = Year(TransDate), Mn = MONTH(TransDate), MTD = SUM(Amount) FROM #t t WHERE MONTH(TransDate) = MONTH(GetDate()) and YEAR(TransDate) = YEAR(GetDate())
            GROUP by Customer, YEAR(TransDate), MONTH(TransDate)
    ) m 
    JOIN (SELECT Customer, Yr = YEAR(TransDate), YTD = SUM(Amount) FROM #t t  GROUP by Customer,  YEAR(TransDate)) y on m.Customer =y.Customer and m.Yr = y.Yr
    JOIN (SELECT Customer, Yr = YEAR(TransDate), YTD = SUM(Amount) FROM #t t  GROUP by Customer,  YEAR(TransDate)) p on y.Customer =p.Customer and y.Yr = p.Yr + 1

This gives the following results (which don;t match your example but I think your sample is incorrect)

I would like to display current month, year to date and previous year to date on ssrs

1 Answers