Data validation for datetime parameter in SSRS

Question

 

Hi,

 

I wanted to know more about validation of SSRS parameters. I have a simple report which has a parameter called startdate of DateTime datatype. The datetime parameter in SSRS takes manual input as well. So, the user can enter any junk value. I want to ensure that the input parameter is in correct format and I want to display an error msg when the format is incorrect. My report has the following VB code for validation:

 

Public Function Validate( ByVal startdate As String) As Boolean
If IsDate(startdate) = True Then
Return True
Else
Return False
End If
End Function

 

And my report has a textbox which has the expression property set to;

=Code.Validate(Parameters!startdate.Value)

the textbox on the report has to display if the entered date is valid or not.

 

But, when i enter an erroneous date, SSRS doesn't render the report and throws a generic error. This happens even before the code written for validating the parameter executes.

 

Also couldn't find a way to disable the manual input for the datetime parameter. Even that would solve the problem.

 

Another alternative was to make the startdate parameter as string, but i want the calendar control button to be provided for the user.

Answer 1

The function below checks for the Start and End date ranges .

 

Function:

Function CheckDateParameters(StartDate as Date, EndDate as Date) as Integer
Dim msg as String
     msg = ""
     If (StartDate > EndDate)  Then
 msg="Start Date should not be later than End Date"
     End If
     If msg <> "" Then
 MsgBox(msg, 16, "Report Validation")
 Err.Raise(6,Report)                    'Raise an overflow
     End If
End Function

 

Steps:

1.) Go the Report Parameters and add a parameter with the datatype is string.

2.) Check the Hidden checkbox and Allow blank value ckeckbox.

3.) From Default Values choose Non-Queried radio button and then press the FX button and paste this code.

=CODE.CheckDateParameters(<parameterStartdate>.Value,<parameterEnddate>.Value)

 

Then press OK.

 

Hope this helps......

Answer 2

In the sample that you provided, if we enter an improper date as 02/31/2003 [mm/dd/yyyy], then the report execution fails and an error is thrown right away "error occured during the processing of report parameter". I was talking of handling such errors.

 

I feel that reporting services, initially validates the entered value matches the datatype of the parameter and then proceeds with execution of any VB code and finally renders the report.

 

When I said validation, I wanted a functionality similar to the client side validation in an asp page, where improper date formats like 12/31/235668 etc can be taken care of.

 

Answer 3

 

Hi,

 

Even I have a similar requirement, where in I validate two dates that one date is lesser than the other date and throw an error.

 

But msgbox works fine when we are working in Sql Server BI but when deployed on the server it gives errors.

 

 

Any Suggestion

 

Thanks

Sen.

Answer 4

Hi Sen,

 

Have you find any ans. to your query.

If yes please share it with me, I have the similar requirment in one of my SSRS report.

 

Thanks

Saurabhkantdixit

 

Answer 5

The solution is to use several parameters to help implement the validation:
1) Parameter 1 allows the user to provide input.  If teh format of the data time is an issue, use a string parameter and then handle the conversion in the code.
2) Parameter 2 is a hidden parameter visible only to the report.  Set the value of this parameter Code.ValidationMethod(Parameter1.Value). 

If you'd like to show the user a nice error message, then use a third parameter which is a boolean.  Set its value to Code.IsValid(Parameter1.Value).   For Parameter2, set the value to a known time.

Then in the body of the report show/hide an error message text box based on the value of Parameter 3.

Hope that helps,
-Lukasz

---

Got a bug or feature request? Tell me about it at http://connect.microsoft.com. This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 6

Deepak,

We tried to implement the solution given in SQL 2008 express edition,but it is not working throwing generic error message " An Error occured during local report processing ". Will the solution work in the said version.. should I have make any changes to make it work in 2008 edition.

Thanks,

Vijay 

 

Answer 7

Hi, I'm having the same problem with the SQL 2008, did you solve it?

Answer 8

The rest of your report must handle the invalid parameter error.  For example - your query should have a 'safe default' so that it does not return an error.   I would investigate which part of the report is causing the error you're seeing and fix them accordingly.

This solution should work regardless of version of RS.

Hope this helps,

-Lukasz

---

Got a bug or feature request? Tell me about it at http://connect.microsoft.com. This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 9

the syntax:

 Code.IsValid(Parameter1.Value) 

returns IsValid a an Unrecognized identifier.

Sugestions?