Validating user input -- sql server reporting services

magendo_man
9/7/2006 1:34:01 AM

You need to go to the Layout tab and then on the menu select Report > Report
Parameters... This is where you define the datatype of the parameters. You
can also force some validation by defining datasets to populate a given
parameter. Such a dataset has a "label field", which is the one displayed to
the user, and a "value field", which is presented to your query/stored
procedure. The label and value field can be the same.

You must also bear in mind that there are "Report" parameters and "dataset"
parameters. When you add a new dataset which requires parameters Reporting
Services adds the Report Parameters in for you. If you look at the Parameters
tab for the dataset you will see the mapping of report to dataset parameters.
You can make changes to these parameters to suit your own needs.

HTH

Magendo_man

Freelance SQL Reporting Services developer
Stirling, Scotland

[quoted text, click to view]

JL
9/8/2006 10:12:18 AM

Thank you Magendo_man for your response. What I am still unclear about
is how to validate and format a user input for not dataset populated
report parameters. For example, if I need a date, I want to (a) be
sure the value entered is a valid date and (b) if it is, format it
according to my needs (e.g. MMM, YYYY etc). Can this be done and if
so how? I also want to display this in a text box on the report.

TIA,
John

On Thu, 7 Sep 2006 01:34:01 -0700, magendo_man
[quoted text, click to view]

magendo_man
9/8/2006 11:03:01 AM

It isn't really possible to validate and format a directly entered report
parameter.

If your date parameter is a normal day/month/year then by defining the
parameter as datetime it will ensure that you get a valid date. However, the
system will then display the date according to your locale settings including
a time element.

If all you want to do is have a month and year entered then you would
probably be best to generate this using a dataset either by (a) selecting
valid month/year combinations from the same place as the report's main
dataset, or (b) just making up a list of valid dates based on the range
permissable, using a query/stored procedure.

You can easily display and format your report parameters in textboxes using
and expression, such as:
=Parameters!MyReportParameter.Value
This can be formatted using the textboxes format property

--

Magendo_man

Freelance SQL Reporting Services developer
Stirling, Scotland

[quoted text, click to view]

JL
9/8/2006 2:03:53 PM

Hi Magendo_man,
Thanks for the tip on the text box format property. That and setting
the parameter to datetime works fine. But now I am trying to use
custom code and hope you can shed some light for me.

What I have been trying to do since my las post was to use a custom
code function. So I typed the following into the custom code window:

Public Function GetMyDate(byval theUserDate as string) as string
If Isdate(theuserdate) then
Return Format(theUserDate, "MMM, YYYY")
Else
Return "Invalid Date"
End If
End Function

My problem is that when I try to access this code in the text box
function property by typing =Code.GetMyDate(Parameters!UserDate.Value)
I am not able to see a reference to the custom code. When I type Code.
I do not see my function but only a slection list of "Equals",
"GetHashCode","GetType", "Reference Equals" and "To String". I am
tinking I do not have something configured properly but according to
books on-line this should work.

BTW, I even simplifed my code to only have a pubic const and still
could not see it when I typed Code.

Any insight on (a) this approach and (b) what I am doing wrong?

Thanks for all your help,
John

On Fri, 8 Sep 2006 11:03:01 -0700, magendo_man
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Validating user input