sql server reporting services: How to Validate parameters in SQL Reporting Services -- sql server reporting services

Re: How to Validate parameters in SQL Reporting Services

don
6/26/2006 8:13:02 AM

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#?

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

Tim Dot NoSpam
6/26/2006 11:00:32 AM

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

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

Tim Dot NoSpam
6/27/2006 12:00:00 AM

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?

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

Jr
6/27/2006 11:39:02 AM

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?

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

Tim Dot NoSpam
6/27/2006 2:18:48 PM

Here you go.

[quoted text, click to view]

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
[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

JrMcG
6/28/2006 9:43:50 AM

I think i've got now. Thanks so much for your help.

PS - let me know when the book gets published!
Jr
[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

JrMcG
6/28/2006 10:05:09 AM

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?

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

Tim Dot NoSpam
6/28/2006 10:36:27 PM

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

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

JrMcG
6/29/2006 12:00:00 AM

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.

[quoted text, click to view]

Re: How to Validate parameters in SQL Reporting Services

Tim Dot NoSpam
6/29/2006 1:22:51 PM

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

[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 in the sql server reporting services group.