help with report parameters - SQL Server Forums

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.

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources