News

Tweets













Validate SSRS Report Input Parameters the Proper Way

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:

image

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):

image

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

image

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))) + "-" + 
   3:  CSTR(Day(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.

image

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

image

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
   5:          Return"True"
   6:      Else
   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

image

After you clicked on Specify values option the Value drop-down becomes available where you copy and past the following expression to:

=Code.ValidateRange(Parameters!InputDate.Value,Parameters!InputDate_To.Value)

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

image

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

image

Tuesday, August 14, 2012 11:27 PM

Feedback

# re: Validate SSRS Report Input Parameters the Proper Way

Hi,

Thanks for providing a very good and complete process of validating the parameters.

It is very useful.. 10/14/2012 10:44 PM | Santosh

# re: Validate SSRS Report Input Parameters the Proper Way

Hi Dear,

Can you post it with more Calrification or any Video Link for same....I m facing problem to implement this.....

Ragard's
Durgesh Singh
durgeshgkp@hotmail.com 12/4/2012 4:47 AM | Durgesh

# re: Validate SSRS Report Input Parameters the Proper Way

Please post your issue description here or on the SSRS MSDN Forum http://bit.ly/ILH9P7 and will have it resolved in no time. 12/6/2012 11:52 AM | Arthur

# Loops In Variable

When I run the report I receive the following error:

An error occurred during local report processing.

The Variable(Last_Valid_Report_End_Date) expression for the report contains a direct or indirect reference to itself. Loops in variable value expressions are not allowed.

12/19/2012 12:49 PM | Brian

# re: Validate SSRS Report Input Parameters the Proper Way

It sounds to me as a case of an improper variable use.
Is the expression driving a chart? 12/19/2012 9:53 PM | Arthur

# re: Validate SSRS Report Input Parameters the Proper Way

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 ? 1/7/2013 10:35 AM | ALW

# re: Validate SSRS Report Input Parameters the Proper Way

My thinking is you should run a query to pull the allowed members and validate against this list. 1/12/2013 4:11 PM | Arthur

# re: Validate SSRS Report Input Parameters the Proper Way

is this a typo? Code.ValidateRange(Parameters!InputDate.Value,Parameters!InputDate_To.Value)

shouldn't be code.ValidateDatesApart? 2/8/2013 7:11 PM | Hui Shi

# re: Validate SSRS Report Input Parameters the Proper Way

Its is helping in Hidding Data from the report. Is there is any around to Skip execution of dataset if validation error occurs? 4/25/2013 12:10 PM | Raam

# re: Validate SSRS Report Input Parameters the Proper Way

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.?

Thans! 6/19/2013 4:37 AM | Daniel

# re: Validate SSRS Report Input Parameters the Proper Way

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.?

Thans! 6/19/2013 4:38 AM | Daniel

# re: Validate SSRS Report Input Parameters the Proper Way

Hello Daniel,

Please correct me if I misunderstood you:

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. 6/19/2013 3:50 PM | Arthur

# re: Validate SSRS Report Input Parameters the Proper Way

Cant we make warning message as popup 12/24/2013 5:32 AM | Tanmay

# re: Validate SSRS Report Input Parameters the Proper Way

SSRS does not expose the Windows OS MessageBox API, being a web based app it is not guaranteed that a target OS running it can even do it. Or do that in the same manner. 12/24/2013 1:02 PM | Arthur

# re: Validate SSRS Report Input Parameters the Proper Way

I created SSRS Reports Using SQL Sever 2008R2. In My report two date parameters(i.e. Week-starting-date and week-ending-date).when User selects Week-starting-date ending date will automatically populate after 3 months.That is fne.

When user selects end date below 3 Months,i need to display message to the User. End date should be less than 3 months.

Please Help Me
Konda 1/3/2014 7:19 AM | Konda

# re: Validate SSRS Report Input Parameters the Proper Way

Konda,

the trick is in having a pre-fabricated message that you display conditionally - really a hide and show switch pattern. 1/3/2014 9:45 AM | Arthur

Post A Comment
Title:
Name:
Email:
Comment:
Verification: