SSRS 2005 Parameter validation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » SSRS 2005 Parameter validation

14 posts, Page 1 of 2

1 2 »»

SSRS 2005 Parameter validation

Rate Topic

Display Mode

Topic Options

Author

Message

neeraj-344433

Posted Monday, July 21, 2008 9:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:14 AM
Points: 3, Visits: 77

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

Post #537763

neeraj-344433

Posted Monday, July 21, 2008 12:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:14 AM
Points: 3, Visits: 77

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

Post #537914

e.j.egan

Posted Wednesday, August 06, 2008 8:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 02, 2010 12:52 PM
Points: 7, Visits: 46

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:

[color=000080]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[/color]

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):

[color=000080]=CODE.CheckLength(Parameters!MerchantID.Value)[/color]

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

Post #547573

e.j.egan

Posted Wednesday, August 06, 2008 8:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 02, 2010 12:52 PM
Points: 7, Visits: 46

Please ignore the hexidecimal color errors in the above post.

Post #547599

myke

Posted Thursday, August 07, 2008 2:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 8:45 AM
Points: 6, Visits: 8

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?

Post #548073

sgmunson

Posted Thursday, August 07, 2008 6:51 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

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)
Smile

Weight Loss Tips

Post #548245

e.j.egan

Posted Thursday, August 07, 2008 7:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 02, 2010 12:52 PM
Points: 7, Visits: 46

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.

Post #548273

e.j.egan

Posted Thursday, August 07, 2008 7:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 02, 2010 12:52 PM
Points: 7, Visits: 46

[color=#000000]In reply to [/color]

[color=#0008]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? [/color]

[color=#000000]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).[/color]

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

Post #548279

sgmunson

Posted Thursday, August 07, 2008 7:58 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

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)

Steve
(aka sgmunson)
Smile

Weight Loss Tips

Post #548307

e.j.egan

Posted Thursday, August 07, 2008 8:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 02, 2010 12:52 PM
Points: 7, Visits: 46

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.

Post #548315

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions