SSRS Parameter Validation Using Custom Code

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

Here we see only validation using custom code.

Before starting we need to understand where we can write custom code in SSRS. For this go to any RDL file and then go to the top menu bar Report and then 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, that is date parameter validation.

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

1. Function CheckDate(StartDate as DateTime , EndDate as DateTime) as Boolean:  
2. Dim RetValue As Boolean  
3. RetValue = "False"   
4. if(DateDiff("d",StartDate,EndDate)<0) Then  
5. RetValue = "False"  
6. Else   
7. RetValue = "True"  
8. End if  
9. Return RetValue   
10. End Function  

Function Description

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

Now we will see how to use this function in a 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 then there is no need to create a parameter in SSRS Reports.
To create a parameter we need to go the ReportData window and then go to the Parameter folder and right-click on that and select Add Parameter.



In this manner we can create any number of parameters in a SSRS report.

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



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 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 that is 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 the 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 then there is no 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 then 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.



Now 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 to the user after validating these two parameters.

 

For this we need to add a TextBox to the report and write a message that you want to show to 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 then click on 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 a SSRS Report. Let us go to the Report Preview part.

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