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.
James Terrington - 30 Jun 2008 07:36 GMT
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=@ProjectManager (I had the parameter in the FILTER column)
ProjectManager.Value Is Null (the parameter was in the FILTER column)
ProjectType.Value=@ProjectType (I had the parameter in the OR column)
ProjectType.Value Is Null (I had the parameter in the OR column)
Regards
James
James Terrington - 30 Jun 2008 07:37 GMT
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
Barry Gilbert - 01 Jul 2008 18:51 GMT
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
> 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