Managing ‘exclude’ options in Reporting Services

Here’s a good way of making reports more useful by including extra options.

Sometimes, you will want to provide the option to exclude or include a particular set of data. For instance, when showing car sales over the last quarter for Jolly Roger’s Car Showroom, you may want to include second-hand sales, or specifically exclude them, looking only new car sales. (Assuming our sales table has a column for ‘SecondHandSale’)

This is a bit different for showing ONLY second hand sales or ONLY new sales: you want to show either both or just one sort.

You can achieve this in SQL using a CASE statement in your WHERE clause:

WHERE IsSecondHandSale =
CASE WHEN @ExcludeSecondHandSales THEN 0
ELSE IsSecondHandSale END

What you’re saying is that if second hand sales are excluded than the value in the IsSecondHandSale should be zero, otherwise it should be the same as it’s value. This will always be true, and so will ensure that all rows are returned.

This neat trick makes extending reports easy, and means you’re not duplicating reports just to show slightly different data.