Easy Step by Step SSRS Parameter Validation Using Code & Conditional DataSet

At A Glance

Parameter Validation on web application is almost must all the time. Usually developer use javascript or server side script validation.

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.

Implementation

On SQL Server BIDS:

  1. Create a New Report.
  2. 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
    return FALSE
    else
    return TRUE
    end if
    end function

    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.
  3. 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.
  4. Click Report Menu then Report Parameters. Add New Parameter with Name: IsValidParam, DataType: Boolean, tick Hidden, Default Values set to
    =code.ValidParams(Parameters!startdate.Value, Parameters!enddate.Value)
    This hidden parameter IsValidParam will have value true/false depending on stardate and enddate parameters. This point is a second key note to remember.
  5. 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.
  6. Make simple table layout and textbox (name: validationmsg) to prompt an error message if validation is false. I made its font bold and red.
  7. 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.
  8. Preview the report with only 1 year order date range. Parameters are valid.
  9. 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.

Regards,
Agung Gugiaji

Advertisements

4 responses to “Easy Step by Step SSRS Parameter Validation Using Code & Conditional DataSet

  1. This is exactly what I was looking for. Thank you so much!

  2. how to do it for cascading parameters . can you please explain

  3. and i am using stored procedure not a query . so can you give me details how can i do the same validation using stored procedure as my datasource and using cascading parameters

    • Hi Ansar,

      You can change this part : =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”)

      Replace that with stored procedure and take Parameters!IsValidParam.Value as one of arguments i.e dbo.employee(Parameters!startdate.Value, Parameters!enddate.Value, Parameters!IsValidParam.Value).
      Inside this stored procedure use a condition if else and write proper query

      Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s