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...’

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Default values need to be added from a query

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

2) SQL stored procedure should have CASE statements inside WHERE

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