Demystifying SQL Server: T-SQL Tuesday #005 – Creating & Emailing HTML Reports

Tuesday, April 13, 2010

T-SQL Tuesday #005 – Creating & Emailing HTML Reports

This post is my contribution to the popular TSQL Tuesday event, http://sqlvariant.com/wordpress/index.php/2010/04/t-sql-tuesday-005-reporting/. The creator of this amazing event is Adam Machanic. What I love most about this event is how it brings the SQL Server Community together. The “theme” for this TSQL Tuesday is reporting. As you aware, reporting is a very broad topic. I will be focusing on creating and emailing HTML reports. Now this process is no substitute for a SSRS report or a cube report. What I am about to show you is a very sleek way of presenting data to managers at a very high level. You do not want to send an entire report as a HTML report, so this process should be limited to dashboards or reports that are small in nature. If the user needs more detail , or is simply requesting too much data, you may want to add a detail link in the HTML body, as this gives the user the ability to drill through for more detail.

Let’s get started by creating a sample table and a couple of views. It should be noted that this process will primarily utilize views to expose data. This code can be further expanded to filter for specific columns, but as it stands now…. this process returns all columns in the view or table.

USE [tempdb]
GO

SET NOCOUNT ON;
GO

IF OBJECT_ID('tempdb.dbo.Sales') IS NOT NULL
BEGIN
    DROP TABLE dbo.Sales;
END
GO

CREATE TABLE dbo.Sales(
SalesId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmployeeId INT,
Amt NUMERIC(9,2),
LocationCd INT
);
GO

INSERT INTO dbo.Sales VALUES (1,12.50,1);
INSERT INTO dbo.Sales VALUES (1,99.99,4);
INSERT INTO dbo.Sales VALUES (2,45.64,1);
INSERT INTO dbo.Sales VALUES (3,44.65,2);
INSERT INTO dbo.Sales VALUES (3,52.89,4);
INSERT INTO dbo.Sales VALUES (4,250.54,3);
INSERT INTO dbo.Sales VALUES (5,150.00,5);
GO

IF OBJECT_ID('tempdb.dbo.vw_SalesVolumnByLocation') IS NOT NULL
BEGIN
    DROP VIEW dbo.vw_SalesVolumnByLocation;
END
GO

CREATE VIEW dbo.vw_SalesVolumnByLocation
AS
SELECT LocationCd, SUM(Amt) AS SalesVolume
FROM dbo.Sales
GROUP BY LocationCd
GO

CREATE VIEW dbo.vw_SalesBySalesCounselor
AS
SELECT [EmployeeId],[LocationCd],[Amt]
FROM dbo.Sales
GO

Next, the stored procedure. First and foremost this code looks a lot worse than than it really is. I had to use dynamic SQL because I did not want to have to create this stored procedure in every database.

The parameter list is pretty massive, but a lot of the parameters have default values, which means you do not have to specify anything. The parameter are pretty self explanatory.

USE [master]
GO

CREATE PROCEDURE usp_Email_HTML_Rpt
    @DB VARCHAR(255) = NULL,
    @Object VARCHAR(255),
    @Schema VARCHAR(255),
    @Rec NVARCHAR(255),
    @CC NVARCHAR(255) = NULL,
    @rpt_Header VARCHAR(50),
    @rpt_Header_BGColor VARCHAR(10) = '#FFFFFF',
    @TblHdr_BGColor VARCHAR(10) = '#FFFFFF',
    @Condition1_Col VARCHAR(255) = NULL,
    @Condition1_Expression VARCHAR(500) = NULL,
    @Condition1_BGColor VARCHAR(10) = NULL,
    @Condition2_Col VARCHAR(255) = NULL,
    @Condition2_Expression VARCHAR(500) = NULL,
    @Condition2_BGColor VARCHAR(10) = NULL,
    @AltRowBGColor VARCHAR(10) = NULL,
    @Pred_Filter1_Col VARCHAR(255) = NULL,
    @Pred_Filter1_Expression VARCHAR(500) = NULL,
    @Pred_Filter2_Col VARCHAR(255) = NULL,
    @Pred_Filter2_Expression VARCHAR(500) = NULL,
    @OrderBy VARCHAR(500) = NULL
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX),
        @StyleSheet VARCHAR(MAX),
        @RtnSQL NVARCHAR(MAX),
        @html_email NVARCHAR(MAX)
                
DECLARE @HTML TABLE(seq TINYINT, Tag VARCHAR(MAX));

--Create a new style sheet if none was passed in
IF @StyleSheet IS NULL
BEGIN
--Set the Procedure Stylesheet.  You can also supply this as a variable
SET @StyleSheet = 
'<head>
 <style type="text/css">
     th {width:150px;color:"#FFFFFF";font-weight:bold;background-color: ' + QUOTENAME(COALESCE(@TblHdr_BGColor,'#FFFFFF'),'"') +';border:1;border-width:thin; border-style:solid; align:center}
    td {width:150px;background-color: "#FFFFFF"; border: 1; border-style:solid;border-width:thin; text-align: "left"}
    td.Cond1Met {width:150px;background-color: ' + QUOTENAME(COALESCE(@Condition1_BGColor,'#FFFFFF'),'"') +'; border-style:solid;border-width:thin; text-align: "left"}
    td.Cond2Met {width:150px;background-color: ' + QUOTENAME(COALESCE(@Condition2_BGColor,'#FFFFFF'),'"') +'; border-style:solid;border-width:thin; text-align: "left"}
     td.AltRowColor {width:150px;background-color: ' + QUOTENAME(COALESCE(@AltRowBGColor,'#FFFFFF'),'"') +'; border: 1; border-style:solid;border-width:thin; text-align: "left"}
    td.LegendCond1Met {width:200px;background-color: ' + QUOTENAME(COALESCE(@Condition1_BGColor,'#FFFFFF'),'"') +'; border-style:solid;border-width:thin; text-align: "center"}
    td.LegendCond2Met {width:200px;background-color: ' + QUOTENAME(COALESCE(@Condition2_BGColor,'#FFFFFF'),'"') +'; border-style:solid;border-width:thin; text-align: "center"}
     th.LegendHdr {width:200px;color:"#FFFFFF"; font-weight:bold; background-color: ' + QUOTENAME(COALESCE(@rpt_Header_BGColor,'#FFFFFF'),'"') + ';border: 1;border-width:thin; border-style:solid;text-align: "center"}
    td.Legend {width:200px;background-color: "#FFFFFF"; border: 1; border-width:thin; border-style:solid; text-align: "center"} 
    th.LegendTitle {width:200px;color:black;background-color: "#C0C0C0"; border: 1; border-width:thin; border-style:solid; text-align: "center"} 
 </style>
<title>' + COALESCE(@rpt_Header,'Report Header') + '</title>
</head>
'
END 

--Build basic html structure
INSERT INTO @HTML (seq,Tag) 
VALUES (1,'<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">' + CHAR(13) + '<html>' + COALESCE(@StyleSheet,'') + '<body>');

--If optional conditions exist, build a legend
IF @Condition1_Col IS NOT NULL OR @Condition2_Col IS NOT NULL
BEGIN

INSERT INTO @HTML (seq,Tag) 
SELECT 2, '<table border="1" align="LEFT">' UNION ALL
SELECT 3, '<tr><th class="LegendTitle"COLSPAN=3>Legend</th></tr>' UNION ALL
SELECT 4, '<tr><th class="LegendHdr">Variable</th><th class="LegendHdr">Condition Column</th><th class="LegendHdr">Condition Expression</th></tr>' UNION ALL
SELECT 5, '<tr><td class="Legend">@Condition1</td><td class="Legend">' + COALESCE(@Condition1_Col,'n/a') + '</td><td class="LegendCond1Met"> ' + COALESCE(@Condition1_Expression,'n/a') + '</td></tr>' UNION ALL
SELECT 6, '<tr><td class="Legend">@Condition2</td><td class="Legend">' + COALESCE(@Condition2_Col,'n/a') + '</td><td class="LegendCond2Met"> ' + COALESCE(@Condition2_Expression,'n/a') + '</td></tr>' UNION ALL
SELECT 7, '</table><br><br><br><br><br><br><br>' + '<h1>' + COALESCE(@rpt_Header,'Report Header') + '</h1>' + '<table border="1" align="left" width="25%">'

END
ELSE
BEGIN --No legend is needed, start building the table
    INSERT INTO @HTML (seq,Tag) 
    SELECT 8, '<br>' + '<h1>' + COALESCE(@rpt_Header,'Report Header') + '</h1>' + '<table border="1" align="left" width="25%">'
END

--Create Table Header
SET @sql = N'
SELECT 9,CAST(
    (
    SELECT CAST(''<th>'' + COALESCE(c.COLUMN_NAME,'''') + ''</th>'' AS XML)
    FROM ' + COALESCE(QUOTENAME(@DB) + '.','') + '[INFORMATION_SCHEMA].[COLUMNS] c
    WHERE c.[TABLE_NAME] = @dynObject AND c.[TABLE_SCHEMA] = @dynSchema
    FOR XML PATH(''''),ELEMENTS,ROOT(''tr''),TYPE
    ) AS VARCHAR(MAX))';
    
INSERT INTO @HTML (seq,Tag) 
EXEC sp_executesql @sql, N'@dynObject VARCHAR(255),@dynSchema VARCHAR(128)',@dynObject = @Object, @dynSchema=@Schema

    
--Create SQL Statement to return actual values
SET @sql = N'
SELECT 
    @dynRtnSQL = ''SELECT 10,''''<tr>''''+'' + STUFF(
    (
        SELECT 
            ''+  CASE '' +
                 COALESCE(''WHEN '' + QUOTENAME(@dynCondition1_Col) +  @dynCondition1_Expression
            + '' THEN  ''''<td class="Cond1Met">'''' + CAST('' + QUOTENAME(c.COLUMN_NAME) + '' AS VARCHAR(MAX))'','''')
            +     COALESCE('' WHEN '' + QUOTENAME(@dynCondition2_Col) +  @dynCondition2_Expression
            + '' THEN  ''''<td class="Cond2Met">'''' + CAST('' + QUOTENAME(c.COLUMN_NAME) + '' AS VARCHAR(MAX))'','''')
            + '' WHEN ''''1''''= CASE WHEN ROW_NUMBER() OVER(ORDER BY ' + COALESCE(@OrderBy,'(SELECT NULL)') + ') % 2 = 0 THEN 1 ELSE 0 END''
            + '' THEN  ''''<td class="AltRowColor">'''' + CAST('' + QUOTENAME(c.COLUMN_NAME) + '' AS VARCHAR(MAX))''
            + '' ELSE ''''<td>'''' + CAST('' + QUOTENAME(c.COLUMN_NAME) + '' AS VARCHAR(MAX))''
            + '' END''
            + '' + ''''</td>''''''
        FROM ' + COALESCE(QUOTENAME(@DB) + '.','') + '[INFORMATION_SCHEMA].[Columns] c
        WHERE c.[TABLE_NAME] = @dynObject AND c.[TABLE_SCHEMA] = @dynSchema
        FOR XML PATH(''''),TYPE
    ).value(''.'',''VARCHAR(MAX)'')
    ,1,1,'''') + ''+''''</tr>'''' FROM '  + COALESCE(QUOTENAME(@DB) + '.','') +  ''' + QUOTENAME(@dynSchema) + ''.'' + QUOTENAME(@dynObject) +
    ''WHERE 1=1 ' + COALESCE(' AND' + QUOTENAME(@Pred_Filter1_Col) + SPACE(1) + @Pred_Filter1_Expression,'') + ''
    + COALESCE(' AND' + QUOTENAME(@Pred_Filter2_Col) + SPACE(1) + @Pred_Filter2_Expression,'') 
    + COALESCE(' ORDER BY ' + @OrderBy,'') + ''''

--Create a variable to hold the newly created dynamic sql statement
--PRINT @sql
EXEC sp_executesql 
    @sql, 
    N'@dynCondition1_Col VARCHAR(255), @dynCondition1_Expression VARCHAR(500), @dynCondition2_Col VARCHAR(255), @dynCondition2_Expression VARCHAR(500), @dynSchema VARCHAR(255), @dynObject VARCHAR(255), @dynRtnSQL NVARCHAR(MAX) OUTPUT',
    @dynCondition1_Col = @Condition1_Col,
    @dynCondition1_Expression = @Condition1_Expression,
    @dynCondition2_Col = @Condition2_Col,
    @dynCondition2_Expression = @Condition2_Expression,
    @dynSchema = @Schema,
    @dynObject = @Object,
    @dynRtnSQL = @RtnSQL OUTPUT

--PRINT @RtnSQL

--Execute the newly created dynamic TSQL statment.
INSERT INTO @HTML (seq,Tag)
EXEC sp_executesql @RtnSQL

--Close all report HTML tags
INSERT INTO @HTML (seq,Tag)
SELECT 11, '</table></body></html>'

--SELECT Tag FROM @HTML ORDER BY seq -- return HTML in the correct order

SELECT @HTML_Email = COALESCE(@HTML_Email,'') + Tag FROM @HTML ORDER BY seq -- return HTML in the correct order

--PRINT @HTML_Email
EXEC msdb.dbo.sp_send_dbmail
    @recipients = @rec,
    @copy_recipients = @CC,
    @subject = @rpt_Header,
    @body = @HTML_Email,
    @body_format = 'HTML',
    @importance = 'Normal'    

END 
GO

Now let’s see this stored procedure in action. The code is very flexible and gives you a variety of methods to slice and dice data. I have provided two conditional filters that will highlight data that meets the criteria to a specified color. I have also include sort and filtering parameters to help reduce the amount of data being returned. As I stated before, not all of the parameters are required. One of my favorite parameters is @AltRowBGColor. @AltRowBGColor accepts an HTML color that will alternate the row color of the HTML table.

Execute the following code: (AltRowBGColor is commented out for this demo)

EXECUTE [dbo].[usp_Email_HTML_Rpt] 
   @DB ='tempdb'
  ,@Rec = 'ahaines@stei.com' –Change to your email address
  ,@Object = 'vw_SalesVolumnByLocation'
  ,@Schema = 'dbo'
  ,@rpt_Header = 'Sales Volumn By Location'
  ,@rpt_Header_BGColor = '#87AFC7'
  ,@TblHdr_BGColor = '#87AFC7'
  ,@Condition1_Col = 'SalesVolume'
  ,@Condition1_Expression = '<100'
  ,@Condition1_BGColor = '#E55451'
  ,@Condition2_Col = 'SalesVolume'
  ,@Condition2_Expression = '>200'
  ,@Condition2_BGColor = '#00FF00'
  --,@AltRowBGColor = '#A0CFEC'
  ,@OrderBy = '[SalesVolume] DESC'

You will get an email similar to the one below. Note that you have to have database mail enabled for this code to work. You will note that because a conditional filter was supplied a legend was generated. The legend contains the details of the supplied parameters. In the case below, Locations with a sales volume < 100 is considered sub par, hence the red color, and Locations with a sales volume > 200 is green. As you can see this is a great way to visually see your data. I use these types of reports in my environment to monitor backups, jobs, and their corresponding metrics.

Now, I will execute the stored procedure with lesser parameters and use the @AltRowBGColor variable. You will note that no legend is generated because no conditional formatting was supplied.

EXECUTE [master].[dbo].[usp_Email_HTML_Rpt] 
   @DB ='tempdb'
  ,@Rec = 'ahaines@stei.com'
  ,@Object = 'vw_SalesBySalesCounselor'
  ,@Schema = 'dbo'
  ,@rpt_Header = 'Sales Volume By Sales Counselor'
  ,@rpt_Header_BGColor = '#87AFC7'
  ,@TblHdr_BGColor = '#87AFC7'
  ,@AltRowBGColor = '#A0CFEC'
  ,@OrderBy = '[Amt] DESC'

This type of reporting is very good for quick and dirty analysis, like dash boarding. It is also very easy to implement and gives developers/DBAs quick turnaround for reporting. The alternative would be to open BIDs (or another reporting tool) and generate a report which takes a lot more time that executing a stored procedure. If you need automation, you can schedule this procedure to execute via a SQL job. There are a lot of modifications that this stored procedure can undergo. This stored procedure is by no means perfect, but it does get the job done. I am planning on enhancing a lot of the features provide here, but for the time being I am satisfied. I personally believe that variable checks need to be put into place and a show/hide legend bit should be introduced. Someone more versed in HTML might find it better to import a style sheet. When I get a little more time, I will formally update this post with more complete code. The idea here was to present a concept and show you the power of TSQL and database mail.

I hope that you find this stored procedure useful and I invite you to modify the code to work for your environment. If you have ideas on how to optimize the code or make a cool add-on, please keep me informed, so I can update this post.

Until next time happy coding.