SSRS Parameter Validation Using Custom Code

Introduction

This article explains the following points.

• Apply validation on from date and to the date parameter of SSRS. (The From date should be less than the To date.)
• Validation for Numeric number accepted by SSRS parameter. Here we check that the parameter value entered by the user is numeric.
• Specify a parameter value range. Like only 0-10 digit numbers are accepted by the parameter.
• The scenario is that we need to select all rows between FromOrderID and ToOrderID. In that case, we need to maintain fromOrderId should be less than ToOrderID.

Now we start from the first point.

Apply validation on the from-date and the date parameter of SSRS. (The From date should be less than the To date.)

For this, we need to go SSRS report (RDL file), where we need to implement the same validation. Here I am only showing how to implement the validation in a report. If you want to learn about SSRS report creation, go to this link.

• How to create SSRS Report and deploy

Here we see only validation using custom code.

Before starting, we must understand where to write custom code in SSRS. For this, go to any RDL file, go to the top menu bar Report, and select the Report properties option from here.

Then we see this window appear.

In this window, go to the Code tab. There we can write custom (VB) code only in this window.

Now we will start with our point, which is date parameter validation.

I write simple VB code and place it in the custom code window for this.

Function CheckDate(
  StartDate as DateTime ,  EndDate as DateTime
)  as Boolean :    Dim RetValue As Boolean   RetValue  =   "False"     if(
  DateDiff("d", StartDate, EndDate)< 0
)  Then   RetValue  =   "False"    Else    RetValue  =   "True"    End if   Return RetValue    End Function  

Function Description

Here you see, I have written a function CheckDate that accepts a StartDate and EndDate parameter checks the validation against these dates, and returns a true and false Boolean value.

Now we will see how to use this function in an SSRS report for date validation.

Now I will create two parameters for the report one is Order from Date, and the other is Order To Date.

Note. If you use a parametrized Stored Procedure, there is no need to create a parameter in SSRS Reports.

To create a parameter, we need to go to the ReportData window, go to the Parameter folder, right-click on that, and select Add Parameter.

In this manner, we can create any parameters in an SSRS report.

Then provide the parameter name and choose the data type depending on your value. In the same manner, we create another parameter for Order To Date. Here I will choose the DateTime data type and then set the visibility of the parameter to visible, in other words, you want to show the parameter in the SSRS report.

Here you can see the parameter that I created.

Now when we run the SSRS report, we can see two parameters on the top of the SSRS report window.

Now to validate these parameter values. In my case, the validation of the Order from Date is that it should be less than the Order to date.

Let us see how to validate this parameter.

Step 1

Create a Parameter name as IsValiddate and set the visibility to internal because we use this parameter for internal processes only. This parameter returns a true or false value.

Then go to the default values tab of this parameter and specify values.

After clicking on the Add button, you can see this TextBox here.

Now here we will write an expression, and for that, we need to click on the button of the preceding window.

Then here, we will write an expression called the custom code function CheckDate and pass the parameter value.

In the following window, you can see how I call the Custom code function.

The IsValidDate parameter returns a value on behalf of the function CheckDate. Now we can access the IsValidDate parameter in the SSRS report where we need to call the CheckDate function.

Step 2

Now we need to add filters on the dataset field ORDERDATE.

Note. If you use a parametrized Stored Procedure, you don't need to add filters here.

Here I will create two filters for the Order From date and Order to date parameters.

Go to the reportdata window, then the Dataset folder, right-click on the dataset, and choose Dataset Properties.

Now you can see the dataset properties window below. Here I have added a filter on the ORDERDATE Field of the dataset.

We need to validate the FromDate and Todate parameters to pass a value in the dataset.

In the same way, we need to validate the Todate parameter value.

Step 3

Now to display a user-readable message after validating these two parameters.

For this, we need to add a TextBox to the report and write a message we want to show users. In my case, I have a message like this.

Then we need to set the visibility of this TextBox. Go to the TextBox Properties and then set the visibility of the TextBox. Select the following option from the Visibility tab and click the fx button.

Then we need to set the value for the visibility. Here we choose the parameter IsvalidDate value that returns a true or false value.

Now we need to test the preceding approach in an SSRS Report. Let us go to the Report Preview part.

Here I have used order from the date of October and order to the date of April and clicked the ViewReport button, and then I got the following message: the purpose of this tutorial.

Conclusion

This article explains how to apply validation on an SSRS parameter and display a user-readable error message.