Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Master Data Services for the Beginner Part 06

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

[code][/code]

,
[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.

MDS for the beginner part601

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

MDS for the beginner part602

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”

MDS for the beginner part603

Click OK to create your project.

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

MDS for the beginner part604

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.

MDS for the beginner part605

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.

MDS for the beginner part606

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.

MDS for the beginner part607

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

MDS for the beginner part608

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:

MDS for the beginner part609

Note the data source in the upper right hand corner.

MDS for the beginner part610

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

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

MDS for the beginner part611

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

Our Design Surface is now exposed.

MDS for the beginner part612

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

MDS for the beginner part613

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.

MDS for the beginner part614

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

MDS for the beginner part615

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:

MDS for the beginner part616

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.

MDS for the beginner part617

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:

MDS for the beginner part618

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.

MDS for the beginner part619

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).

MDS for the beginner part620

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

MDS for the beginner part621

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

Click OK to close the ‘Fields’ box.

MDS for the beginner part622

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.

MDS for the beginner part623

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

MDS for the beginner part624

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:

MDS for the beginner part625

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

MDS for the beginner part626

The
“Report Parameter Properties’ box will appear.

MDS for the beginner part627

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

Next click on the ‘Available Values’ tab.

MDS for the beginner part628

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

MDS for the beginner part629

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:

MDS for the beginner part630

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:

MDS for the beginner part631

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

MDS for the beginner part632

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

MDS for the beginner part633

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

MDS for the beginner part634

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.

MDS for the beginner part635

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:

MDS for the beginner part636

Click OK to accept the parameter values.

Your screen should look like this.

MDS for the beginner part6377

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)

MDS for the beginner part638

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

MDS for the beginner part639

Enter the field name.

MDS for the beginner part640

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:

MDS for the beginner part641

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).

MDS for the beginner part642

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’.

MDS for the beginner part643

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.

MDS for the beginner part644

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

MDS for the beginner part645

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

MDS for the beginner part646

Uncheck ‘Sum’

MDS for the beginner part647

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

MDS for the beginner part648

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

MDS for the beginner part649

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

MDS for the beginner part650

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).

MDS for the beginner part651

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.

MDS for the beginner part652

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!

MDS for the beginner part653

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.