Geeks With Blogs
Natasa Gavrilovic my Explorations in Coding

Recently I had to create couple SQL Server Reports (SSRS) with optional parameters built in. It took me a while to refresh memory how this can be done. It was very simple to create reports and processes behind, but connecting these two were are little bit challenging – stored procedure was tested and worked fine, but when the report was passing optional parameters it didn’t returned expected results. After tweaking SQL stored procedures and reports parameter options, the following approach turn to be the winning one.

1) Defining report parameters:

From Menu bar select ‘View’ and ‘Report Data’

Newly open window should have ‘Parameters’ folder display

Right click on this folder and select ‘Add new parameter...’

 

optionalParams_01

 

 

 

 

 

 

 

 

 



optionalParams_02

 

 

 

 



Default values need to be added from a query

optionalParams_03

 

 

 

 

 

 

 

 



A query values need to include ‘’ (empty string) – as highlighted

optionalParams_04

 

 

 

 

 

 

 

 


 

2) SQL stored procedure should have CASE statements inside WHERE

and it was the only way that a report was getting correct results back.

optionalParams_05

 

 

 

 



Posted on Wednesday, March 28, 2012 10:32 AM | Back to top


Comments on this post: SSRS optional parameters settings

# SSRS optional parameters settings
Requesting Gravatar...
Thanks natasa, this article helped me a lot
Left by Srikanth on Jul 04, 2013 7:38 AM

# re: SSRS optional parameters settings
Requesting Gravatar...

Thanks a lot
Left by rf on Jan 11, 2016 10:10 AM

# Cheap goods
Requesting Gravatar...
Only wanna tell that this is very useful , Thanks for taking your time to write this. cffeeakkfgkd
Left by Johne78 on Dec 06, 2016 6:47 AM

# cialis online pharmacy
Requesting Gravatar...
Hello! [url=http://www.cia9online.com/#2.html]cialis online pharmacy[/url]
Left by Pharmk700 on Dec 07, 2016 12:25 AM

# cialis online pharmacy
Requesting Gravatar...
Hello! cialis online pharmacy http://www.cia9online.com/#4.html
Left by Pharme637 on Dec 07, 2016 12:26 AM

# online pharmacy cialis
Requesting Gravatar...
Left by Pharmd330 on Dec 08, 2016 6:38 AM

Your comment:
 (will show your gravatar)


Copyright © Natasa Gavrilovic | Powered by: GeeksWithBlogs.net