It should not be a secret that sooner or later in the BI developer’s career one would face the necessity to perform a complex input parameter validations.

This necessity to validate data is typically triggered by the report design itself allowing great freedom to the end user to supply input data in a quite relaxed form. Alas, as what I heard from William Vaughan, “every data are dirty, unless proven otherwise”, and I totally agree!

I do not want to go to great extends here describing what the negative consequences are in failing to invalidate bad input, so let’s just not forget to provision data validation each time we design any report. And I would like to encourage you right away that the input validation is not terribly difficult or even time consuming endeavor at all, especially for those who are armed with the proper VB scripting knowledge.

However, even though I already seen a significant number of blog posts covering this topic, and after consulting for so many years, from my personal experience, I still have not seen so far even a single report I touched where the validation would be done 100% properly and efficiently.

This negative experience has made me highly motivated to write a post with a comprehensive example of how a BI developer should approach and implement the input data validation.

Another item I would like to tell up in front is that even though I will be covering the date range validation, the technique is quite generic and can be applied to any kind of input data validation.

To demonstrate, I will start off with an already created report. In fact, this was one of the reports I was tasked with to introduce the input parameters validation into.

It is called “Transaction Load – Daily” report which is part of a real usage tracking solution. The report takes in two parameters – the report start date and its end date.

The business rule imposed was that the start date - end date range should not exceed 31 days between, and the other I figured begged to be implemented is to prevent an end user (a human!) entering a start date greater than the report’s end date.

So a report starting state would be just two dates to pick:

Very simple!

To cover the range rule I decided to implement it so it would present a user with a red label that tells one about this mishap. One note: raising modal boxes with an exclamation mark kind of messages in SSRS reports is seldom a good idea because you even may not know where the report is going executing and thus it’s functionality not guaranteed to work.

So I went ahead and added a text box that displays exactly that. Plus, to drive its visibility, I created a report variable Last_Valid_Report_End_Date (let’s ignore its expression for a moment):

This variable would hold the last correct report end date, so anything entered greater than that value will be deemed as invalid.

It feels like all the heavy-lifting is done at this point, but hey, what about the report data? There is nothing stopping it from executing and it will, for free, why? Not good. Let’s be wise and prudent by not asking the report engine to go and try to bring our data and thus making un-necessary trips to the database.

To do that we need to be a little crafty. To not bring the data we may opt for applying a parameter that would prevent the query from getting any data if the business rule does not permit – besides, this is the key point, in all reports, I did not see anybody going this far, but it is such a pity!

So to prevent the report from fetching any data the best you can do is to add a WHERE clause that works against that.

In my example the WHERE condition ended up being

<a necessary SELECTstatement> AND (@IsRangeValid <> 0)

To make this parameter transparent to the end user and yet fed from his or her input you mark it hidden.

Note: the parameter data type is Boolean, but in the expression it is getting compared to is the 0 (zero), this is due to the fact the SSRS uses Visual Basic as its scripting engine where a true value is –1 and 0 is false.

Another note here is that the SSRS expressions are merely Visual Basic elements of code, therefore very portable between the expressions to report code.

On this note, we are going to cover the VB script portion. To add it, access the report’s code property page by right-clicking anywhere on the report designer canvas

Please note: despite the Itellisense (TM) underlines the ValidateRange portion of the expression in red as it would be invalid, in fact it is perfectly fine.

Do not forget to make any necessary other report elements hidden if the parameter validation failed, besides, if you wish, and it is a better design, you can now use the parameter to drive the visibility

At this point we have a working expression that helps us to prevent the un-necessary trips to the database

Hi,I have a scenario that wants to validate the input parameter value with existing members. For an example dimension has A , B , C members. If I enter D as input parameter that gives an error. How to validate that ?

Hello,nice approach - just what i was looking for.Now i have the problem that i use a stored procedure as data source, so i only have only to select / enter the name of the procedure. How can i implement the parameter validation with the using of stored proc.?

Hello,nice approach - just what i was looking for.Now i have the problem that i use a stored procedure as data source, so i only have only to select / enter the name of the procedure. How can i implement the parameter validation with the using of stored proc.?

The data source is irrelevant. In your case a stored proc I imagine expects input parameters that result in the prompts you see for the parameters in turn in the SSRS report designer that you must validate, so in short, you are expected to do nothing, getting data off a stored proc is a common case.

Do you know of a way to use this logic for multi-valued parameters? My report uses several parameters and I want to add some logic to edit the parameters to avoid unnecessary database searches. This solution is great for one value parameters. I'm trying to find a way to edit multi-valued parameters.

This might be a bit off topic but any help would be appreciated. I am new to SSRS and looking for a way to take any date format input, from a parameter, (DDMMYYYY, DDMMYY, YYYYDDMM), any format, and convert it to the value my query can use, YYYY-MM-DD. Is there any such conversion or do I need to create a rule to alert the end user they are entering a date in an incorrect format?