Av rating:
Total votes: 45
Total comments: 34

 spDynamicCrosstab.sql
 spDynamicHTMLCrosstab.sql
 spDynamicHTML2000.sql
 send to a friend
 printer friendly version

There comes a time with many Database Developers charged with doing management reports when the process of doing it properly gets very tedious. By 'doing it properly', I mean the 'best practice' of having to do the basic reporting in SQL and relying on a front-end application to do the presentation. This is particularly true where the management want simple aggregate reports, or 'Pivot-table' reports. Presentation is so closely tied with the data that splitting the process can sometimes lead to more problems than it solves. Of course, we have Reporting Services, Analysis Services and other external tools, but there are times when a simple solution based in TSQL has the upper hand.

Anyone who was weaned on Excel knows that these pivot tables are dead simple. You select your data, bang the button and with a bit of dragging and dropping, there it is. Why, they ask, is it so hard to get it out of the database? Why so hard to make changes?

What they want to see is something like this (using NorthWind so those stuck with SQL 2000 can join in)

Now, you'll notice that we've taken a bit of trouble to add some formatting. In the average business, they're fussy about such things as the alignment of numbers and the clear delineation of totals, and summary lines. It also is easier on the eye. It is therefore handy to format pivot table reports in HTML. We can send them via email them, straight from SQL Server, all in their correct formatting, or put the reports on an intranet and update them daily or hourly. With the contents of this workbench we show you how it is done, and how easy it is to do, all without a .NET programmer in sight!

However, first things first, we must first show you how to do a crosstab, or pivot table in Transact SQL

Crosstabs and Pivot tables

The basic code to do the report is pretty simple.

You'll notice that the years are hard-coded into the column headings, which are a time bomb waiting to happen. You'll also realize that the all-important formatting is missing. The structure of the query seems slightly more complicated than necessary, but you'll see why soon. The PIVOT operator in SQL Server 2005 makes it rather easier but we wanted to make this relevant to the SQL Server 2000 users too

For any sort of portable solution that will work on SQL Server 2000, dynamic SQL is the traditional solution. Basically, the stored procedure generates the tedious code and then executes it. Why, one wonders. This is because there are a number of tweaks that have to be made, such as the order of the columns, and rows. After all, wouldn't someone want the report ordered by the number of sales of the salesman rather than just alphabetic order? The same basic query may generate a lot of different aggregations. Pretty soon, some sort of automation will be required.

Keith Fletcher contributed to Simple-Talk the ingenious but complex stored procedure that did cross-tabs in his excellent article Creating cross tab queries and pivot tables in SQL. Because we were awed by its grandeur, we didn't initially want to add our own contribution. However, we had two another objectives, firstly to show how easy the technique can be, and also because we wanted to do more, encouraging you to try things out, and secondly, because we wanted to show how one might mark up the presentation of the crosstab.

Here is a stored procedure that does the trick, along with some examples using NorthWind. (if you are stuck with SQL Server 2000, make the Varchar(MAX)s into Varchar(8000) and don't be too ambitious with the complexity of your crosstabs!)

You'll see that this is a developer's tool. It is easy to crash the procedure by putting in bad SQL. SQL Injectors would love it. No sir, this is a back-office report-generating tool. Let's try it out

EXECUTE spDynamicCrossTab
    @RowValue='ProductName',
    @ColValue='Year(OrderDate)',
    @Aggregate= 'ROUND(SUM(CONVERT(decimal(14, 2), OD.Quantity 
* ( 1 - OD.Discount ) * OD.UnitPrice)),0)',
    @FromExpression='FROM    [Order Details] OD,
        Orders O,
        Products P,
        Categories C
where   OD.OrderID = O.OrderID 
AND OD.ProductID = P.ProductID 
AND P.CategoryID = C.CategoryID',
   @Title ='Customers total orders per year'

-- change the line...     @RowValue='ProductName', 
--             ...to     @RowValue='CategoryName',
-- and see what happens!
/*-------------------------------------------------------------------

*/
--
-- add the row ...
-- @SortBy ='total desc', --what you sort the rows by (column heading)
-- before the @Title ='Customers total orders per year'-- Neat Huh?
--now change 
--    @RowValue='CategoryName',
--    @ColValue='Year(OrderDate)',
--to
--    @ColValue='CategoryName',
--    @RowValue='Year(OrderDate)',
--Instant Rotation!
--Now try this, and notice how we get the columns and rows in the right order
EXECUTE spDynamicCrossTab
    @colValue='DATENAME(year,orderDate)',
    @rowValue='DATENAME(month,orderDate)',
    @Aggregate= 'sum(subtotal)',
   @Rowsort='DATEpart(month,orderDate)',
    @FromExpression='FROM Orders 
   INNER JOIN "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
   inner join employees on employees.EmployeeID =orders.EmployeeID',
    @ColOrderValue='datepart(year,orderDate)',
   @Title ='Customers orders per month ',
   @sortby='max(sort) asc'
/*-------------------------------------------------------------------

HTML Crosstabs

Why bother with this when the Internet abounds with such Cross tab or pivot-table procedures? This is because we are going to take it one stage further so that, instead of just producing a resultset, we want to show how to produce HTML to produce the chart like the one at the beginning of the article.

To reproduce this...

...We need code like this (the first example in the body of the code was used to generate this Pivot-table)…

(n.b this is the SQL Server 2005 version. The SQL Server 2000 version is included with the files you can download in the speech bubble at the top of the article)

You will have noticed a few things here.

• The inputs are the same as the first stored procedure, spDynamicCrossTab. This means that you can try out your parameters in SSMS until you have things the way you want them and then you can just move to spDynamicHTMLCrossTab to concentrate on getting the presentation aspects as you want.
• We have separated the style from the code. All presentation is in an inline style block. This means you can change the way the crosstab looks to your heart's content.
• with both stored procedures, you can specify the order of both the columns and rows precisely (it is always nice to have the months, or the days of the week in the correct order!
• you can specify the units, either before(e.g. '£' or '$') or after (e.g '%') the aggregate values
• We provide you with an optional output variable so you can take the results and save it easily to a file, using the technique Phil described in his Blog 'Using BCP to export the contents of MAX datatypes to a file'

You can change the appearance of the crosstab simply by changing the inline style. For example, this ...

... will give you this

Lastly, although there is a lot more one can say about these procedures and the tricks one can use, especially with the CSS, here is an illustration of the way one might save the results of your crosstab to an HTML file for your management reporting intranet site. This uses Phil's technique taken from his blog entry Using BCP to export the contents of MAX datatypes to a file (Phil: Thanks for the plug, Robyn!)

So that's it. We've enjoyed ourselves trying things out, and we've been surprised how far we can take the dynamic creation of pivot tables. We suggest you take the workshop and explore the ideas. If you discover anything interesting, we'd love to hear your comments! We'd particularly like to hear of interesting CSS layouts, though displaying them on Simple-Talk will be very difficult.