[SSRS] Parameter Validation on Reports

Question

I'm new to SSRS, so I apologize if this question is too simple:

I have a report which accepts a parameter called "Amount". I want to constrain valid inputs to currency values >= 0, and pop open an error message if the user enters improper values.

I don't want to validate inputs in my stored procedure and throw exceptions, because SSRS displays a very generic "Query execution failed for 'someTable'" message to users who access the report from another machine, and my business does not want to turn on the "Enable Remote Errors" flag.

How do I add input validation to report parameters and notify users of bad input?

Yes, I've googled around, but haven't had much luck. Thanks in advance :)

Answer 1

It's possible to make report parameters in SSRS that are based on a particular list or a lookup query but I don't think you can apply a regex or something like that.

Instead you might consider separating your report into two panels, one which displays your report and one which displays an error and then you create an assembly with a function in it that validates the parameters for the report before it is run. If the validate parameters function is successful you hide the error panel and show the report panel, if not you do the opposite.

Answer 2

I don't think there's a whole lot you can do in the reporting tool itself. It's pretty rudimentary.

However, you can provide an ASP.net web interface or a form that you can use to ask the users for the parameter values in any format .NET allows, and use the ReportViewer control to display the report. It sounds daunting, but it's actually pretty straight-forward, particularly if you already have a project you can build on.

Microsoft gives tutorials for using the ReportViewer controls.

Answer 3

Okay, how about this?

All you have in SSRS, really, is the SQL query and expressions in report fields.

Perhaps you could add a big, red text box at the top of the report for your error message, and give it an expression like '=IIf(Parameters!Amount.Value < 0, "Error: Invalid Amount", "")'.

Then go to your table's "Hidden" property and give it the expression "=Parameters!Amount.Value < 0"

You could also add into your query's where clause and add "AND @Amount >= 0" so you aren't fetching from the database when there's an error.