Introduction

A few days ago, I did a presentation for a Virtual Chapter meeting where I discussed SQL Server Data Access Layers. The user obtained his or her data by submitting an argument to a parameter. In our case, there were three of these parameters, thus requiring 3 different arguments.

During the presentation I received the same question, numerous times, ‘what happens if I wish to specify parameters 1 and 3, and yet see all of the available values for parameter 2?’  The Data Access Layer concept actually catered for this type of scenario and this is what I shall be discussing below:

Scenario:

We have a car dealership which has a number of cars, makes and each car may have different number of doors.
The car table may be seen in the following screen dump:

The question  becomes:
Assuming I wish to make any query as flexible as possible, so that I may use the 'make', 'NoOfDoors', and 'colour' as parameters (to any report), how do I structure a generalized query to cater for all scenarios.

As an example:

Some folks may be looking for green cars only.
Others: Chevys with 4 doors, regardless of colour.
Others: Only 4 door cars regardless of make and colour.

I believe that the austute reader picks up on the challenge.
The code behind this type of challenge may be seen below.
Based upon this query and the data within our database table, the query above produces the following data:

use MultiSelect
go

declare @make varchar(55)
declare @colour varchar(55)
declare @noOfDoors as varchar(4)
set @Make = '_ALL'
Set @Colour = '_All'
Set @noOfDoors = '4'
select Keyy, Make, NoOfDoors, Colour, Price from [dbo].[Cars]
where
(1 = (case when @Make = '_All' then 1 else 2 end) or (@Make = make))
and
(1 = (case when @Colour = '_All' then 1 else 2 end) or (@Colour = Colour))
and
(1 = (case when @NoOfDoors = '_All' then 1 else 2 end) or (@NoOfDoors = NoOfDoors))

Based upon this query and the data within our database table, the query above produces the following data:

The trick is found within the syntax of the WHERE clause. When we look at our Reporting Services Project (discussed below) , the use of ‘_All’ will become apparent.
Please note, should ‘_All’  be selected for ‘make’ then 1 = 1 which provides a Boolean ‘True’ mask, thus 'make' is NOT used by the query as a filter. As 1 does in fact = 1 then the second portion of the ’OR’ clause  is NOT parsed nor evaluated. This is the reason that this mechanism works.
The same holds  true for the ‘Colour’.

The NoOFDoors on the other hand has been set to 4 (in our example). Using the logic described above, the first portion of the NoOfDoors 1 <> 4 fails and thus the SECOND portion of the “OR” clause is evaluated and causes only records with 4 doors to be pulled. Please note we could have set its value to ‘_All’ as well with the result that all the table data would be rendered.

Utilizing this technique within the Reporting Services world

In the discussion that follows, I set up a small SQL Server 2012 Reporting Services project for you to see how this technique may be applied within a production environment. The finished project may be seen below:

Note the three parameters that must be selected in order to populate the matrix.

To start off, we shall create four datasets.  Three of which will hold distinct makes, colours and number of doors. The fourth dataset will be used for the result set. The ‘make’ dataset is constructed as shown below:

Why does the ‘_ALL’ option in the ‘Union all’  have an underscore in front of ‘All’. This is to ensure that it is ALWAYS the first value in the drop down, no matter the sorting of the second SQL Statement.

The Colour and NoOfDoors datasets are constructed in a similar manner.
We now create the three parameters to be used to pull our data to the main data matrix. The ‘make(s)’ parameter is shown below:

The NoOfDoors and Colour parameters are defined in a similar manner.
Thus Make pulls its distinct values from the Make(s) dataset.
Colour pulls its distinct values from the colour dataset.
NoOfDoors pulls its distinct values from the NoOfDoors dataset.

Setting up the fourth dataset: Inventory

The fourth dataset and the most important dataset is set up with the query that we saw earlier within this article.

NOTE: The results pulled into this dataset will depend upon whether a distinct value has been selected or whether the wild card, ‘_All’ has been selected.
OK, for the ‘Wise Acres’ out there that ask ‘What happens if a value is null?’
(1 = (case when @make is NULL then 1  else 2 end)  OR  (@make = make))

Running our report

When the report is run, we note that the three parameter drop down boxes now contains the distinct values for make(s), colour and number of doors (See the screen dump below).

MULTI SELECT has not been enabled AND I shall be covering this in my next article. This involves setting up a table variable and using CHARINDEX(). Very simple, HOWEVER I wanted to get the ‘simple version’ out for you to digest.

Make

Colour

The Final Report

4 door chevy's  (all colours)

Conclusion:

Reporting, utilizing flexible parameters which are set at run time is easily implemented.  This gives our end users the ultimate flexibility and eliminates the need to have varied flavours of the same report.

Should you wish a copy of the database and code that I used for this article, please contact me at steve.simon@sqlpass.org

Happy programming!