Since Reporting Services is not any front end programming tool (language like .NET or Java) validating user inputs may not be possible or at best it would be convoluted. I am still exploring what would be the best way to
=> Validate user Parameters supplied by Report Consumer.
=> Based on Validity Dataset (query) either needs to execute or stop from executing.
Till now we were able to validate user input in couple of ways and show to user about wrong parameters but query of report still executes with incorrect set of parameters.
To Validate User Parameters there could be 2 methods:
* Using Variables
* Using Code
SQL Server 2008 introduced Variable which can be used for validate user parameters.
In Report Main screen, Click on Report and to go Report Properties
In Report Properties screen go to Variables and declare a variable. In the value of variable type similar expression as below:
This expression takes CategoryName parameter from Parameters Collection and compares value (Entered by End Users) and compares with Bikes. Based on Parameters.CategoryName.Value being “Bikes” or not, True or False is assigned to the Variable.
Now in Report Body, place a TextBox and for TextBox.value properties type below expression
=iif(Variables!vs.Value.Equals("True"),"","Parameter Selected is not correct, Review Parameters Selected")
This indicates in Variable Collection for VS variable if it equals “False” then end user entered wrong values and above message would be displayed. Else nothing.
Additionally, for Hidden property of TextBox one can write below expression based on value of Variable.
This is one way of Validating User Inputs and sending User messages about wrong Input Parameters, but this is not stopping SSRS for executing query albeit with wrong set of parameters.
* For each parameter, there needs to be a corresponding variable for validation.
* And can get complex with lot of parameters with many different values for each parameter.
* Query still executes with wrong parameters.
For first 2 issues, we can check using Code functionality of SQL Server 2005 / 2008 Reporting Services but still exploring 3rd issue where query should not execute if parameters are not passing validation checks.
How to Use Code to achieve similar goal, we would post it in next blog..