Introduction

The consumers of our data wish to be able to make stock and financial decisions from our data. Turning raw data into valuable information is one of the fortes of Master Data Services. In this part of discussion we shall be looking at ways and means of extracting valuable information for our reports and queries.
Getting Started
The folks at the clothing store want maximum flexibility when it comes to data extraction and reporting. They have defined 6 parameters that they wish to set
1) Color
2) Size
3) Type
4) Fabric
5) Gender
6) Neck Style
A report such as this, with many permutations of filtering, requires that the multi-select property of the parameter lists be set to ‘True’. We shall discuss more about the parameters when we enter SQL Server Reporting Services.
Prior to any report construction, we first shall construct a SQL Server Stored Procedure to extract the data necessary for the reports.
In creating the code, I utilized a string splitting routine which I found on the internet (over a year ago) and which I have modified many times to suit my needs.
The link to the ORIGINAL routine is :
‘http://stackoverflow.com/questions/9678527/creating-table-variable-in-sql-server-2008-r2’
The code for our Stored Procedure may be seen below:

USE [AmsterdamRally]
GO

/****** Object: StoredProcedure [dbo].[MultiSelect] Script Date: 02/02/2014 06:18:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
--BEGIN
-- DROP TABLE #rawdata1
--END

--IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
--BEGIN
-- DROP TABLE #rawdata2
--END

--IF OBJECT_ID(N'tempdb..#rawdata3') IS NOT NULL
--BEGIN
-- DROP TABLE #rawdata3
--END

--IF OBJECT_ID(N'tempdb..#rawdata4') IS NOT NULL
--BEGIN
-- DROP TABLE #rawdata4
--END

--IF OBJECT_ID(N'tempdb..#rawdata5') IS NOT NULL
--BEGIN
-- DROP TABLE #rawdata5
--END

--IF OBJECT_ID(N'tempdb..#rawdata6') IS NOT NULL
--BEGIN
-- DROP TABLE #rawdata6
--END

Go

CREATE Procedure [dbo].[ClothingSales]
(
@Color VARCHAR(4000),
@Size VARCHAR(4000),
@Gender VARCHAR(4000),
@Type VARCHAR(4000),
@Fabric VARCHAR(4000),
@NeckStyle VARCHAR(4000)
)
AS
--declare @Size VARCHAR(4000)
--declare @Color VARCHAR(4000)
--declare @Type VARCHAR(4000)
--declare @Fabric VARCHAR(4000)
--declare @Gender VARCHAR(4000)
--declare @NeckStyle VARCHAR(4000)
--set @Size = '2,3,4,5,6,7'
--set @Color = 'Green,Blue,Red'
--Set @Type = 'Shirt,Dress,Pants'
--Set @Fabric = 'Cotton,Cotton Blend'
--Set @Gender = 'Male,Female'
--Set @NeckStyle = 'Crew,Collared'

--Declare re-cycleable variables
declare @Comma CHAR(1)
DECLARE @Position INT
DECLARE @Substringg VARCHAR(4000)
Set @Comma = ','

--Declare Table Variables
declare @SizeTable table
(
Size varchar(30) NOT NULL
)
--
declare @ColorTable table
(
Color varchar(30) NOT NULL
)
declare @TypeTable table
(
Type varchar(30) NOT NULL
)
declare @FabricTable table
(
Fabric varchar(30) NOT NULL
)

declare @GenderTable table
(
Gender varchar(30) NOT NULL
)

declare @neckStyleTable table
(
NeckStyle varchar(30) NOT NULL
)

BEGIN
--Process Color

SELECT @Position =1
IF (LEN(@Color)<1) OR @Color IS NULL RETURN
WHILE @Position!=0
BEGIN
SET @Position=CHARINDEX(@Comma,@Color)
IF @Position0
SET @Substringg=LEFT(@Color,@Position-1)
ELSE
SET @Substringg=@Color
IF(LEN(@Substringg)>0)
INSERT INTO @ColorTable(Color) VALUES(RTRIM(LTRIM(@Substringg)))
SET @Color=RIGHT(@Color,LEN(@Color)-@Position)
IF LEN(@Color)=0 BREAK
END

--Process Size

SELECT @Position =1
IF (LEN(@Size)<1) OR @Size IS NULL RETURN
WHILE @Position!=0
BEGIN
SET @Position=CHARINDEX(@Comma,@Size)
IF @Position0
SET @Substringg=LEFT(@Size,@Position-1)
ELSE
SET @Substringg=@Size
IF(LEN(@Substringg)>0)
INSERT INTO @SizeTable(Size) VALUES(RTRIM(LTRIM(@Substringg)))
SET @Size=RIGHT(@Size,LEN(@Size)-@Position)
IF LEN(@Size)=0 BREAK
END

--Process type

SELECT @Position =1
IF (LEN(@Type)<1) OR @Type IS NULL RETURN
WHILE @Position!=0
BEGIN
SET @Position=CHARINDEX(@Comma,@Type)
IF @Position0
SET @Substringg=LEFT(@Type,@Position-1)
ELSE
SET @Substringg=@Type
IF(LEN(@Substringg)>0)
INSERT INTO @TypeTable(Type) VALUES(RTRIM(LTRIM(@Substringg)))
SET @Type=RIGHT(@Type,LEN(@Type)-@Position)
IF LEN(@Type)=0 BREAK
END
--Process Fabric

SELECT @Position =1
IF (LEN(@Fabric)<1) OR @Fabric IS NULL RETURN
WHILE @Position!=0
BEGIN
SET @Position=CHARINDEX(@Comma,@Fabric)
IF @Position0
SET @Substringg=LEFT(@Fabric,@Position-1)
ELSE
SET @Substringg=@Fabric
IF(LEN(@Substringg)>0)
INSERT INTO @FabricTable(Fabric) VALUES(RTRIM(LTRIM(@Substringg)))
SET @Fabric=RIGHT(@Fabric,LEN(@Fabric)-@Position)
IF LEN(@Fabric)=0 BREAK
END
--Process Gender
SELECT @Position =1
IF (LEN(@Gender)<1) OR @Gender IS NULL RETURN
WHILE @Position!=0
BEGIN
SET @Position=CHARINDEX(@Comma,@Gender)
IF @Position0
SET @Substringg=LEFT(@Gender,@Position-1)
ELSE
SET @Substringg=@Gender
IF(LEN(@Substringg)>0)
INSERT INTO @GenderTable(Gender) VALUES(RTRIM(LTRIM(@Substringg)))
SET @Gender=RIGHT(@Gender,LEN(@Gender)-@Position)
IF LEN(@Gender)=0 BREAK
END

--Process neckStyle

SELECT @Position =1
IF (LEN(@NeckStyle)<1) OR @NeckStyle IS NULL RETURN
WHILE @Position!=0
BEGIN
SET @Position=CHARINDEX(@Comma,@NeckStyle)
IF @Position0
SET @Substringg=LEFT(@NeckStyle,@Position-1)
ELSE
SET @Substringg=@NeckStyle
IF(LEN(@Substringg)>0)
INSERT INTO @NeckStyleTable(NeckStyle) VALUES(RTRIM(LTRIM(@Substringg)))
SET @NeckStyle=RIGHT(@NeckStyle,LEN(@NeckStyle)-@Position)
IF LEN(@NeckStyle)=0 BREAK
END

select Color as [Color] Into #rawdata1 from @ColorTable
select Size as [Size] Into #rawdata2 from @SizeTable
select [Type] as [Type] Into #rawdata3 from @TypeTable
select Fabric as [Fabric] Into #rawdata4 from @FabricTable
select Gender as [Gender] Into #rawdata5 from @GenderTable
select NeckStyle as [NeckStyle] Into #rawdata6 from @NeckStyleTable

END
-- Return dataset to the report
select

,
[Cost],
[Retail Price],
[Safety Stock Level],
[Reorder Point],
[Design],
[Discontinued_Name],
[Available to Outlet_Name],
Color_Name,
Size_Name,
[Type_Name],
fabric_name,
gender_name,
[Neck Style_Name]
from [dbo].[MyProducts]
where color_name in (select color from #rawdata1)
and size_name in (select Size from #rawdata2)
and [Type_name] in (select Type from #rawdata3)
and Fabric_Name in (select Fabric from #rawdata4)
and Gender_Name in (select gender from #rawdata5)
and [Neck Style_Name] in (select NeckStyle from #rawdata6)

----Select * from #rawdata1
----Select * from #rawdata2
----Select * from #rawdata3
----Select * from #rawdata4
-- Select * from #rawdata5
-- Select * from #rawdata6

Starting Up Reporting Services
We start by opening SQL Server Reporting Services and by creating a new Reporting Services project which we shall call ClothingDSS.

Click New and Project (as seen above). The New Project creation screen will appear.

Note that I have selected Reporting Services in the drop down box on the left and to create the report NOT utilizing the wizard.  Being from the ‘Old School’ I do not utilize Report Builder nor the wizard, Sorry. Working without Report Builder provides added flexibility which most of us like to have.

As mentioned above, I call my Reporting Project “ClothingDSS”

Click OK to create your project.

For those new to Reporting Services, your working space should appear as shown in the screen dump below:

We start off by creating a Data Source.  For those new to the term Data Source, I like to think of a Data Sources as a garden hose attached to the wall of a house. When turned on, it will bring data from the database (house) to my Reporting Services project. The other end of the hose, I use to place the data into a dataset. This could be synonymous with a watering can, i.e. pouring the water from the hose into a watering can and watering the flowers from there.

Right click on ‘Shared Data Sources’ and then click on Add New Data Source as may be seen in the screen dump above. The Shared Data Source Properties box will appear.

We merely give the data source a name. In our case ‘ClothingDSS’. What we now need to do is to create the actual connection back to our database. Click the ‘Edit’ button next to the connection string text box. The ‘Connection Properties’ box will appear.

We now configure our connection and test the connection (as we have done in past).

Click OK to release the ‘Test Connection succeeded’ text box. Click OK to free the Connection Properties Box and OK AGAIN to save our connection string. Your screen should now appear as in the screen dump below:

Note the data source in the upper right hand corner.

Right click on ‘Reports’ and choose ‘add’ and ‘new item’ as shown above.

The ‘Add New Item’ data capture screen will appear.

Chose ‘Report’ option and call your report ‘Clothing’ (as shown above). Click add.

Our Design Surface is now exposed.

Our ‘construction area’ is within the rectangle. Let’s stretch it out  (see below)

Note that we have a lot of new options on the left hand side (parameters, Images etc.)

Should screen NOT show the Report Data menu, then click on VIEW (at the top) and click on the ‘Report Data’ menu. It should then appear where mine is.

Creating our first dataset

We shall start off creating our first data set. One for color. In total we shall eventually require seven datasets.

Right click on Dataset and select ‘Add Dataset’. We are going to create a ‘Color’ dataset.

The ‘Dataset Properties’ will appear. Change the name of the dataset from ‘DataSet1’ to Color. Then click the ’Use a dataset embedded in my report’ radio button. Your screen should now appear as shown below:

We now wish to ‘hook up’ the report to the database via our data connection. Click ‘New’ to the right of the ‘Data Source’ drop down.

In the name box we shall change the name of the data source from ‘DataSource1’ to ‘OurClothingDataSource’.
Click on the ‘Use shared data source reference’ radio button and click on the drop down box. You will see the data source that we have just created. Select it.
Your screen should now appear as shown below:

Click OK to complete the definition of our LOCAL Data Source. Please note that ‘OurClothingDataSource’ is only known within our small report. No other reports know about its existence. You will now find yourself back at the ‘DataSet’ screen.

We are now set to create our ‘Color’ dataset. Ensure that the TEXT radio button is selected as shown above. We are now going to add a bit of text in the text box (see below).

Click the ‘Refresh Fields’ button and then click on the ‘Fields’ tab in the upper left portion of this drop down box.

You will note that the ‘Color_name’ field is now shown within the box.

Click OK to close the ‘Fields’ box.

You will see that we now have a data set which will contain all of our colors KNOWN TO THE PRODUCTS WITHIN OUR TABLE.

It is left to the reader to create a similar dataset for Size, Type , Gender, Fabric and Neck Style. Please note that once we created the color dataset, that the ‘OurClothingDataSource’ will be available for you to utilize in creating the remaining 5 datasets.

After having completed creating the additional data sets, then your screen should appear similar to the one below:

Creating the parameters to be sent to the Store Procedure

We now wish to create the parameter drop down boxes, just like the ones shown below:

Right
click on the ‘Parameter’ tab and click ‘Add parameter’

The
“Report Parameter Properties’ box will appear.

Change the name to ‘Color’, the ‘Prompt’ to Color, and CHECK the ‘Allow multiple values’ box.

Next click on the ‘Available Values’ tab.

Click the ‘Get values from a query’ radio button. Select ‘color’ from the from the data set drop down box.

Select ‘color_name’ from the Value field and from the Label field. The click OK.

It is left to the reader to create the necessary parameters for the remaining 5 datasets.

At the end of this part of the process, your screen should appear as shown below:

Creating the final dataset: The data matrix which will hold our data

Once again, we create a new dataset in a similar fashion to what we have done above. This time however, instead of entering free form text, we shall select the ‘Stored Procedure’ option and select the Stored Procedure created with the code listing above.

Your screen should look approximately like the screen dump below:

Click refresh fields, below the ‘Select or enter stored procedure name’ drop down box. The ‘Define Query Parameters’ pop up box will appear.

Simply click OK. Click the ‘Parameters’ tab as shown below:

Note that the Parameter Name is entered HOWEVER not its value.

We need to enter it. Click the ‘fx’ beside the color parameter. The expression box will open. Enter the following for @Color   =JOIN(Parameters!Color.Value,",") See below.

It is left to the reader to complete the remaining 5 expressions changing the word ‘color’ for the respective parameter. Your completed screen should look like the one below:

Click OK to accept the parameter values.

Your screen should look like this.

Note that the fields in Clothing(1) are shown.

ONE CAVEAT, I have noticed at times, that the fields DO NOT populate automatically when one goes to the fields’ tab. (see below)

This is a ‘nasty’ and one has to manually enter the fields. See below:

Enter the field name.

While it is a pain, you will come right. The most important thing to note is that you must reset your parameter values to
=JOIN(Parameters!Color.Value,",")
=JOIN(Parameters!Size.Value,",")
=JOIN(Parameters!Type.Value,",")
=JOIN(Parameters!Gender.Value,",")
=JOIN(Parameters!Fabric.Value,",")
=JOIN(Parameters!NeckStyle.Value,",")

See the screen dump below:

Setting up the Matrix

Click on the SSRS toolbox to bring up the tool box.

Drag a Matrix control onto the screen. Ensure that the ‘Properties’ box is showing on the bottom right side of your screen.

Find the Dataset name property and set it to Clothing1 or whatever you called your dataset(See below).

For our example right click on the ‘Column Group’ drop down box (above this text).  Select ‘Delete Group Only’  (See below).

Now, double Click on the ‘RowGroups’. Set the grouping to be based upon ‘code’.

Click OK. We are now going to ‘fill in’ our data fields. Place ‘code’ in the first column of the first row as shown below.

When we do so, because the code is an integer, SQL Server Reporting Services want to SUM the column.

Simply right click on the field and follow the context menu as shown below.

Uncheck ‘Sum’

The “sum” should no longer be there. In the box next to [Code] click the ellipsis. You will note all the other fields.

Select Color and add Color. We now need to add additional field ‘boxes’ as the matrix only gave us two by default.

Right Click on the top margin of the matrix as shown above in dark grey. Select ‘Insert Column’ and ‘Right’ (see below):

An additional column is inserted. The placement of the remaining columns is left to the reader to complete.

After all is said and done, your screen should resemble something like this (below).

Note that I just added a text box at the top and inserted the words “Our Sample MDS Report”

Running our report

To run and test our report, simply click upon the preview tab. The drop down parameter boxes will appear. The important point to note, is the flexibility that you have and the many permutations that you can create.

I also show (above) the size drop down box. Note that I have selected all and note that this too will form a comma delimited string once we pass to the next parameter. Upon completion of choosing your parameter arguments, click view report and you are done!!  Amen!

Wrapping up

Today we have seen
1)      How to create a simple SQL Server Reporting Services report, to pull data from our Amsterdam database.
2)      We have developed a SQL Server Stored Procedure to parse a string of parameter arguments passed through by the user. Multi selects make your reports more flexible for the end user.
3)      You have seen how we construct our report, creating the parameter datasets, and then we created a data set devoted to the report matrix itself.

In the next part of this article, we shall be starting off in SQL Server Integration Services and we shall see how to create our daily load batch files to ensure that data is timeous loaded.

As always, should you have any questions, comments or concerns, please feel free to contact me at steve.simon@sqlpass.org

Happy Programming