SSRS date range parameter

Question

I have a report in SSRS 2016 that contains one SP which has a date field called startdate. I'd like to create a parameter where the user can select between two ranges: startdate >='2017'or startdate < '2017'. This seems like it should be simple, but I can't see to find an easy way to do this.

I've tried to create a parameter in SSRS where I chose "Specify Values" and manually added these values but I get an error that the "expression references the parameter "startdate" which does not exist in the Parameters collection.

I'm sure I can create this by creating a stored procedure with these dates, but that seems like more work than is needed.

Does anyone know of a better way to do this?

I'm not quite sure what you mean by the user can select between two ranges: startdate >='2017-01-01' or startdate < '2017-01-01'. Are you wanting the user to choose two dates for the report, and filter the results where the start date is between the two? A query containing WHERE startdate >= '20170101' AND startdate < '20170101' would return no results, as something cannot be greater or equal to a value while being less than the same value. — Larnu
I want the user to have two options to choose from: startdate>='2017' would be the first option. The second option would be startdate < '2017'. — jackstraw22
What if you allow the user to select these ranges and pass a varchar instead of a date/datetime field? i.e. The drop down would offer two options 'Before' or 'After' and you could filter within the sproc based off of these values? That seems pretty straight forward. — StelioK
Add 2 parameters to your SSRS report, for example called DtStart and DtEnd. Then, in your where clause, use AND StartDate >= @DtStart AND StartDate < @dtEnd. — Larnu
@Steliok--can you be more specific on how to filter within the sproc based on the values? Right now my SPROC looks like: and Datefrom >='2015-01-01'. — jackstraw22

JesalynOlson JesalynOlson · Accepted Answer · 2017-12-11T18:14:37

If you are looking to have a parameter that has two options, 0 - Before 2017, and 1 - After 2017 then you should just create a Date parameter which has two options, Before 1/1/2017 in the label field with a 0 in the value field and After 1/1/17 in the label field with a 1 in the value field. Then in your report you just have to filter your data based upon the 1 or 0 value.

For example:

DECLARE @DateFilter INT = 1

    IF ( @DateFilter = 0 )
        BEGIN
            SELECT * FROM dbo.MyTable t
            WHERE t.DateFilter < '1/1/17'
        END

    IF ( @DateFilter = 1 )
        BEGIN
            SELECT * FROM dbo.MyTable t
            WHERE t.DateFilter >='1/1/17' 
        END

I don't know why you would filter your data this way. I recommend to use Larnu's suggestion that you have two parameters, a Start Date and an End Date. Otherwise this could return a lot of rows for the second option as time goes on.

SSRS date range parameter

2 Answers