How to get previous values from 12 rows in ssrs

Question

I have a report in SSRS which displays the revenue for each month:

    | MO-YR  | Revenue|
    |01-2019 | $10.000|
    |02-2019 | $13.000|
    |03-2019 | $9.500 |
    |...     |...     |
    |...     |...     |
    |...     |...     |
    |10-2020 | $21.000|
    |11-2020 | $850   |

I have to do the 3 months average and 12 months average, so for example: For April of 2020, the 3 months average will be [(January 2020 Revenue) + (February 2020 Revenue) + (March 2020 Revenue)] / 3. The 12 months average will be the same, but it'll have 12 months added to each other and then divided by 12. My question is: how can I somehow get values of 3 previous rows and how can I get values from 12 previous rows in SSRS?

I'm adding also the code of my dataset source, stored procedure:

    SELECT
        *
        FROM 
            (SELECT
            pr.company_name AS [CompanyName],
            pr.sr_service_recid AS [Ticket],
            pr.unit_price AS [Invoice Amount],
            FORMAT(ih.Date_Invoice, 'MM-yyyy') AS [MO YR],
            FORMAT(EOMONTH(ih.Date_Invoice),'dd') AS [MO Days],
            ih.Date_Invoice AS [WorkDate]
            FROM
            Product pr
            LEFT JOIN InvoiceHeader ih
                ON 
                    ih.Billing_Log_RecID = pr.Billing_Log_RecID 
                    AND 
                    ih.Company_RecID = pr.company_recid
            LEFT JOIN SRService sr 
                        ON 
                            pr.sr_service_recid = sr.SR_Service_RecID
            LEFT JOIN vService vs 
                        ON 
                            vs.SR_Service_RecID = pr.sr_service_recid
            UNION ALL
            SELECT
                vt.company_name AS [CompanyName],
                vt.SR_Service_RecID AS [Ticket],
                vt.Billable_Hrs * CASE   
                    WHEN 
                        vt.Hourly_Rate < 25 
                    THEN 
                        0.00 
                    ELSE 
                        vt.Hourly_Rate 
                END [Invoice Amount],
                FORMAT(vt.Date_Start, 'MM-yyyy') AS [MO YR],
                FORMAT(EOMONTH(vt.Date_Start), 'dd') AS [MO Days],
                vt.Date_Start AS [WorkDate]
                FROM
                vTime vt
                LEFT JOIN SRService sr 
                    ON  
                        sr.SR_Service_RecID = vt.SR_Service_RecID 
                LEFT JOIN vService vs 
                    ON 
                        vs.SR_Service_RecID = vt.SR_Service_RecID
                LEFT JOIN Product pr 
                    ON 
                        pr.sr_service_recid = vt.SR_Service_RecID
                LEFT JOIN InvoiceHeader ih
                    ON 
                        vt.Invoice_Number = ih.Invoice_Number
                    )union_all
    ORDER BY [WorkDate]

Where is your data coming from? This is usually something you would do in your dataset definition. — iamdave
Can you change the stored procedure or the source of the dataset? — iamdave
Yes, I can do anything I want with it, but I was hoping that it can be done in srss — user9736017
This will be very hard to do within SSRS without killing performance. Can you add your procedure code to your question? — iamdave

Alan Schofield Alan Schofield · Accepted Answer · 2020-11-05T13:56:30

If you can do this in your dataset query then I suggest that's the best option.

To keep this answer simple I won't post the full code of the stored proc changes but it should be easy enough to do.

Step1: Add a new column to your stored proc that you can use to sort your data correctly.

FORMAT(vt.Date_Start, 'yyyyMM') AS [YrMoSort]

Step2: Dump the result of your stored proc into a temp table where we can work out the additional values required.

DECLARE @t TABLE ([MO-YR] varchar(7), [YrMoSort] varchar(6), Revenue float)

INSERT INTO @t
    EXEC yourStoredProcNameHere

SELECT 
    *,
     MT3 = SUM(Revenue) OVER(ORDER BY YrMoSort ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
     MT12 = SUM(Revenue) OVER(ORDER BY YrMoSort ROWS BETWEEN 11 PRECEDING AND CURRENT ROW),
    FROM @t

NOTE: This assumes that there are no gaps in your data. If there are then you will need to fill those gaps before you carry out the SUM() OVER() part as it only looks for previous rows and has no idea that YrMoSort contains date info.

How to get previous values from 12 rows in ssrs

1 Answers