Reporting in SQL Server - Manipulating the data via the grouping functions

Introduction

In many of our past “get togethers” we have discussed pivoting raw data in order to best represent corporate data within our business reporting. As we may remember, in many instances we pivoted the data within the report query.

In today’s “fire side chat” we shall be discussing how we may achieve the same effect with the report controls, by manipulating the data via the grouping functions (provided by the Tablix control). In other words, we shall be discussing how we may go from this

to this…

Without further adieu, let us get started.

Getting started

We begin with a small sample of data representing the revenue from two SQL Shack corporate divisions. As most folks in the financial world prefer spreadsheets, we received the necessary data in spreadsheet format (see below).

The data above represent the revenue for 2015, month by month.

Our first task is to load the data into SQL Server.

Having completed the SQL Server load (see above), our next task is to create an additional column that will display the calendar month for any given “YearMth”. As an example, for 201501 we would expect to see “Jan”.

As the reader may have noted, in a previous article I created a user function that accepts a year and month combination and returns the calendar month name. The function is named “Monthee” and the code may be found in Addenda 1.

The function “Monthee” may be seen above.

Having now met “Monthee”, we shall use the function in our report query.

Our report query may be seen above. In this query we wish to extract the company name, the year and month combination, the calendar month name and the sales figures (“Amount”). Having created the necessary code, we proceed to create a stored procedure (which we shall utilize within our report).

Creating our report

Opening Visual Studio 2015 or SQL Server Data Tools 2010 or later, we create a new Reporting Services project (see below).

We give our new “Report Server Project” a name (see above).

Upon creating our project we find ourselves on our design surface (see above).

Our first task is to connect to our relational database “SQLShack”.

We right click upon the “Shared Data Source” folder and select “Add New Data Source” from the context menu (see above).

The “Shared Data Source” properties dialogue box appears. We give our “Shared Data Source” a name and click upon the “Edit” button to create the necessary “Connection String” (see above).

The “Connection Properties” dialogue box appears. We tell the system to which server the connection should be made, in addition to providing the name of the relevant database. This achieved we test the connection (see above). We click “OK”.

Clicking upon the “Credentials” tab, we accept the default option to utilize Windows Authentication to verify that the user is entitled to view the data (see above). Finally we click “OK” to leave the “Shared Data Source Properties” dialogue box and we find ourselves back upon our design surface.

Adding a new report to our project

We right click upon the “Reports” folder to bring up the related context menu. We select “Add” and “New Item” (see above).

We select “Report” and give our new report the name “PivotTablix1” and click “Add” (see above).

Our report design surface appears (see above).

Creating a local dataset

Our next task is to create a local data set.

We right click upon the “Datasets” folder (above) and bring up the context menu. We select “Add Dataset” (see above).

The “Dataset Properties” dialogue box opens. We give our local dataset the name “PivotTablixDS” and select “Use a dataset embedded in my report” (see above). We must new click the “New” button to create a new “local data source” which shall be utilized to connect to the shared data source that we created above. As I have mentioned in other articles, the advantage of utilizing local datasets and local data sources are that we can customize them for this one particular report and yet not affect the shared data source, considering that a Reporting Service project will probably have a myriad of different reports.

Our configured “Local Data Source” may be seen above. We click “OK” to leave the dialogue box.

We find ourselves back within the “Dataset Properties” dialogue box. We click the “Stored Procedure” Query type and select the stored procedure that we created within SQL Server Management Studio (see above). We click “OK” to continue.

Whilst we now have a connection through to the stored procedure, we much now inform our dataset of which data fields will be pulled. We click the “Refresh Fields” button (see above).

Clicking upon the “Fields” tab, we note that the fields within our stored procedure are now are visible to our report (see above). We click “OK” to leave the “Dataset Properties” dialogue box.

When returned to our drawing surface, we cannot help but notice that the dataset that we just created is now clearly visible (see above).

Creating our data display

Our next task is to “drag” a “Matrix” from the toolbox and place it on our drawing surface.

The “Matrix” may be seen above.

Clicking upon the “Matix/Tablix” we bring up the “Tablix1” property and assign the dataset which we just created (see above).

Now that the dataset has been allocated to the “Matrix / Tablix”, we are now able to “Add” our desired columns to the “Matrix / Tablix”. We add “Company” and the “Amount” field (see above).

The important point to note is that in this case we shall utilize the “Column Grouping” which in past exercises we have removed.

Setting the “Column Grouping” criteria

We commence by right clicking upon the “ColumnGroup” tab (see below). The context menu appears.

We select “Group Properties”.

We add “Monthee” as the “Group on” field (see above).

Now here is where the “real trick” appears. We must now set the “Column Sorting” which we shall do with the “YearMth” field. The astute reader will remember that “YearMth” contained the year and month of the revenue record.

Setting the “Row Grouping” Criteria

This time however we right click upon the “RowGroup” tab. The “RowGroup” context menu appears.

We opt to “Group on” Company (see above). We click OK to leave the “Group Properties” dialogue box.

We find ourselves back upon the design surface and have on last (however extremely important) task to do. We must replace the “Amount” field shown above with the “Monthee” field (see below).

This change will permit the month names to be displayed as the column header.

We note the effect of the change above.

Let us give our report a spin!

We click upon the “Preview” tab and our report is brought into view. We note that instead of the initial data format that we placed into our SQL Server table, that our data is now displayed in a format that starts with January and ends with December (not shown). This is all possible as we utilized the year and month combination as the sort field (see above).

Now it is blatantly obvious that we need to perform a tad of formatting to “pretty up” our report.

Formatting our report

Back within design view we begin by formatting our “Title” line.

We set the “BackGroundColor” to Khaki (see above).

We note that our “Title or Column Header” line now has a Khaki coloured fill.

We set the “Data Row” background to “LightGray”.

Our modification may be seen above. Further, we shall ensure that the “Monthee” and “Amount” columns are “Right” aligned.

Our last task is to format the financial amounts so that they represent dollar values.

Highlighting the “Sum(amount)” text box and right clicking upon it (see above), we bring up the context menu. We select the “Text Box Properties” option (see above).

The “Text Box Properties” dialogue box appears. We select “Number” from the upper left portion of the box and set the value to “Currency” and set the decimal places to 0. We also tell the system that we wish to have negative values represented with a minus sign (see above). We click “OK” to leave the “Text Box Properties” dialog box.

Clicking the “Preview” button once again we see our completed report.

Conclusions

Once again, we have arrived at the end of another “get together”. In past exercises we have always pivoted report data within our query prior to populating our report dataset.

In our current chat, we have seen how this pivoting may be achieved via the report controls, in a most efficient and effective manner.

As always, should you have any questions pertaining to what we have just discussed or for any other “fireside chat” for that matter, please do feel free to contact me.

Happy programming and all the best!

Addenda 1

USE [SQLShack]

/****** Object: UserDefinedFunction [dbo].[Monthee] Script Date: 10/17/2016 8:56:33 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create function [dbo].[Monthee]

(

@YearMth as varchar(6)

)

RETURNS Varchar(6)

BEGIN

declare @Return varchar(6)

Begin

Set @return =

case when right(@YearMth,2) = '01' then 'Jan'

when right(@YearMth,2) = '02' then 'Feb'

when right(@YearMth,2) = '03' then 'Mar'

when right(@YearMth,2) = '04' then 'Apr'

when right(@YearMth,2) = '05' then 'May'

when right(@YearMth,2) = '06' then 'Jun'

when right(@YearMth,2) = '07' then 'Jul'

when right(@YearMth,2) = '08' then 'Aug'

when right(@YearMth,2) = '09' then 'Sep'

when right(@YearMth,2) = '10' then 'Oct'

when right(@YearMth,2) = '11' then 'Nov'

when right(@YearMth,2) = '12' then 'Dec' else 'XXX' end

end

RETURN(@return)

end

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