One of the most common business requirements for SQL Server Reporting Services is that an SSRS Report should be dynamic. Dynamic in a sense that the report consumer can query the report in a variety of ways. In this post, I will demonstrate how you can add a parameter value “ALL” to return all of the desired result.

I have four parameters in this report; all of them are optional (can be NULL). I will demonstrate the optionality in a different post. For the mean time, let me demonstrate how to add “ALL” in a parameter.

My parameter, which we alias here as “param1”, is optional. Here is how I created my statement in TSQL and wrapped it in a Stored Procedure:

As you can see, a simple “Union ALL” statement can do the job. I can include NULL value in param1 because that parameter is meant to…