However, although SQL Server Reporting Service (SSRS) is a web app but as a default it does not have parameter (user input) validation. We have to write a Code at Report Properties Menu and implement it on Report Parameter.
The SSRS Parameter Validation mechanism must check if parameters do not meet specific criteria then related sql query is not running.
I use SSRS 2005 and AdventureWorks database to demonstrate creating this validation. I will show Purchase Order info based on Order Date.
Parameters are range of Order Date (Order Date From & Order Date To) but the range must not exceed 365 days or 1 year.
On SQL Server BIDS:
- Create a New Report.
- Click Report Menu then Report Properties. Go to Code Tab
Write in the code:
function ValidParams(byval startdate as datetime, byval enddate as datetime) as boolean
dim lngdatediff as long
lngdatediff = DateDiff(DateInterval.Day, startdate, enddate)
if lngdatediff > 365 then
Click OK button.
The function check date difference between two dates. if the difference below 365 days then parameters are valid or true otherwise validation returning false.
Creating a code is a first key not to remember when creating SSRS parameter validation in this example.
- Click Report Menu then Report Parameters. Add New Parameter with Name: startdate, DataType: DateTime, Prompt: Order Date From. Add another Parameter with same way unless Name is enddate and Prompt is Order Date To.
- Click Report Menu then Report Parameters. Add New Parameter with Name: IsValidParam, DataType: Boolean, tick Hidden, Default Values set to
This hidden parameter IsValidParam will have value true/false depending on stardate and enddate parameters. This point is a second key note to remember.
- Create a New Data Set with proper DataSource and write expression as below
=iif(Parameters!IsValidParam.Value, "select EmployeeID, VendorID, OrderDate, TotalDue from Purchasing.PurchaseOrderHeader where OrderDate between '" + Parameters!startdate.Value + "' and '" + Parameters!enddate.Value + "'", "select '' as EmployeeID, '' as VendorID, '' as OrderDate, '0' as TotalDue")
Above expression means if IsValidParam is true then run PurchaseOrderHeader query with filled parameters otherwise we just print empty data. This point is a third key note to remember.
- Make simple table layout and textbox (name: validationmsg) to prompt an error message if validation is false. I made its font bold and red.
- Right Click validationmsg textbox, click Properties and go to Visibility tab. Check on Expression Radio Button and write below code
=iif(Parameters!IsValidParam.Value, true, false)
So this textbox only appear only if validation is false.
- Preview the report with only 1 year order date range. Parameters are valid.
- Preview the report with 2 years order date range. Parameters are not valid.
Returning Empty Data and the validationmsg textbox is visible.
In a summary there are three key points to create this validation. Write a code in Report Properties, add hidden parameter that contains SSRS Code execution result and conditional expression in DataSet.