CODEWITHSUNDEEP
sqlsql-serversql-server-2008reporting-servicesparameters
Alias Varghese
Alias Varghese

Reputation: 2172

Report Parameter validation in ssrs report

I have created one SSRS report having begin date and end date. If I provide end date< start date it will execute the report as shown in the image enter image description here

But that condition I need to show a pop up "Please Check the start date and end date provided". How to do this?

Upvotes: 4

Views: 18513

Answers (4)

Rick Goff
Rick Goff

Reputation: 1

I don't know if this will solve your problem, but a simple approach is to use the Visibility characteristic of a couple of report elements.

For the objects in your data region, set the Visibility according to your data validation. Also create a textbox that gripes at the offender and set its Visibility as the opposite of the visibility of your table or matrix.

On Tablix Properties, Visibility, set the value of Hidden as:

=Iif(Parameters!EndDate.Value > Parameters!StartDate.Value, False, True)

Then for your validation textbox Visibility, set the value of Hidden as:

=Iif(Parameters!EndDate.Value > Parameters!StartDate.Value, True, False)

Upvotes: 0

NLandis
NLandis

Reputation: 264

Ciarán, thanks for your suggestion, but the problem with it is you are still hitting the database. I came across these two links that I found a little more helpful in explaining how to validate in SSRS without hitting the database.

This link shows everything except it's not clear how to use the validation parameter in conjunction with the stored proc.: http://geekswithblogs.net/Compudicted/archive/2012/08/14/validate-ssrs-report-input-parameters-the-proper-way.aspx

This link clears up how to use the validation parameter with the stored proc.: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/01b37f60-9048-4c54-8d54-574b5fc71de5/how-to-call-a-stored-proc-conditionally-based-on-a-parameter-validation-or-using-a-stored-proc-in?forum=sqlreportingservices

Added helpful link... I needed to validate using regular expressions and found this link really helpful: https://regex101.com/

Upvotes: 0

Hell Boy
Hell Boy

Reputation: 981

Click Report Menu then Report Properties.
Go to Code Tab and add similar code as per your requirement:

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

And

Follow the 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.

See reference Link:

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

Upvotes: 6

Ciar&#225;n
Ciar&#225;n

Reputation: 3057

I'm answering this to chip in another possible solution when working with SQL/Server. If you just want to throw an error then simply amend your query SQL to raise an error on the SQL/Server side by adding something like this to the top of your SQL...

IF @ParEndDate < @ParStartDate
BEGIN
   RAISERROR('Please check the start date and end date provided', 16, 1);
   RETURN;
END;

The query won't run and the error message will be displayed in the report body.

Upvotes: 1

Related Questions