SSRS Date Parameter Requirements and DateDiff

Question

In my SSRS report, I have a start date and an end date as two of my parameters. I have others as well, so the start and end date can be NULL. However, I would like to make sure that if the start date parameter is selected, then the end date parameter is also selected. Also, I would like to make sure that the start date is earlier than the end date (for obvious reasons).

Are these two things something I can do in SSRS or will I need to use SQL - or both?

I'm using T-SQL Server 2005 and SSRS 2005. Thanks!

Jamie F Jamie F · Accepted Answer · 2011-10-14T20:26:13

You can certainly make these checks in SSRS without needing to call to SQL. But giving feedback to the user is the challenge. You can't dynamically change the user input section of Report Manager.

What I have done to address almost identical requirements: Leave the default value for the date parameters as NULL. Then write your checks (in VB) to show a text box in the body of the report with a warning message ("Incorrect Parameters: you must...") if the check fails. The inverse should be added to any data section, so that it is hidden if invalid data is returned. If there are no visible references to a data set, then the query isn't executed.

Setting the visibility property of a text box to:

=IIF(Parameters!ShowInactive.Value = "Yes"
    and  (ISNothing(Parameters!InactiveFrom.Value)
       or Isnothing(Parameters!InactiveTo.Value)),
  false,
  true)

will show the text box if the @InactiveParameter is set to "Yes" and either the @InactiveFrom or @InactiveTo parameters are not set.

SSRS Date Parameter Requirements and DateDiff

1 Answers