SQL Server Reporting-SSRS Parameter validation using Custom(VB-visual basic) Code Part-1

In this article we are going to learn how to apply validation on SSRS parameter and display user readable error message. In this article I am going to discuss below listed point.
-Specify Parameter value range. Like only 0-10 digit number accepted by Parameter.
Scenario- We needs 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 first point.
  (1) Apply validation on from date and to date parameter of SSRS. [From date should be less than To date]
For this we need to go SSRS report (RDL file) where we need to implement same validation. Here I am only show how to implement validation in report. If you want learn SSRS report creation then go to this link Howto create SSRS Report and deploy.
Here we see only validation using custom code.
->Before Start we need to know where we can write custom code in SSRS. For this go to any RDL file then go to top menu bar Report.
Report properties

Then we see this window appeared.

Custom Code Window

In this window go to Code tab here we can write custom (VB) code only in this window.
Now we will start with our point which is date parameter validation.
For this I write simple vb code and placed it in custom code window.

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"
RetValue = "True"
End if
Return RetValue
End Function
VB Code
Function Description-Here you see I have written a function CheckDate which accept StartDate and EndDate parameter and check validation against these date and return Boolean value true and false.

Now we are going to see how to use this function in SSRS report for Date validation.
Now I am going to create two parameter for Report one is Order from Date and another one Order To Date.
Note- If you use parametrize stored procedure no need to create parameter in SSRS Report.
 Create Parameter – We need to go ReportData  window and then go to Parameter folder and right click on that and select add parameter.
Add Parameter
In this manner we can create any number of parameters in SSRS report.
After that Write parameter name and choose data type according to your value here I will choose DateTime Data type and then set visibility of parameter Visibility means you want to show parameter in SSRS report or not.  In same manner we create another parameter which is Order To Date.
Parameter Window
Here you can see parameter which I have created.
Report Data

Now when we will run SSRS report we can see two parameters on the top of SSRS report window.
Report Preview
Now times to validate these parameter values. In my case validation is Order from Date should be less than The Order to date.
Let us see how to validate this parameter.
Step 1: Create a Parameter name is IsValiddate and set visibility internal because we use this parameter internal process only. This parameter returns true or false value.
Validate Date
Then go to default values tab of this parameter and specify values.
After Click on Add button you can see this textbox here.

Now here we will write expression for this we need to click on fx button of above window.
Then here we will write expression which called custom code function CheckDate and pass parameter value.
 In below window you can see how I call Custom code function.
IsValidDate Parameter returns value on the behalf of function CheckDate. Now we can access IsValidDate parameter in SSRS report where we need to call CheckDate function.

Step 2: Now we need to add filters on dataset field ORDERDATE.
Note- If You use Parametrized stored procedure then no need to add filters here.
Here I am going create two filters for Order From date and Order to date parameters.
Go to reportdata window then Dataset folder then right click on dataset and choose Dataset Properties.

Now you can see dataset properties window below. Here I have added filter on ORDERDATE Filed of dataset.
Now we need to validate FromDate and Todate parameter to pass value in dataset.
In the same way we need to validate Todate parameter value.
Step 3: Now times to display User readable message to the user after validating these two parameters.
For this we need to add textbox in report and write message which you want show users. In my case I have put message like this.
After that we need to set visibility of this textbox. Go to textbox Properties and then set visibility of textbox. Select below option from Visibility tab and then click on fx button.
After that we need to set value for visibility. Here we choose parameter IsvalidDate value which returns true or false value.

Now we need to test above approach in SSRS Report. Let us go to Report preview part.
Here I Have put Order from date October month and order to date April month and click ViewReport Button then I got this message which is purpose of this tutorial.

In this article we have learned how apply validation on Parameter.In Next article i will discuss validation -
 ·         Validation for Numeric number accepted by SSRS parameter. Here we check parameter value is numeric or not which enter by user.

Thanks for Reading!!!

Priti Kumari

I am technical blogger.I blogs at www.c-Sharpcorner.com and https://aspdotnetmyblog.blogspot.in/.

No comments:

Post a Comment