Working with parameters in SSRS 2008

Question

I have an (either or) situation in regards to parameters in SSRS 2008. I currently have my report working with a date range but I've been asked to add a drop down for the user to select the weekending date. I've got that drop down working but how can I switch between parameters (Date Range and the use of the Weekending Date drop down) for sending parameters to my report?

Answer 1

The way I allways fix this is by setting the parameters as nullable. Then in my sql script I select all dates on the weekending date or between the daterange: So whatever the user specifies, your sql script is filtered based on their parameters.

select *
from [table] t
   where t.[date] = @WeekendingDate 
      or t.[date] is between @DateRangeFrom and @DateRangeTo

Answer 2

I usually handle this situation by creating an Internal Parameter(s) to sit between the UI and the query or stored procedure. The Internal Parameters are driven by expression depending on the user selection.

so lets say you want the user to either select a begin and end date range(Begin: 2012-01-01 End: 2012-01-31), or a month (Jan 2012).

If they select a value for Month. I convert that to an equivalent date range in the internal parameter expression. If they enter a date range I just pass through the begin and end values to the internal parameters.

Hopefully this makes sense. with a little work and imagination I think the approach can handle most scenarios.

Answer 3

One possibility would be to use the version control system of your choice to make another branch for the second report, change that one to use Week Ending, and then just make sure you merge changes every time you make a change to the main report.

I'm sure someone will come up with a cleaner way to handle it, though...