How to Validate parameters in SQL Reporting Services

Can I apply validations to the parms in SQL Reporting Services?
Example:
I create a report that has two parameters listed:
FromDate
ToDate
When I run this report, user can key in any date he wishes. I need to apply
validations to the report:
1) FromDate must be less than ToDate
2) Dates must be in valid format
3) Date range cannot be more than 6 months
Can anyone tell me how to do this?
--
Thanks for your response.
JrYou could make the dates both dropdowns and make the "To Date" dependent on
the "From Date"
ex:
// data source for "From date"
select DISTINCT datePosted
from mytable
// date source for "To Date"
select distinct datePosted
from mytable
where datePosted > @.FromDate
and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
"JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
> Can I apply validations to the parms in SQL Reporting Services?
> Example:
> I create a report that has two parameters listed:
> FromDate
> ToDate
> When I run this report, user can key in any date he wishes. I need to
> apply
> validations to the report:
> 1) FromDate must be less than ToDate
> 2) Dates must be in valid format
> 3) Date range cannot be more than 6 months
> Can anyone tell me how to do this?
> --
> Thanks for your response.
> Jr|||dates are one example but what if there are business rules that need to be
written? cant that be done from reporting services or does it have to be done
via C#?
"Tim Dot NoSpam" wrote:
> You could make the dates both dropdowns and make the "To Date" dependent on
> the "From Date"
> ex:
> // data source for "From date"
> select DISTINCT datePosted
> from mytable
> // date source for "To Date"
> select distinct datePosted
> from mytable
> where datePosted > @.FromDate
> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
> > Can I apply validations to the parms in SQL Reporting Services?
> >
> > Example:
> > I create a report that has two parameters listed:
> > FromDate
> > ToDate
> >
> > When I run this report, user can key in any date he wishes. I need to
> > apply
> > validations to the report:
> > 1) FromDate must be less than ToDate
> > 2) Dates must be in valid format
> > 3) Date range cannot be more than 6 months
> >
> > Can anyone tell me how to do this?
> >
> > --
> > Thanks for your response.
> > Jr
>
>|||Class consultant response; "It Depends".
Really, it depends on what the rules are.
One such rule might be (this is from a real spec): "Display the current
season first followed by the next 2 future seasons in ascending order.
Finally, append all other seasons in reverse chronological order". This
rule can be met using TSQL (although it took me 10 minutes just to code the
sql for a parameter query).
Really complex business rules may best be written in C# and handled outside
of the report, then passed to it sans parameter toolbar. I honestly haven't
come across any that I couldn't handle in TSQL though. Can you give me an
example of some you've dealt with?
"don" <don@.discussions.microsoft.com> wrote in message
news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
> dates are one example but what if there are business rules that need to be
> written? cant that be done from reporting services or does it have to be
> done
> via C#?
>
> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>|||I'll chime back in. I don't fully understand your example below for the
dates. Are you saying you have a table with all dates in it? If I have a
FROM and TO date and I need the TO to be greater than the from, then how are
you loading the drop down for the TO date? Does this table contain all the
dates in time? I guess if I can understand how to do this date logic, then
most rules could be handled with TSQL. However, I don't know enuff yet to
know how to conditionally load a drop. Example: Dropdown1 has customers in
it. I want to load DropDown2 based on the customer selected in DropDown1.
How do you make the dropdown load conditioned on the first one?
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:uOWszXemGHA.2316@.TK2MSFTNGP04.phx.gbl...
> Class consultant response; "It Depends".
> Really, it depends on what the rules are.
> One such rule might be (this is from a real spec): "Display the current
> season first followed by the next 2 future seasons in ascending order.
> Finally, append all other seasons in reverse chronological order". This
> rule can be met using TSQL (although it took me 10 minutes just to code
> the sql for a parameter query).
> Really complex business rules may best be written in C# and handled
> outside of the report, then passed to it sans parameter toolbar. I
> honestly haven't come across any that I couldn't handle in TSQL though.
> Can you give me an example of some you've dealt with?
>
> "don" <don@.discussions.microsoft.com> wrote in message
> news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
>> dates are one example but what if there are business rules that need to
>> be
>> written? cant that be done from reporting services or does it have to be
>> done
>> via C#?
>>
>> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>
>|||Here you go.
> Example: Dropdown1 has customers in it. I want to load DropDown2 based
> on the customer selected in DropDown1. How do you make the dropdown load
> conditioned on the first one?
Let's suppose that @.CustomerId is the parameter to be used in the report.
Also, @.BillingPeriod is used in the report and is related to customer via a
table called "CustomerBillingPeriod".
the sql for the Customer parameter comes from the dataset dsCustomer:
select CustomerID,
CustomerFullName
from CustomerTable
the sql for the BillingPeriod parameter comes from the dataset
dsBillingPeriod:
select b.BillingPeriodID,
b.BillingPeriodDesc
from BillingPeriod b
INNER JOIN CustomerBillingPeriod cb
ON b.BillingPeriodID = cb.BillingPeriodID
WHERE cb.CustomerID = @.CustomerID
The billingPeriod parameter has now become a dependent parameter, dependent
on the selection of Customer.
There's a trade-off here though. The more dependent parameters you have in
a report, the more complicated the report becomes. You now have to
replicate portions of the TSQL for the report to help filter the report
based on parameters that will actually yield a report. If, on the other
hand, you made every parameter independent of the others, you now invite the
user to select parameters that yield no results whatsoever. This is
sometimes very frustrating, especially when a report takes longer than a
couple of minutes to run. On the positive side, the user is guided to their
report by the absense of parameter values that would otherwise give them the
"No data is available for the parameters you specified".
That's a paragraph from a book I'm going to write one day. <g>
-Tim
Say for example, that
"Jr" <JrM@.noemail.noemail> wrote in message
news:ezUBA9fmGHA.856@.TK2MSFTNGP03.phx.gbl...
> I'll chime back in. I don't fully understand your example below for the
> dates. Are you saying you have a table with all dates in it? If I have a
> FROM and TO date and I need the TO to be greater than the from, then how
> are you loading the drop down for the TO date? Does this table contain
> all the dates in time? I guess if I can understand how to do this date
> logic, then most rules could be handled with TSQL. However, I don't know
> enuff yet to know how to conditionally load a drop. Example: Dropdown1
> has customers in it. I want to load DropDown2 based on the customer
> selected in DropDown1. How do you make the dropdown load conditioned on
> the first one?
>
> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
> news:uOWszXemGHA.2316@.TK2MSFTNGP04.phx.gbl...
>> Class consultant response; "It Depends".
>> Really, it depends on what the rules are.
>> One such rule might be (this is from a real spec): "Display the current
>> season first followed by the next 2 future seasons in ascending order.
>> Finally, append all other seasons in reverse chronological order". This
>> rule can be met using TSQL (although it took me 10 minutes just to code
>> the sql for a parameter query).
>> Really complex business rules may best be written in C# and handled
>> outside of the report, then passed to it sans parameter toolbar. I
>> honestly haven't come across any that I couldn't handle in TSQL though.
>> Can you give me an example of some you've dealt with?
>>
>> "don" <don@.discussions.microsoft.com> wrote in message
>> news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
>> dates are one example but what if there are business rules that need to
>> be
>> written? cant that be done from reporting services or does it have to be
>> done
>> via C#?
>>
>> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date"
>> dependent on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need
>> > to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>
>>
>|||I think i've got now. Thanks so much for your help.
PS - let me know when the book gets published!
Jr
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:ueg2jYhmGHA.4992@.TK2MSFTNGP03.phx.gbl...
> Here you go.
>> Example: Dropdown1 has customers in it. I want to load DropDown2 based
>> on the customer selected in DropDown1. How do you make the dropdown load
>> conditioned on the first one?
> Let's suppose that @.CustomerId is the parameter to be used in the report.
> Also, @.BillingPeriod is used in the report and is related to customer via
> a table called "CustomerBillingPeriod".
> the sql for the Customer parameter comes from the dataset dsCustomer:
> select CustomerID,
> CustomerFullName
> from CustomerTable
> the sql for the BillingPeriod parameter comes from the dataset
> dsBillingPeriod:
> select b.BillingPeriodID,
> b.BillingPeriodDesc
> from BillingPeriod b
> INNER JOIN CustomerBillingPeriod cb
> ON b.BillingPeriodID = cb.BillingPeriodID
> WHERE cb.CustomerID = @.CustomerID
>
> The billingPeriod parameter has now become a dependent parameter,
> dependent on the selection of Customer.
>
> There's a trade-off here though. The more dependent parameters you have
> in a report, the more complicated the report becomes. You now have to
> replicate portions of the TSQL for the report to help filter the report
> based on parameters that will actually yield a report. If, on the other
> hand, you made every parameter independent of the others, you now invite
> the user to select parameters that yield no results whatsoever. This is
> sometimes very frustrating, especially when a report takes longer than a
> couple of minutes to run. On the positive side, the user is guided to
> their report by the absense of parameter values that would otherwise give
> them the "No data is available for the parameters you specified".
> That's a paragraph from a book I'm going to write one day. <g>
> -Tim
> Say for example, that
> "Jr" <JrM@.noemail.noemail> wrote in message
> news:ezUBA9fmGHA.856@.TK2MSFTNGP03.phx.gbl...
>> I'll chime back in. I don't fully understand your example below for the
>> dates. Are you saying you have a table with all dates in it? If I have
>> a FROM and TO date and I need the TO to be greater than the from, then
>> how are you loading the drop down for the TO date? Does this table
>> contain all the dates in time? I guess if I can understand how to do
>> this date logic, then most rules could be handled with TSQL. However, I
>> don't know enuff yet to know how to conditionally load a drop. Example:
>> Dropdown1 has customers in it. I want to load DropDown2 based on the
>> customer selected in DropDown1. How do you make the dropdown load
>> conditioned on the first one?
>>
>> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
>> news:uOWszXemGHA.2316@.TK2MSFTNGP04.phx.gbl...
>> Class consultant response; "It Depends".
>> Really, it depends on what the rules are.
>> One such rule might be (this is from a real spec): "Display the current
>> season first followed by the next 2 future seasons in ascending order.
>> Finally, append all other seasons in reverse chronological order". This
>> rule can be met using TSQL (although it took me 10 minutes just to code
>> the sql for a parameter query).
>> Really complex business rules may best be written in C# and handled
>> outside of the report, then passed to it sans parameter toolbar. I
>> honestly haven't come across any that I couldn't handle in TSQL though.
>> Can you give me an example of some you've dealt with?
>>
>> "don" <don@.discussions.microsoft.com> wrote in message
>> news:9CF3705B-AA2C-4070-9EA9-A4D4E1F5EC4E@.microsoft.com...
>> dates are one example but what if there are business rules that need to
>> be
>> written? cant that be done from reporting services or does it have to
>> be done
>> via C#?
>>
>> "Tim Dot NoSpam" wrote:
>> You could make the dates both dropdowns and make the "To Date"
>> dependent on
>> the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> > Can I apply validations to the parms in SQL Reporting Services?
>> >
>> > Example:
>> > I create a report that has two parameters listed:
>> > FromDate
>> > ToDate
>> >
>> > When I run this report, user can key in any date he wishes. I need
>> > to
>> > apply
>> > validations to the report:
>> > 1) FromDate must be less than ToDate
>> > 2) Dates must be in valid format
>> > 3) Date range cannot be more than 6 months
>> >
>> > Can anyone tell me how to do this?
>> >
>> > --
>> > Thanks for your response.
>> > Jr
>>
>>
>>
>|||Tim-
One more question on this part. I understand the customer and billingperiod
example, but what about dates:
1) how do i prevent user from keying 2/32/2006?
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
> You could make the dates both dropdowns and make the "To Date" dependent
> on the "From Date"
> ex:
> // data source for "From date"
> select DISTINCT datePosted
> from mytable
> // date source for "To Date"
> select distinct datePosted
> from mytable
> where datePosted > @.FromDate
> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>|||If they're dropdowns, the user can't key in an invalid date.
As an aside, you could also use custom code to perform parameter validation
although this is a real pain in the posterior and you may spend more time
working around that than actually building a good report, but,
You ~could~ have a textbox above your report visualization (i.e., Table,
Matrix, List) and add 2 methods to the report in the code section:
' assume you have date1 and date2 as parameters
Public shared function IsValidParameterList(date1 as string, date2 as
string) as boolean
dim startDate as dateTme = datetime.parse(date1)
dim endDate as dateTime = dateime.Parse(date2)
if ( endDate < startDate) then
return false
end if
return true
End function
Public shared function GetInvalidParameterMessage(date1 as string, date2 as
string) as string
Dim rtnvalue as string
dim startDate as dateTme = datetime.parse(date1)
dim endDate as dateTime = dateime.Parse(date2)
if ( endDate < startDate) then
rtnvalue = "End date must be greater than start date"
end if
return rtnvalue
End function
Then in your report, the textbox would have visibility expression ="=(Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
Parameters!EndDate.Value.ToString()))"
And your report visualization would have visibility expression = "=(NOT
Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
Parameters!EndDate.Value.ToString()))"
This is purely an academic exercise. Like I said, you could spend almost as
much time doing validation like this as you could giving the user a good
report and an hour of training on how to use it. At the end of the day, you
really can't protect a user from themselves but so much. It's cost
prohibitive.
-Tim
"JrMcG" <JrM@.noemail.noemail> wrote in message
news:%23056RurmGHA.3732@.TK2MSFTNGP05.phx.gbl...
> Tim-
> One more question on this part. I understand the customer and
> billingperiod example, but what about dates:
> 1) how do i prevent user from keying 2/32/2006?
> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
> news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>>
>|||Tim -
Thanks. This has been most informative. I think i'll go with the drop down
for teh Ending date, with a date picker on the from date.
Thanks again.
Jr.
"Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
news:u5G2uTymGHA.200@.TK2MSFTNGP03.phx.gbl...
> If they're dropdowns, the user can't key in an invalid date.
> As an aside, you could also use custom code to perform parameter
> validation although this is a real pain in the posterior and you may spend
> more time working around that than actually building a good report, but,
> You ~could~ have a textbox above your report visualization (i.e., Table,
> Matrix, List) and add 2 methods to the report in the code section:
> ' assume you have date1 and date2 as parameters
> Public shared function IsValidParameterList(date1 as string, date2 as
> string) as boolean
> dim startDate as dateTme = datetime.parse(date1)
> dim endDate as dateTime = dateime.Parse(date2)
> if ( endDate < startDate) then
> return false
> end if
> return true
> End function
> Public shared function GetInvalidParameterMessage(date1 as string, date2
> as string) as string
> Dim rtnvalue as string
> dim startDate as dateTme = datetime.parse(date1)
> dim endDate as dateTime = dateime.Parse(date2)
> if ( endDate < startDate) then
> rtnvalue = "End date must be greater than start date"
> end if
> return rtnvalue
> End function
> Then in your report, the textbox would have visibility expression => "=(Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
> Parameters!EndDate.Value.ToString()))"
> And your report visualization would have visibility expression = "=(NOT
> Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
> Parameters!EndDate.Value.ToString()))"
> This is purely an academic exercise. Like I said, you could spend almost
> as much time doing validation like this as you could giving the user a
> good report and an hour of training on how to use it. At the end of the
> day, you really can't protect a user from themselves but so much. It's
> cost prohibitive.
> -Tim
>
> "JrMcG" <JrM@.noemail.noemail> wrote in message
> news:%23056RurmGHA.3732@.TK2MSFTNGP05.phx.gbl...
>> Tim-
>> One more question on this part. I understand the customer and
>> billingperiod example, but what about dates:
>> 1) how do i prevent user from keying 2/32/2006?
>> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
>> news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>> You could make the dates both dropdowns and make the "To Date" dependent
>> on the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>>
>>
>|||Glad I could help. I've been down both paths and have even been down the
path of doing my own UI for the parameters. That was a big waste of time
for little payback. IMHO, there's more business value in a solid report
with meaningful information rather than a flashy UI that captures
parameters.
_T
"JrMcG" <JrM@.noemail.noemail> wrote in message
news:Oxdk2$2mGHA.3376@.TK2MSFTNGP03.phx.gbl...
> Tim -
> Thanks. This has been most informative. I think i'll go with the drop
> down for teh Ending date, with a date picker on the from date.
> Thanks again.
> Jr.
> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
> news:u5G2uTymGHA.200@.TK2MSFTNGP03.phx.gbl...
>> If they're dropdowns, the user can't key in an invalid date.
>> As an aside, you could also use custom code to perform parameter
>> validation although this is a real pain in the posterior and you may
>> spend more time working around that than actually building a good report,
>> but,
>> You ~could~ have a textbox above your report visualization (i.e., Table,
>> Matrix, List) and add 2 methods to the report in the code section:
>> ' assume you have date1 and date2 as parameters
>> Public shared function IsValidParameterList(date1 as string, date2 as
>> string) as boolean
>> dim startDate as dateTme = datetime.parse(date1)
>> dim endDate as dateTime = dateime.Parse(date2)
>> if ( endDate < startDate) then
>> return false
>> end if
>> return true
>> End function
>> Public shared function GetInvalidParameterMessage(date1 as string, date2
>> as string) as string
>> Dim rtnvalue as string
>> dim startDate as dateTme = datetime.parse(date1)
>> dim endDate as dateTime = dateime.Parse(date2)
>> if ( endDate < startDate) then
>> rtnvalue = "End date must be greater than start date"
>> end if
>> return rtnvalue
>> End function
>> Then in your report, the textbox would have visibility expression =>> "=(Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
>> Parameters!EndDate.Value.ToString()))"
>> And your report visualization would have visibility expression = "=(NOT
>> Code.IsValidParameter(Parameters!StartDate.Value.ToString(),
>> Parameters!EndDate.Value.ToString()))"
>> This is purely an academic exercise. Like I said, you could spend almost
>> as much time doing validation like this as you could giving the user a
>> good report and an hour of training on how to use it. At the end of the
>> day, you really can't protect a user from themselves but so much. It's
>> cost prohibitive.
>> -Tim
>>
>> "JrMcG" <JrM@.noemail.noemail> wrote in message
>> news:%23056RurmGHA.3732@.TK2MSFTNGP05.phx.gbl...
>> Tim-
>> One more question on this part. I understand the customer and
>> billingperiod example, but what about dates:
>> 1) how do i prevent user from keying 2/32/2006?
>> "Tim Dot NoSpam" <Tim@.MindYourSpammy.spam> wrote in message
>> news:erXqGFTmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>> You could make the dates both dropdowns and make the "To Date"
>> dependent on the "From Date"
>> ex:
>> // data source for "From date"
>> select DISTINCT datePosted
>> from mytable
>> // date source for "To Date"
>> select distinct datePosted
>> from mytable
>> where datePosted > @.FromDate
>> and abs(dateadiff(month, @.FromDate, datePosted)) <= 6
>> "JrMcG" <JrMcG@.discussions.microsoft.com> wrote in message
>> news:911F6B67-06F9-4D82-969A-08C276D382F9@.microsoft.com...
>> Can I apply validations to the parms in SQL Reporting Services?
>> Example:
>> I create a report that has two parameters listed:
>> FromDate
>> ToDate
>> When I run this report, user can key in any date he wishes. I need to
>> apply
>> validations to the report:
>> 1) FromDate must be less than ToDate
>> 2) Dates must be in valid format
>> 3) Date range cannot be more than 6 months
>> Can anyone tell me how to do this?
>> --
>> Thanks for your response.
>> Jr
>>
>>
>>
>