In this Article I am explain , How to create HTML Reporting in SQL SERVER and that on Email.....
USE Gangwar
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('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 Gangwar
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
@profile_name='Asp.Net Blog',--You can change profile name
@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 ='Gangwar'
,@Rec = 'test@gmail.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'
No comments:
Post a Comment