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 we're testing the @Subcategory and @Product parameters for null values. The COALESCE() function returns the first nonnull in the argument.
We can also use the ISNULL() function, which works like this: ISNULL (check_expression, replacement_value).
You might be debating which one to use, COALESCE() or ISNULL().
The difference between the two functions are widely documented and debated on various forums. Let it suffice to say that ISNULL() is not an ANSI standard, and thus, proprietary only to T-SQL.
Let's go back to our report.
After we modified the stored procedure to test null values, we then modify the subcategory and product parameters in our report:
- In the Report Data window, let's expand the
Parametersfolder and right-click on the Subcategory parameter then select Parameter Properties:
Clicking on the Parameter properties displays, you guessed it right, the Report Parameter Properties dialog box.
- Go to the General tab and then select the Allow null value checkbox, then click on OK.
- Repeat the same on the Product parameter and we're all set. Now, let's run our report with the optional parameters.
- Let's try the following values:
IsOnline: True (we want to know what items are sold online only, not including in-store) Category: Clothing Subcategory: NULL Product: NULL
We would come up with something like the following screenshot:
