SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 help with report parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 02/14/2013 :  14:48:55  Show Profile  Reply with Quote
hi team i am new to ssrs and i have created a simple report that have 2 parameters and i want to use parameters in serach criteria and want that if only one criteria is selected or no criteria is seleted the report displys data.

select id, name
from mbv_netrt
where id = parameter1
and name = parameret2


kindly help



challenge everything

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 02/14/2013 :  15:42:48  Show Profile  Reply with Quote
You can send a null (as the default value) and have it be an indicator that you don't need to filter by that parameter. For example:
select id, name 
from mbv_netrt 
where (id = @parameter1 OR @parameter1 IS NULL) 
and (name = @parameret2 OR @parameret2 IS NULL);
Are you using an adhoc query or using a stored proc? If you are using a stored proc, there may be performance implications in this type of "catch-all" queries.
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 02/18/2013 :  14:53:46  Show Profile  Reply with Quote
it works but why are you said that there is performance impacts.

challenge everything
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 02/18/2013 :  16:07:59  Show Profile  Reply with Quote
The potential performance impact is because of what they call "parameter-sniffing". I will point you to two articles that discuss the issue and workarounds:

http://www.sommarskog.se/dyn-search-2008.html
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000