Prev: Problem if the table does not provide data
Next: problem with report parameters
From: Barry Gilbert on 25 Jun 2008 14:51
I have a query with two parameters, both of which are nullable. However, in
the report, I want to require that at least one of the two be entered. In
other words, they can't both be null, but one or the other can. Any thoughts
on how to do this?

Also, I don't want an initial data set to appear when first opening the
report. They'll need to fill in one or both values.

Any help would be appreciated.
From: James Terrington on 30 Jun 2008 02:36
Barry,

I had a similar requirement a while back.

I initially started off with the following site, it was a good start.
http://blogs.msdn.com/chrishays/archive/2006/04/18/AllParameterValueRedux.aspx

At the end of the day:

1) In my report I have 2 parameters - ProjectManager and ProjectType
2) In my query I had the same lines twice (I use the QUERY DESIGN view)

ProjectMgr.Value=(a)ProjectManager (I had the parameter in the FILTER column)
ProjectManager.Value Is Null (the parameter was in the FILTER column)
ProjectType.Value=(a)ProjectType (I had the parameter in the OR column)
ProjectType.Value Is Null (I had the parameter in the OR column)

Regards

James


From: James Terrington on 30 Jun 2008 02:37
also try this page is that did not help:

http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx
From: Barry Gilbert on 1 Jul 2008 13:51
James,

Thanks for your suggestion. My issue was not with the where clause in the
query, but with the validation in the report's parameters. I don't want the
report to allow both parameters to be null. Also, this report doesn't lend
itself to default values for either of these two parameters.

What I ended up doing was forcing on the the parameters to a string and
setting the default to ' '. This produced an empty report on initial load. If
the user backspaces this value out of the prompt and clicks View Report,
he/she'll get a gazillion records. Oh well. Otherwise, they'll fill in a
valid value for either of the two parameters and get just what they want.

It's not the cleanest solution, but it works.

Thanks,
Barry

"James Terrington" wrote:

> also try this page is that did not help:
>
> http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx
 | 
Pages: 1
Prev: Problem if the table does not provide data
Next: problem with report parameters