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