Is there a way to choose functions for dates when subscribing to an SSRS report?

Question

I have a report that has a 'StartDate' and 'EndDate' parameter, with no default values. When subscribing to this report, the user chooses the dates that they would like to see. What I want to do is have options for them to choose, such as 'Today', 'Yesterday', 'Last Week' so that they don't have to create new subscriptions every time they change the date. i.e. if they chose 'Today' for both 'StartDate' and 'EndDate', and subscribed to receive this report every day at 9pm, then the report they received would be showing the data from the day that it arrived, not the day that they set up the subscription. Is this possible?

I have tried a few methods that I have seen on Stack Overflow, however every method I try results in 'StartDate' and 'EndDate' being given a value of a date, e.g. 10/08/2023, rather than a continually changing date based on the date the report is sent out etc... From then on the report is always showing data from 10/08/2023, but I would like the date to change automatically.

Answer 1

I'm not sure how you are passing in the dates (filters or as part of a where clause) but this example shows how to do it using a WHERE clause.

This simply selects from my date table for simplicity but the data itself could obviously be anything as long as it has a real Date or DateTime column.

First I created a report and added a parameter called DateRange. I then added some fixed available values, "Today", "Yesterday" and "Last Week".

My dataset query takes the passed in parameter DateRange value (no need to declare this in your dataset query), then calculates the start and end dates, saves then in variables and then queries the main data table returning only values between those dates.

Here is the query

DECLARE @StartDate date 
DECLARE @EndDate date 

SET @StartDate = CASE @DateRange 
                    WHEN 'Today' THEN CAST(GetDate() AS DATE)
                    WHEN 'Yesterday' THEN CAST(DATEADD(d, -1, GetDate()) as DATE)
                    WHEN 'Last Week' THEN CAST(DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) as DATE)
                 END

SET @EndDate = CASE @DateRange 
                    WHEN 'Today' THEN CAST(GetDate() AS DATE)
                    WHEN 'Yesterday' THEN CAST(DATEADD(d, -1, GetDate()) as DATE)
                    WHEN 'Last Week' THEN CAST(DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 4) as DATE)
                 END

SELECT *
from Config.DatesTable 
WHERE TheDate BETWEEN @StartDate AND @EndDate

When we run the report we get the following results depending on the selection.