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:
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.
This condition is easily checked using the following expression:
=IIF(Parameters!InputDate_To.Value < Variables!Last_Valid_Report_End_Date.Value, true, false)
End result for the cosmetics is
OK, we got the message and its visibility tackled, so here is the expression that drives the value of the Last_Valid_Report_End_Date report variable:
1: =DATEVALUE(CSTR(Year(DATEADD("d",31,Parameters!InputDate.Value))) + "-" +
2: CSTR(Month(DateAdd("d",31,Parameters!InputDate.Value))) + "-" +
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 SELECT statement> 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
The code is very simplistic and here it is in full for reference:
1: 'Invalidates the duration between the start date and end date
2: Function ValidateDatesApart(StartDate as DateTime , EndDate as DateTime) as Boolean
3: If EndDate <= DATEVALUE(CSTR(Year(DATEADD("d",31,StartDate))) + "-" +
4: CSTR(Month(DateAdd("d",31,StartDate))) + "-" + CSTR(Day(DateAdd("d",31,StartDate)))) Then
7: Return "False"
8: End if
9: End Function
Now go back to the report parameter declaration and click on its Default Values property page
After you clicked on Specify values option the Value drop-down becomes available where you copy and past the following expression to:
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