Steve Simon

How to enhance your reports with SQL Server Reporting Services (SSRS)

November 18, 2014 by

Introduction

A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports.

Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort.

The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!!

SQL Shack carsexample

Getting started

Opening SQL Server Data Tools (hence forward referred to as SSDT) we begin by creating a Reporting Services Project call ‘Cars’. The project may be created in any directory that is convenient to you.

Create project

We click OK to arrive at our drawing surface. We first need to create a brand new ‘report’.

Create report

By right clicking on the Reports folder on the ‘Solution Explorer; we see the context menu (see above). We next click ‘Add’ and then ‘New Item’. The ‘new report’ menu is then brought up for you to see.

Adding new item

We choose the option ‘Report’ and give our report a name: ‘SQLShackCars’. The click Add.

Choosing report option

As the reader will note above, we now have a drawing surface, and an area to define and to create datasets parameters (upper left).

We must now create a ‘Shared Data Source’ to access our data.

A data source may be likened to a ‘water tap’ on a house. The report will be using the ‘water’ /data thus it is necessary to define where that ‘water’ / data resides.

I right click on ‘Shared Data Sources’ and the following context menu is displayed (see below top right).

Context menu

I choose ‘Add New Data Source’ from the context menu (see above). It should be noted from the outset that this data source (as it is shared) is for all intents and purposes a ‘GLOBAL’ data source. The ‘Shared Data Source Properties’ data entry screen is then displayed.

Data properties entry

Note that I have given the data source a name (see above) and the only step left is to define the ‘connection string’ to our database (where our report data resides). By clicking ‘Edit’ we arrive at the ‘Connection Properties’ data entry screen (see below).

Connection properties

I have taken the liberty completing the name of the connection string and point the string to the correct SQL Server instance and database.

We are now ready to create our first report.

I first want to increase the size of our drawing surface (see below).

Drawing surface

Setting our report parameters (Color, Number of Doors, Make)

As we want our report to be a flexible as possible, we must create a few ‘user selection’ parameters. These parameters will permit the user to pass ‘desired’ criteria to a stored procedure that will utilize these criteria to pull the correct data from the database table(s) and return the data to the report. This data will then be reflected in a data matrix.

Creating our ‘Color’ parameter

In order to create a parameter, we must ensure that our ‘Report Data’ window is visible. Should yours not be visible, then simply click on the ‘View’ Tab of SSDT and the option to show the ‘Report Data’ window may be found there. My screen looks as follows:

Adding parameters

I right click on the Parameters folder (in the upper left hand portion of the screen dump above) and choose ‘Add Parameter’.

The ‘Report Parameter Properties’ screen is then shown.

Report parameters

The astute reader will note that I have set the parameter name to ‘Color’, the prompt to ‘Color’, and more importantly the ‘Allow multiple values’ box has been checked.

For the time being I am just going to say ‘OK’ and close the parameter selection box. Fear not, we shall be returning to this parameter in a few minutes.

At this point in time your screen will appear as follows:

Created parameter

Note that we have created a ‘Color’ parameter which is highlighted in blue. It is left up to the reader to create the ‘Number of doors’ and the ‘Make’ parameters. They will be created in a similar manner as described above.

Our final parameters may be seen on the screen dump immediately below.

Final parameters

Creating our Datasets

When our data flows from the ‘house water tap’ through the connection ‘hose’, it must be placed in a ‘bucket’ that will be used when required by our report. This bucket is called a dataset.

The datasets that we shall be creating, will provide the parameters (that we have just created) with a complete list of colors, makes and number of doors that exist within our database table.

Creating the color dataset

To create our dataset we right click on the ‘Datasets’ folder and the following screen is brought up.

Creating data sets

We now click on ‘Add dataset’. Our next data entry screen is then displayed.

Adding dataset

We now give our dataset the name ‘Color’ and select the ‘Use a dataset embedded in my report’ radio button. In my case I chose to enter a simple SQL statement to pull all the colors that we have available (within the ‘cars’ table). Note that not all cars come in all colors nor do all makes have all the ‘number of doors’ that may be selected. I am merely trying to pull all possible combinations to have them available for the end user, knowing that some selections may return an empty dataset.

The contents of the Cars table may be seen immediately below.

Table content

Creating the LOCAL data source that will call for the data via its ‘Mother’, our global data source ‘SQLShackCars’ (within the dataset tab)

In order to obtain the necessary data we must now create a local data source which will communicate with the global data source which will pull the necessary data from the table.

Creating data source

Simply click on the ‘New’ tab shown above and the ‘Data source’ dialog box will open.

Data source dialog

I have once again taken the liberty of completing the necessary data fields. I called the local data source ‘SQLShack01’. I then selected the ‘Use shared data source reference’ tab and I selected our ‘SQLShackCars’ global data source.

I click OK, and OK to close out.

We can see our finished dataset in the screen dump below:

Finished dataset

In a similar manner we may create the ‘Number of doors’ and the ‘Make’ datasets UTILIZING the local data source (SQLShack01) that we just created above. This is left to the reader HOWEVER when you do go to create these datasets, you will find that the local data source (SQLShack01) is NOW available for you to use.

Our workspace with the three datasets may be seen below:

Datasets screen

Populating our parameter drop down lists

Returning to our ‘Color’ parameter, I double click on the ‘word’ Color and the parameter dialog box is displayed once again.

This time however I select the ‘Available Values’ tab.

Choosing values

Note that I have opted for the ‘Get values from a query’ radio button. By opening the ‘Dataset’ tab we note that the three datasets that we created in the steps above are present. Merely select ‘Color’.

Having selected ‘Color’ we can finalize our setup as shown below:

Final setup

The reader will note that I have selected Color for the ‘Label’ field and for the ‘Value’ field. I now click OK to accept and leave the parameter set up properties dialog.

It is left up to the reader to configure the ‘Number of doors’ and ‘Make’ parameters in the same manner as just described.

Taking it for a ‘test drive’

Alright, let us have a quick look at what we have thus far.

By clicking the ‘Preview’ tab we shall see that the parameter boxes have been populated.

Color

Color preview

Make

Make preview

And Number of Doors

Number of doors

Great!!!! Here comes the real fun!!!

Meanwhile back in SQL Server Management Studio

The most critical part of our report has yet to be created. This is the dataset that will hold our final results. To channel the data to our fourth and final dataset, we shall create a stored procedure that will accept three incoming parameters (Color, Make and number of doors). These parameters will be used within the predicate and will filter out records that do not match the search criteria.

To do this we create a new ‘query’ within SQL Server Management Studio.

Creating query

The reader will note that I have created the ‘header’ for our stored procedure (see above). You will also note that I have placed the query prototype within the code. BUT WAIT!! IF we were to pass through a single valued request such as where the color is ‘red’, the number of doors is ‘2’ and that the make was ‘Chevy’ then the predicate / where clause would be ‘where NumOFDoors = @NumOFDoors i.e. 2 and Make = @Make (Chevy) and Color = @Color (red)’

We would then be cooking with gas!

Unfortunately it is not that simple when comes to multiple selections.

Our plan of action

What is required, is to pass the stored procedure a COMMA DELIMTED STRING containing the arguments that the end user has selected. In other words should the user wish to see green, red and yellow cars then the argument value passed to the parameter would be GREEN, RED, YELLOW. We shall see how this is achieved when we return to our report project.

In the interim, we need to program our stored procedure to handle a comma delimited string and this is how I LIKE to achieve this task.

The code below accepts the value of @Color and splits the string of the color values, placing the values one by one into a table variable. Placing these values in a table variable permits us to iterate through the string stripping the string of the color that was just processed, shortening the string and doing so until the entire string is processed.

The effect of this piece of code is to convert ‘ Yellow, Red, Green’ into

Yellow

Red

Green

Note that in the last step I moved all the values from the table variable into a temporary table. I do this by habit as I may have to issue a GO statement further down within the code. Issuing a GO statement clears any table variables that may exist whilst temporary tables persist until the end of the code.

Move values

Placing this code snippet into our stored procedure we have now catered for the color parameter. It is left up to the reader to create the necessary code for the number of door and the make of car.

Looking at the data structure of our table we find the following:

Placing code snippet

Our finished stored procedure is similar to the one shown below:

Finished stored procedure

The actual final query is shown immediately below:

Once again, the astute reader will note that I have used inner joins as a predicate (i.e. the where clause). Now wait a minute!!! Why not use where color in ( ‘Yellow’, ‘Green’ etc). The reason being that one may ONLY HAVE 1000 “in’s”. Any more, say 1001 will cause a query to fail. This is true in SQL Server and Oracle. By using inner joins, the number of filter choices are virtually limitless.

REMEMBER THAT IF YOU ‘SELECT ALL’, it is quite possible for the number of values to exceed 1000.

Putting the pieces to talk to one another

Now that the store procedure has been created, we must create a fourth and final dataset within our reporting services project. This dataset will contain the client requested filtered data.

Back in our reporting services project we create a dataset which I call ‘FinalDataSet’ which will source its data from the return from the stored procedure (SQLShackCars1) that we just created. Once again we right click on ‘Datasets’ and select ‘Add dataset’. This opens the dataset properties data entry screen. Note that I have completed the stored procedure data capture box with the name of the stored procedure that we just created. The completed properties screen may be seen below:

Properties screen

Clicking on the ‘Fields’ tab (see the screen dump above upper left), we shall see that the fields that the query returns, are present on your screen(see below).

Fields tab

As we shall be passing a string (to the stored procedure) which potentially contains more than one value i.e. “Green, Yellow), we must change the format of the parameter values for Make, NoOFDoors and Color.

Right now they reflect as ….

Note the value [@Make], [@NoOFDoors] and [@color] (see above).

We modify [@Make], [@NoOFDoors] and [@color] to be

The change to ‘color’ is shown below. Make and NoOFDoors are processed in a similar manner.

Change processed

Back on our project surface, we drag in a MATRIX/TABLIX control from the tool box.

Toolbox

We set the ‘dataset name’ property of the control to FinalDataSet (see below lower right)

Dataset property

We leave the row grouping box untouched. The reason being, that we do not wish to aggregate the extracted values. Quite the contrary, we wish to view the entries as they are. (See below)

View entries

Next, right click on the ‘Column Grouping’ box. We click ‘Delete Group’ (see below)

Delete group

We are then asked if we want to delete the ‘Group and the data’ or just the Group. We check the ‘Delete group only’ option (see below).

Delete group only

We click OK to finish this task.

We are now in a position to populate our table cells.

Populating cells

Above you will see the list of fields returned from the stored procedure. I remove the ‘Rows’ grouping box and then I place ‘Make’ in the first text box, ‘NoOFDoors’, in the second and ‘Color’ in the last text box. Please remember that they fields may be in any order. Further, the header name for the fields may be modified to something more meaningful.

The final Matrix layout may be seen below:

Matrix layout

Testing and running our completed report

Once again, we click the preview button. Our parameter capture screen appears. We select all the colors (see below)

Selecting colors

Next we set the ‘Car Make’. We only wish to see “Fords”

Chosen attribute

And last but not least we set the number of doors to “Select All”

Selecting all

The result of this query may be seen below:

Query result

Thus we have completed our final report.

Conclusions

With development costs consistently on the rise and with more being demanded with fewer resources, we (as developers) must think outside the box and try to construct our reports etc. with a view to providing the end client with the best possible ‘user experience’. Often this requires making our reports more ‘all-encompassing’.

SQL Server Reporting Services combined with ‘Multiple Select’ parameters permit us to enhance our reports and provide users with this ‘ultimate experience’.

A last thought

The database and the Reporting Services package used in this article are available on request. They are complete, small and give you a feeling for what you could really achieve. Why not take them for a test drive?

Happy programming.


Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years.

Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.

Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.

View all posts by Steve Simon
Reporting Services (SSRS)

About Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years. Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally. Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor. View all posts by Steve Simon

387 Views