Search code examples
sqlreporting-servicesparametersssrs-2008

Pick which value to feed in second parameter in SSRS


I have a report with two date: ProductCreatedDate, ProductSoldDate. User need to be able to filter either on createdDate or SoldDate not both. How can I do this? I am thinking of creating three parameters. First one will give user an option to select which date they want to filter on. For example:

First Parameter-
Pick Date type:
Product Created Date
Product Sold Date
Second parameter based on first parameter value-
Start Date (calendar form)
Third parameter based on first parameter value-
End Date (Calendar form)

Let's say user selects value Product Created Date as a first parameter

Then second and third (start and end date calendar) parameter should be based on ProductCreatedDate filter

Any suggestion, on how to do this?

What I have tried hoping it will work:

WHERE (CASE WHEN @Type = 'Created' THEN ProductCreatedDate BETWEEN @StartDate AND @EndDate END)
OR (CASE WHEN @Type = 'Sold' THEN ProductSoldDate BETWEEN @StartDate AND @EndDate END)

Solution

  • if you have 3 parameters (DateType, StartDate, EndDate) you can just add some ANDS and ORS in your WHERE.

    WHERE (@DateType = 'Created' AND ProductCreatedDate BETWEEN @StartDate AND @EndDate)
          OR (@DateType = 'Sold' AND ProductSoldDate BETWEEN @StartDate AND @EndDate)