SSRS 2005 Parameter validation

Question

Post reply

SSRS 2005 Parameter validation

neeraj-344433

Mr or Mrs. 500

Points: 519
More actions
July 21, 2008 at 9:40 am

#93601

Hi there,
I am writing a simple report using SSRS 2005 where user selects the start date and end date , and data which was added between start date and end date is displayed.
I created to date parameters and user can select dates from there. The issue is SSRS is allowing user to select the End date which is prior to selected Start Date. Is there a way i can specify or force user to select End date after Start date.
I have used RaiseError on stored procedure but i need something which can show the error immediatly.
Custom code is on option but i could not figure out how to implement that with parameters.
Please advice
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

neeraj-344433 Mr or Mrs. 500 Points: 519 More actions · Answer 1

Hi there,

I got this working. Following is the method i used to make it work in case someone else is in same situation :

You need to add a function to validate the parameters.

Go to the Report Properties under the Report menu and place this code to the Code Tab.

Public Function CheckDate(SDate as Date, EDate as Date) as Integer

Dim msg as String

msg = ""

If (SDate > EDate) Then

msg="Start Date should not be later than End Date"

End If

If msg <> "" Then

MsgBox(msg, 16, "Parameter Validation Error")

Err.Raise(6,Report) 'Raise an overflow

End If

End Function

Then go to the Report Parameters and Add named it XXX with the datatype is string. Checked the Hidden and Allow blank value ckeckboxes. In Available Values choose Non-Queried and from Default Values choose Non-Queried in right side of textbox then press the FX button then paste this code.

=CODE.CheckDate( .Value)

Then press OK.

Thanks

e.j.egan Old Hand Points: 369 More actions · Answer 2

Neeraj...

I have tried something similar to this unsuccessfully. I've worked on this for some time and am at the point of throwing in the towel. I want to validate a parameter length. I've set up code a number of different ways and finally settled on the following:

Public Function CheckLength (ByVal MerchantID as String) As String

Dim msg As String

msg = ""

TRY

If MerchantID.Trim.Length > 4 THEN

msg="Merchant ID should not exceed 4 characters"

Else

msg = " "

End If

Catch ex As Exception

END TRY

Return msg

End Function

I then setup a parameter named "Validate" just as you described above. The parameter expression is set up as follows (I've tried many combinations):

=CODE.CheckLength(Parameters!MerchantID.Value)

I either get an error on the hidden parameter or it just does nothing (when hidden parameter set up as above).

Any advice would be greatly appreciated.

Ed

e.j.egan Old Hand Points: 369 More actions · Answer 3

Please ignore the hexidecimal color errors in the above post.

myke SSC Journeyman Points: 84 More actions · Answer 4

ej - two things.

First, it doesn't appear you're raising an error anywhere, which, I believe is part of why it's not doing anything. Even if it does catch a MerchantID with a length > 4 - it will just return a message, and not really stop execution, no?

second, what is the error message you get on the hidden parameter?

sgmunson SSC Guru Points: 110639 More actions · Answer 5

There's a much easier way, although it might make the report design appear a bit odd. You can use an expression to change the visibility of any given section or control, or within a table, any given column, row, or cell. The concept is to make everything on the report invisible except one text box with the error message, and the value expression for that text box is almost identical to the expression for it's Visibility property. You then make that expression False when the parameter validates properly, or True when it doesn't. The visibility property determines if an object gets hidden (value True) or not (value False).

All the other objects in the report have a visibility expression that depends on the parameter being validated correctly. Thus, even if validating the parameter sufficiently requires custom code, you can still use the result of the custom code to affect visibility on any given object.

It's not the prettiest way to go, but it might be better than throwing in the towel. At least the user won't get any results they can use without a valid parameter. Your thoughts?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

e.j.egan Old Hand Points: 369 More actions · Answer 6

I coud do this however it would not prevent the back-end stored procedures from running the report. It's a very large report and ideally I would want the report to not run should the value entered for the parameter is > 4 characters.

e.j.egan Old Hand Points: 369 More actions · Answer 7

In reply to

ej - two things.

First, it doesn't appear you're raising an error anywhere, which, I believe is part of why it's not doing anything. Even if it does catch a MerchantID with a length > 4 - it will just return a message, and not really stop execution, no?

second, what is the error message you get on the hidden parameter?

You are correct, the report runs as normal however I'm not sure where the message is being returned. I expected it to be in my hidden parameter but when I make it unhidden and run the report it's just empty space. The error message I was hopping to see is the msg returned by the function, the "Merchant ID should not exceed 4 characters" (or something like that).

Suggestions? I originally got this idea from posting http://www.sqlservercentral.com/Forums/Topic537763-145-1.aspx#bm537914

sgmunson SSC Guru Points: 110639 More actions · Answer 8

Is there any way to limit the parameter to being selected from a list? If you can feed the parameter list from a query, then you might not need to worry about how many possible values there are. That would at least eliminate any problem parameters. Your thoughts?

Steve

(aka smunson)

:):):)

e.j.egan (8/7/2008)
I coud do this however it would not prevent the back-end stored procedures from running the report. It's a very large report and ideally I would want the report to not run should the value entered for the parameter is > 4 characters.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

e.j.egan Old Hand Points: 369 More actions · Answer 9

Well, actually the parameter was pulling from a data set (using an SPC) previously. Problem was it was depending on the values from two other data sets, so in all 3 SPSs were required to run before the entire report even ran. The report has 11 parameters. We elimiated the drop downs to help performance of initial load and it has helped.

e.j.egan Old Hand Points: 369 More actions · Answer 10

It's also created some issues. My boss now want to ensure we are validating user entered values. His fear "...what to prevent someone from entering a select * or delete statement". Fine. But the parameter is defiined as a varchar(4) so it will only pass the first 4 characters anyway.

dorai.pandi 54335 SSC Rookie Points: 37 More actions · Answer 11

Hi,

This is working fine in the solution(i.e., rdl file when I preview) but not working from report server.

It's throwing some error like :

Error during processing of ‘ValidateDate’ report parameter. (rsReportParameterProcessingError)

What can be done to solve this kind of issue?

Thanks

Dorai Pandi.P

sgmunson SSC Guru Points: 110639 More actions · Answer 12

Given the level of angst on this topic, I wonder if perhaps using SSRS is really the right solution for a scenario with what amounts to a desire for "cascading" parameters (a parameter that depends on the value of another parameter, or for a rather complex mix of inputs, all requiring validation. Seems to me that kind of requirement is far more appropriate to a developed application than to a reporting scenario.

The idea behind reporting is to keep it simple, and 11 parameters, while possible, is way beyond simple. That's not to say SSRS can't do it, but I question any kind of "reporting" being labelled as reporting when there are that many parameters. How can one person really keep track of that many selections? Something is missing here...

Just my two cents...

Steve

(aka sgmunson)

:-):-):-)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

CozzaroNero SSC-Addicted Points: 460 More actions · Answer 13

Hi there,

you said

"In Available Values choose Non-Queried and from Default Values choose Non-Queried in right side of textbox then press the FX button then paste this code =CODE.CheckDate( .Value) "

Unfortunately, the option "Non-Queried" doesn't exist.

For Default Values, I have ticked the radio-Box for Specify Values, clicked on the Fx button and copied =CODE.CheckDate( .Value).

For Available Values, I'm not sure what to select, if I select "NONE", I received an error message about this newly created parameter:

".... BC30157 Leading '.' or '!' can only appear inside a 'with' statement" that is documented in this page

http://msdn.microsoft.com/en-us/library/1f35a50w.aspx

What should be the set-up for "Available Value" ?

Thank you,

AL

MMartin1 One Orange Chip Points: 27764 More actions · Answer 14

I would just add that if a start date is greater than an ending data, there should be no data returned. The user will get the point sooner or later, though it helps to have that hidden message appear at this time, as described earlier.

----------------------------------------------------