Introduction

In our last two chats, we discussed enterprises that have had financial years that began in July and ended at the end of June. One of our clients works with this fiscal calendar and their financial folks are Excel “Fundi’s” (Fundisa is a Nguni word for “expert”). Many of their reports contain the current month’s sales, in addition, carrying running totals from the beginning of the fiscal year to date. An example may be seen in the screen dump below:

In today’s chat, we shall be concentrating on creating the necessary stored procedure to extract the data so that it may be placed in a Reporting Services matrix (similar to the Excel spreadsheet shown above). In doing so we are going to commit a SQL Server cardinal sin and utilize a small cursor. But then again, we are all adults and as such do not always do what we should. Not so?

Without further ado, let’s get started!

Getting started

SQL Shack financial is an organization that sells widgets to the public. Their fiscal year begins July 1 and ends June 30. Our data table contains a list of their sales from July 1 to present. Our task is to calculate the monthly income (on a monthly basis) and to have an additional column showing the year to date earnings.

We begin by having a look at our working data.

We note that within our data table there are four fields:

• Revenue
• Yearmth
• Orderdate
• Monthee

As our reporting is based on the concept of “a month”, the field “Orderdate” will only be utilized as a part of the predicate. “YearMth” however will be utilized as a grouping column.

Opening a new query window, we begin by declaring our stored procedure and a creating a few working variables (see below).

The reader will note that we declared two date variables (@BeginFiscal and @Endfiscal).

Additionally we declared twelve variables which will contain the values of “YearMth” for the twelve months of the current fiscal year. Finally we have a small start and end date routine that will ascertain the start and end date of the current fiscal year. See the code snippet below:

Now that with the necessary infrastructure in place, we shall calculate the values for our twelve monthly variables.

July is calculated as follows:

If we were to parse this SQL statement, ‘201407’ would be rendered (in February 2015).

The values associated with these 12 variables may be seen in the screenshot below:

The complete code listing may be seen in ADDENDA 1.

We are now in a position to calculate the monthly earnings (see below).

Looking at the screenshot above, we note the monthly sales PLUS some prepaid accounts for March through May (as seen in February 2015). The important point is that we have the monthly totals. The astute reader will note that I placed all the values into a temporary table called “#rawdata1”.

Coming from an Oracle world (cursor based), I am about to commit the most serious of SQL Server CRIMES. I am going to utilize a cursor upon the data within the temporary table (in order NOT to lock the proper database table). The reason being to calculate our running total.

In the screen dump above, we declare a table variable called @Revenue. This table variable contains three fields “Revenue1”, “Cumulative” and “YearMth” (see above).

Declaring our cursor

We are now in a position to create our cursor (see below).

We first declare three variables: @YearMth which will contain the “YearMth” value from the data. @Revenue which will contain the current month’s revenue figures. @Cumulative will be utilized to contain the running total of revenue for the current year (see above). We initialize @Cumulative = 0.

We are now in a position to declare our cursor (see above).

Creating our cursor body

We note from the code snippet above, that with each iteration through the body of the cursor, @Cumulative is incremented by the current value of @Revenue1. The current YearMth, the current month’s revenue, and the cumulative revenue are all then inserted into our table variable.

After that last row is retrieved @@FETCH_STATUS will no longer return a “0”, thus on the next pass, we break out of the loop.

We then close the cursor and deallocate it and place all the data from the table variable into a temp table called #rawdata2. Why? Hint: Persistence, especially with any “Go” statement which may be executed somewhere “further down” the code.

Running our query we obtain the desired results (see above).

Our current dilemma

The format in which the data has been rendered (see above), whilst conducive to a chart or graph, is not conducive to populating a Reporting Services matrix. This said we must massage the data utilizing a pivot construct (see below).

We begin by declaring an additional variable @NSQL nvarchar(2000). We shall want to execute a T-SQL statement within our code utilizing “exec sp_executesql” and for this to work, the statement must be in NVARCHAR format.

This said the reader will note that we construct a PIVOT SQL statement and assign this to the variable “NSQL”. Within the statement, we utilize the “YearMth” variables that we created above, in addition to utilizing the “YearMth” values from the table.

This done, we create an additional temporary table which we call #rawdata55.

Temporary table #rawdata55 will hold the pivoted data which will eventually feed our report matrix.

With this achieved, we are in a position to execute the SQL statement (contained within our “NSQL” variable) and load the data into our temporary table “#rawdata55”.

Executing the query (as is) renders the following data (see below).

Utilizing our query for reporting purposes

Bringing up SQL Server Data Tools, we are going to create a new Reporting Services project to display our data in a matrix format.

We give our “Report Server Project” the name “AggregationAndPivot” (see above). We click OK to continue.

Our first task is to create a “Shared Data Source”. Right-clicking on the “Shared Data Sources” folder, we select “Add” and then “Add New Data Source” from the context menu.

The “Shared Data Source Properties” dialog box is brought into view.

We give our “Shared Data Source” a name and point it to our “SQLShackFinancial” database. We then test the connection and find that all is in order (see above). We click “OK” to create our shared dataset.

As always, should you not be certain on how to create data sources, datasets and reports, please do have a look at my SQL Shack article entitled:

“Now you see it, now you don’t”.
/now-see-now-dont/

We are now in a position to create our one and only report for this “get together”. By right-clicking on the “Reports” folder. We select “Add” and “New Item” from the context menu.

We create a new report which we call “Matrix01”. We then click “Add”.

Once on our report drawing surface (see above), we right click upon the “Datasets” folder and select “Add Dataset” from the context menu (see above).

The “Dataset Properties” dialog is brought up and we give our new dataset the name “MatrixData”. We must now define a local data source. We click on the “New” button (see above and to the right).

The “Data Source Properties” dialogue box is brought up. We give our local data source the name “MatrixDataSource” and link it to our shared data source “MatrixDS” (see above). We click OK to leave the “Data Source Properties” box and find ourselves back on the “Dataset Properties” page.

We set our “Query type” to “Stored Procedure” and select the “SQLShackAggregation” procedure from the drop-down list (see above). This is the query that we just created, which is now being utilized as a stored procedure.

Having accepted our “SQLShackAggregation” stored procedure as our source of data, we click the “Refresh Fields” button (see above). We then click on the “Fields” tab.

We note that all of the fields that we added to our SQL query are now present in our dataset (see above). We click OK to leave the “Dataset Properties” dialogue box.

Our completed dataset may be seen on the left of the screenshot above.

We place a “Matrix Report Item” onto our report surface (see above).

and set its “DataSetName” property ( see above and to the bottom right).

We must now remove the column grouping which is achieved by right-clicking on the “ColumnGroup” and selecting “Delete Group” (see above).

As always, we are asked if we wish to “Delete group and related rows and columns” OR “Delete group only”. We select “Delete group only” (see above).

Arriving back on our report surface we add eleven additional columns to our matrix (see above).

We add the “Name” column (from our query, which is either the monthly or cumulative value) to the first column and then each month’s values to the ensuing columns (see above).

Tidying things up a bit and adding a bit of color, our report should be similar to the one shown below:

All numeric figures have been changed to currency and right oriented (see above).

We now double click upon the “Row Group” [name] as we must set the sorting of the rows.

The “Group Properties” dialog box is brought up (see above). We click the “Sorting” tab.

We set the sorting on the column “name” to be from “Z to A”. In other words “descending” (see above). We click “OK” to close the dialogue box.

Let us give it a run

Clicking the “Preview” button, we render our report. Note that two rows are rendered. The first, the monthly revenue and the second row, the year to date. This is exactly what we planned to achieve.

Conclusions

In today’s “Get together” we saw that extracting data in the correct format is not always that easy. Running totals are often required in reports and they are easy to achieve via looping or by utilizing a “SQL Server dirty word”, a cursor.

No matter which technique is utilized the data may still be in an in appropriate format and oft times to get our data into the correct format we must “pivot” of data.

This said we have seen how easily our data may be pivoted.

This brings us to the end of today’s “get together”. I hope that I have generated more questions than answers.

As always, should you have any questions or concerns OR wish to obtain a copy of the code and applications, please do feel free to contact me.

In the interim, happy programming!

ADDENDA 1 (Stored Procedure listing)

• Author
• Recent Posts

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

Latest posts by Steve Simon (see all)

• Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016
• How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016
• How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016