Setting your predicate arguments at run time Part: 2 MultiSelect

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?’
As a follow-up and as promised (in the first portion of this saga), here is the solution that I utilized for a multi-selects scenario.

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:

Parameter01

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.

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:

use multiselect
go

–alter procedure Getdata
–(
–@Makes varchar(2000),
–@Colour varchar(2000),
–@Doors varchar(2000)
–)
–as
declare @Makes as varchar(2000)
declare @Colour as varchar(2000)
declare @Door as varchar(2000)
set @makes = ‘Chevy,Ford,Honda,Toyota,ford1’
set @Colour = ‘Green,Yellow,Red’
set @Door = ‘2,4,5’
declare @Makes2 as varchar(2000)
declare @Colour2 as varchar(2000)
declare @Door2 as varchar(2000)
declare @make   as table (Make varchar(50))
declare @colours as table (Colour varchar(50))
declare @doorss  as table (Doors varchar(50))
declare @delimiter   varchar(1)
set @delimiter = ‘,’
–Make
BEGIN
WHILE CHARINDEX(@Delimiter,@Makes,0) <> 0
Begin
set  @makes2 =RTRIM(LTRIM(SUBSTRING(@Makes,1,CHARINDEX(@Delimiter,@Makes,0)-1))) –From 1 to position of ‘,’ less 1
set  @Makes=RTRIM(LTRIM(SUBSTRING(@makes,CHARINDEX(@delimiter,@Makes,0)+LEN(@delimiter),LEN(@makes))))
–Above
–From postion of comma ‘,ford,chevy…’ to the end of string
IF LEN(@makes2) > 0
INSERT INTO @make SELECT @Makes2 –Place the substring into table variable
END
———————————————————–
IF LEN(@Makes) > 0
INSERT INTO @Make SELECT @Makes  — Flush the remaining buffer into the table variable.
–Colour
WHILE CHARINDEX(@Delimiter,@Colour,0) <> 0
Begin
set  @Colour2 =RTRIM(LTRIM(SUBSTRING(@Colour,1,CHARINDEX(@Delimiter,@Colour,0)-1))) –From 1 to position of ‘,’ less 1
set @Colour=RTRIM(LTRIM(SUBSTRING(@colour,CHARINDEX(@delimiter,@Colour,0)+LEN(@delimiter),LEN(@colour))))
–Above
–From postion of comma ‘,ford,chevy…’ to the end of string
IF LEN(@Colour2) > 0
INSERT INTO @Colours SELECT @Colour2 –Place the substring into table variable
END
———————————————————–
IF LEN(@Colour) > 0
INSERT INTO @Colours SELECT @Colour  — Flush the remaining buffer into the table variable.
–Doors
WHILE CHARINDEX(@Delimiter,@Door,0) <> 0
Begin
set  @Door2 =RTRIM(LTRIM(SUBSTRING(@Door,1,CHARINDEX(@Delimiter,@Door,0)-1)))
set @Door=RTRIM(LTRIM(SUBSTRING(@Door,CHARINDEX(@delimiter,@Door,0)+LEN(@delimiter),LEN(@Door))))
–Above
–From postion of comma ‘,ford,chevy…’ to the end of string
IF LEN(@Door2) > 0
INSERT INTO @Doorss SELECT @Door2 –Place the substring into table variable
END
———————————————————–
IF LEN(@Door) > 0
INSERT INTO @Doorss SELECT @Door  — Flush the remaining buffer into the table variable.
END
select * from cars where make in
(select * from @make)
and colour in (select colour from @Colours)
and NoOfDoors in (select Doors from @Doorss)

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

Parameter01Multi

Utilizing this technique within the Reporting Services world

In the discussion which 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:

Parameter02Multi

Note that three parameters that must be completed 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:

Make:

Parameter03Multi

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: Note that I have ‘checked’ ‘ Allow Multiple Values’.

Parameter04Multi

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/ stored procedure that we saw earlier within this article.

Parameter05Multi

NOTE:  This time around we shall be utilizing the stored procedure to extract the data. The code for the stored procedure may be found above. Note that we pass 3 string arguments to the three stored procedure parameters.

About the stored procedure

The astute reader will note that the strings passed to the stored procedure are comma delimited. This necessitates splitting the string, removing the commas and as we progress from left to right (in parsing the string), place the pertinent value(s) into a table variable.
e.g. ‘chevy,ford, honda’
This is achieved by use of a WHILE loop, removing the left most value with each iteration (using CHARINDEX() ) and placing this value into the table variable. This continues until we have parsed the whole string. (See the code above).
By the time we reach the end of each string, chance are that there is a last remaining item within the string buffer. We need to add this to the table variable as well.

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 now been enabled and should you chose this option, then all of the items that YOU chose from the dropdown box are placed into a comma delimited string, (which is the argument passed to the stored procedure).

Make (has been completed and is shown as a part of the screen dump immediately below)

No Of Doors is shown below

Parameter06Multi
..and colour
Parameter07Multi

The Final Report

All models, two and five doors that are either beige, blue or light grey are shown below.

Parameter08Multi

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!

Setting your predicate arguments at run time

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:

Parameter01

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:

Parameter02

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:

Parameter03

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.

Parameter04

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:

Parameter05

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.

Parameter06

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

Parameter07

Colour

Parameter08

The Final Report

4 door chevy’s  (all colours)

Parameter09

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!

Data Architcture VC Meeting November 14th 2013

Data Architecture VC
Meeting Thursday November 14th 2013

A special thanks to all of you who attended my presentation on Thursday. For those of you who sent emails after the event, a special thanks for the kind words and I hope that I have answered all your questions. Should there be any further questions, please do email me and perhaps we can work though your questions together.

As promised I have zipped up the presentation code, database and queries (please see the link below).

I shall be posting the DQS presentation tomorrow Sunday November 17th. Once again on my website.

DataArchitectureVC