O'Reilly logo

SQL Server 2012 Reporting Services Blueprints by Mickey Stuewe, Marlon Ribunal

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Making a parameter optional

Another common requirement on parameterized reports is the ability to exclude one or more parameters at runtime. For example, we want to see all the sold items online under the accessories category without going further down to subcategory and product.

In this example, let's make subcategory and product optional. We need to modify our stored procedure first, as done in the following expression:

Select …
From …
Where Subcategory  = COALESCE(@Subcategory, Subcategory)
      AND Product = COALESCE(@Product, Product)

Please take a note that the fields may have different names in the code included with the book. The previous code is for clarity purpose only. Modify the codes accordingly.

Basically, what we're trying to do here is ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required