Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Services / Reporting Services / July 2008

Tip: Looking for answers? Try searching our database.

Conditionally nullable parameters

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Barry Gilbert - 25 Jun 2008 19:51 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.