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]


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

EmployeeId INT,
Amt NUMERIC(9,2),
LocationCd INT

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);

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

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

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

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]

    @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


        @StyleSheet VARCHAR(MAX),
        @RtnSQL NVARCHAR(MAX),
        @html_email NVARCHAR(MAX)

--Create a new style sheet if none was passed in
IF @StyleSheet IS NULL
--Set the Procedure Stylesheet.  You can also supply this as a variable
SET @StyleSheet = 
 <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"} 
<title>' + COALESCE(@rpt_Header,'Report Header') + '</title>

--Build basic html structure
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

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%">'

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%">'

--Create Table Header
SET @sql = N'
    SELECT CAST(''<th>'' + COALESCE(c.COLUMN_NAME,'''') + ''</th>'' AS XML)
    WHERE c.[TABLE_NAME] = @dynObject AND c.[TABLE_SCHEMA] = @dynSchema
    ) AS VARCHAR(MAX))';
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'
    @dynRtnSQL = ''SELECT 10,''''<tr>''''+'' + STUFF(
            ''+  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
    ,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 
    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


--Execute the newly created dynamic TSQL statment.
EXEC sp_executesql @RtnSQL

--Close all report HTML tags
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

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


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.


Anonymous said...

Nice! .... I posted something "like" this for the tsql Tuesday this week, yours takes it to the next (actually next several) level!

I will try this out.

Michael J. Swart said...

Very cool. It reminds me of something I started on but never finished:

It's not based on showing tables, but on histograms. Using div elements that have style attributes indicating "position:absolute".

I gave up on it pretty quickly, especially when I saw it's already been done (see phil factor's article and the comments left there).

What I like about your article, what really really lifts this Report-via-SQL idea out of the nifty-trick category is combining it with email.
That all-of-a-sudden makes it useful.

One concern (which you already mentioned) is to keep an eye on the Quick-and-Dirty aspect of it. Full blown reporting applications in the SQL Server database engine would probably be overkill.

www.oracledba.in said...

i like this post

Joshua Smith said...

Thanks for your help in describing this. Nice review! Also turn your attention that it needs to search for mobile software development companies if you need mobile software development.

Smolkaville said...

Excellent work!

Smolkaville said...

Excellent work!

anita said...

I have a similar table which goes out as email. But for some reasons the it puts spaces randomly which breaks my links in the email.

Any suggestions.

jowdjbrown said...

The conclusions they usually arrive at: HTML5 slow, inconsistent, limited and doesn't have a native look and feel. Therefore, native development is superior.psd to email

Priya Kannan said...

I just see the post i am so happy the post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be subscribing to your feed and I hope you post again soon.
SQL Server Training in Chennai

Used PC Distributor said...

Nice Blog Post !

Anonymous said...

Wow! That's really great information guys.I know lot of new things here. Really great contribution.Thank you ...

Weblogic Server 12cR2 Training

Peter Johnson said...

Awe! What An Blog Very Helpful and interesting Really A great center for acquiring knowledge.Very Helpful Post And Explained Very Clearly About All the things.Very Helpful. Coming To Our Self We Provide Food Service Parts .Really Thankfull For the blogger providing such a great information.Thank you. Have a Nice Day.

seo said...

This Blog is very helpful and useful,came to know that i should be strong in my basics and this blog helps me to improve it,Urgent Care Center Services Provided by Us.Thanks For Posting.I Am refereed by my friend to this blog and i also want to refer my other friends to this blog.

Lathika Honey said...

Good article. It is very useful for me to learn and understand easily USMLE Thanks for posting.

Lathika Honey said...

This Blog Provides Very Useful and Important Information. I just Want to share this blog with my friends and family members. Tibco Certification Training

24Layouts said...

Thanks For the Detailed Information........

Visakhapatnam Real Estate

sai ram said...

Thanks for the informative post....

Vijay Devarakonda Height

Anbarasan14 said...

Nice post. Thanks for sharing such a worthy information.

Spoken English Classes in Velachery
Spoken English Classes in Medavakkam
Spoken English Class in Guindy
Spoken English Coaching Classes near me
Spoken English Classes in Navalur
Spoken English Classes in OMR Chennai
Spoken English Classes in Perungudi

yuva prithika said...

This information is impressive. I am inspired with your post writing style & how continuously you describe this topic. Eagerly waiting for your new blog keep doing more.
ccna Course in Bangalor
cloud computing training institutes in bangalore
best cloud computing training in bangalore
cloud computing certification in bangalore

jefrin adams said...

This post is very useful thanks for sharing
SQL DBA training in chennai

kamal said...

Outstanding blog thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.
SQL Training in Chennai | SQL DPA Training in Chennai | SQL Training institute in Chennai

jaya devan said...

It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.
Data Science Training in Chennai | Data Science Course in Chennai
Python Course in Chennai | Python Training Course Institutes in Chennai
RPA Training in Chennai | RPA Training in Chennai
Digital Marketing Course in Chennai | Best Digital Marketing Training in Chennai

sheela rajesh said...

Such an informative blog that i have ever red.I have read it very interesting information's.
Python Training in Chennai
Python course in Chennai
JAVA Training in Chennai
Big data training in chennai
Android Training in Chennai
Python Training in Chennai
Python Training in Anna Nagar

anusha said...

Full Stack Development Training in Chennai Searching for Full Stack Development training in chennai ? Bita Academy is the No 1 Training Institute in Chennai. Call for more details.

jvimala said...

Hey, would you mind if I share your blog with my twitter group? There’s a lot of folks that I think would enjoy your content. Please let me know. Thank you.
html5 training in chennai |html5 training in institute chennai |best html5 training in chennai |html5 training course in chennai

Elevators and Lifts said...

Nice post. The information you shared is so beautiful and Keep Sharing. https://www.eliteelevators.com/products