Ssrs date parameters

Is there a way if you have two date parameters, one is start date and the other is end date, to make sure that the end date is after the start date ?
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
Megan BrooksConnect With a Mentor SQL Server ConsultantCommented:
You're welcome. It became a personal cause. :-)

I think I prefer using the NoRows message to report the error, rather than the exception message. No matter what I try to do with the exception, it is ugly and somewhat confusing (but I thought it was a neat trick).

I don't think it matters much that the report has to execute in order to display the NoRows message, because when the parameters are bad the dataset is short circuited to return zero rows, and it should execute quickly.

To summarize using NoRows:
  1. Code the main dataset to check for an invalid date range (and/or other parameter value issues), and have it return zero rows if anything is not right. Complex queries may need additional tweaking to ensure that subqueries don't execute anyway and cause errors. In my example, I simply added "WHERE @End >= @Start" to the query.
  2. Define a NoRows message for the outermost tablix of the report (since any inner tablixes will not show if there are no rows at the outermost level). If there is more than one tablix at the top level, or if there are charts, you will have to decide what to do.
  3. Make the NoRows message conditional depending on whether or not the report parameters are valid, so as to distinguish between a no-rows condition where there simply is no data and one caused by invalid parameters as determined in step 1, above. The 'normal' message can be an empty string, but  it is a good practice, generally, to include a message rather than show a blank page. In my example RDL file, the NoRows message expression is:
=IIf(Parameters!End.Value >= Parameters!Start.Value, "No data rows were found", "Start date parameter is greater than End date parameter")

You can style the NoRows message using the other properties of the NoRows section of the tablix property sheet.
Megan BrooksSQL Server ConsultantCommented:
You don't have direct access to parameter values while they are being entered. You can specify an available values list, and then only those values will be allowed. The list can come from a query that depends upon an earlier parameter.

As I have noted elsewhere, recently, the things you can do with the built-in parameter UI in ReportViewer are limited, and you frequently have to make compromises. For this issue, my usual compromise is to just make sure that no results are returned if the dates go the wrong way. This can trigger a NoRows message from a data region. I don't remember off the top of my head if that property (of the data region) can be dynamic, to display a different message if the date parameters are in the wrong order. I don't think so, but I'll check (never tried it).

 It would also be possible, using visibility flags, to display a textbox in place of the normal report output if the date parameters are reversed. Another compromise would be to swap the values in the code that references them, if the first date is larger, but that could be quite confusing to the user.

My general answer to this type of question is that if you write your own parameter UI, customized for your application, and do it well, you won't have to deal with the limitations of the built-in one and all the user complaints that follow.
zephyr_hex (Megan)DeveloperCommented:
You can add the following validation:

function ValidParams(byval startdate as datetime, byval enddate as datetime) as boolean
if enddate < startdate then
return FALSE
return TRUE
end if
end function

Open in new window

Based on:

Add that code to the Report Properties Code tab.
To implement it, add a new hidden parameter with the following default value:
=code.ValidParams(Parameters!startdate.Value, Parameters!enddate.Value)

Open in new window

Then change your original query so that it only runs when the validation passes:

=IIF(Parameters!IsValidParam.Value, "Your SQL Query")

Open in new window

You may want to then add a message to your report that displays when Parameters!IsValidParam.Value is false, so the user knows why they aren't getting any results.
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Megan BrooksSQL Server ConsultantCommented:
I usually just code the SQL-level validation check into the SQL query using query parameters (as opposed to report parameters). But that should work. I don't much care for dynamic SQL in dataset queries, unless it can't be done another way. (such as when reporting off a star schema using a parameter-driven code generator).

You might have given me an idea, though, for another approach to intercepting and validating parameter values while the UI is active. I am not terribly optimistic that it will work, but I need to go take a walk and think about it...
Megan BrooksSQL Server ConsultantCommented:
The idea I had might actually work. When I fired up VS 2015 to try it, however, it crashed for the umpteenth time. I am now installing an update that came out last Friday and that might help with the crashes. It looks like there is a typo in the filename (VS14 instead of VS15), but oh well. I'll try my parameter validation idea when all the updating is finished. It may be a while.
Megan BrooksSQL Server ConsultantCommented:
It appears to be possible to have the NoRows message for a tablix change according to parameter values, although I haven't actually gotten around to trying that, yet. If you have the main dataset return no rows when the date range is invalid, the NoRows expression can also check for an invalid date range and display a message to that effect instead of displaying a "no rows" message, which would be misleading.

My idea worked -- I was able to prevent the report from running if the date range was invalid by defining an internal parameter that depends on a dataset query that takes the two date range parameter values and compares them, returning a different value depending on whether or not the validation test fails. The value from the dataset determines the default value of the internal parameter, and if that value is not valid for the parameter then report execution is blocked.

I have not, however, been able to return any sort of intelligible error message to the user. One idea was to return blank or an error message string from the validation dataset, and to allow blank as the only valid value, thinking that RS might actually show the 'bad' value. Nope. It just says the value is "missing," which is not even true. But such is the built-in parameter UI for ReportViewer. It has a lot of issues.

I have one more idea to try, that doesn't depend on the available values list or any other built-in parameter validation feature.
Megan BrooksSQL Server ConsultantCommented:
I can cause an exception message of my choosing to be displayed by the local report viewer (used in Visual Studio), without allowing the report to run. It's not very pretty but it more or less gets the message across (see screen shot). I don't know if the HTML renderer will display the message or not. RS has a knack for hiding exception messages, and the behavior of different renderers can vary. But it might work.

I will post a sample RDL file later on that demonstrates both methods for handing a parameter range error (exception and NoRows). I still have a thing or two to try to improve the appearance of the exception message.
Megan BrooksSQL Server ConsultantCommented:
Oops, I guess I should attach the screen shot before sending.
Megan BrooksSQL Server ConsultantCommented:
Here is a sample RDL containing both methods. I improved the exception message somewhat (see new screen shot).

The exception message method runs by default. To see the NoRows method, delete the ValidateDateRangeParams internal parameter. When it doesn't throw an exception, the NoRows error message takes over instead. The latter method does cause the report to run, however, while the exception stops execution.

I still don't know if the HTML renderer shows or hides exception messages, and it's not something I can easily test today.

The exception itself is thrown in the IsValid function in the report Code property. The message can be changed there. The ValidateDateRangeParams parameter calls the function to obtain its default value. It seems to reliably do this after the Start and End parameter values have been set. It would be possible to force the execution order, if necessary, by creating another dataset and having it call the validation function, perhaps from a query parameter expression that is returned by the dataset. The internal parameter would then obtain its value from the dataset. That is more complexity than I wanted to introduce to this example, however, and it does not appear to be necessary.

The NoRows expression is a property of the tablix. Select Tablix1 in the Properties pane in Report Designer and you should find the property in the list, well down.

The tablix itself is just silly stuff. I needed some rows and didn't want to have to actually query tables from a database. You do have to connect the data source to a valid database, however. the database isn't actually used; it's just there to keep RS happy. I suppose I could have used an XML source or something.

This exercise certainly satisfied my curiosity about a few things I had never worked out. I hope it may be useful to others as well.
Megan BrooksSQL Server ConsultantCommented:
I inadvertently posted the SSRS 2016 version of the RDL. Here is the 2008 version, which should be compatible with later versions.
Auerelio VasquezETL DeveloperAuthor Commented:
Wow. Thanks for all the effort, I will try to implement this today.
Auerelio VasquezETL DeveloperAuthor Commented:
Thanks so much! i finally got a chance to implement.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.