Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 help with report parameters

Author  Topic 

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-02-14 : 14:48:55
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 15:42:48
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

246 Posts

Posted - 2013-02-18 : 14:53:46
it works but why are you said that there is performance impacts.

challenge everything
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 16:07:59
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
   

- Advertisement -