Monday, June 8, 2009

Creating an HTML report dynamically, with conditional formatting and dynamic filtering

One of the things missing in SSMS is the ability to make “pretty” reports. Most user do not care for the plain text reports that are directly copied from the query window. In fact most of the “higher-ups” demand a report with more pizzazz. So how can we accommodate a “pretty” report, on a frequent schedule? One solution is to use SSRS, but we will be exploring a dynamic solution that uses dynamic sql and sp_send_dbmail. I will be using sp_send_dbmail() because it gives me the ability to put the results of a query into the body of email, or an attachment. How does a process that “automagically” creates and emails a pretty HTML report, with dynamic filtering and conditional formatting sound? I bet you are on the edge of your seat…. well maybe not :), but I bet you are at least thinking about how much time this could save you. So how do we do this? I started by breaking the logic down into three procedures. Two of these procedures are used to actually create and email the report and the third stored procedure is used to strip invalid characters out of the dynamic sql.

You can download all the code here: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/SQL%20HTML%20Report/sp%7C_Create%7C_HTML%7C_Rpt.zip. Lets start with the first stored procedure. The first procedure strips invalid characters from a given input string. This stored procedure helps protect our data by reducing the risk of SQL injection attack. You can customize the regular expression to include characters you deem valid. These checks are done against the filtering and conditional formatting inputs. I chose to exclude all and only include valid characters versus choosing to include all and excluding specific characters. This is a more secure model and is equally configurable. The stored procedure accepts one parameter @str, which is an input string.

Parameters:

  • @str – Input string to be stripped for invalid characters
USE master
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RemoveChars]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    DROP FUNCTION dbo.sp_RemoveChars
END
GO
 
CREATE FUNCTION dbo.sp_RemoveChars(@Str varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
declare @NewStr varchar(500),
        @i int
set @i = 1        
set @NewStr = ''
 
while @i <= len(@str)
begin
    --grab digits or ( in regex) decimal
    if substring(@str,@i,1) LIKE '[0-9A-Z%\/.<>!=]' OR substring(@str,@i,1) = ' ' 
    begin
        set @NewStr = @NewStr + substring(@str,@i,1)
    end
    else
    begin
        set @NewStr = @NewStr
    end
    set @i = @i + 1
end
  RETURN Rtrim(Ltrim(@NewStr))
END
GO

The next stored procedure we will be creating is sp_Create_HTML_Rpt, which is the main report. This stored procedure actually generates the HTML. You should point this code at an table or view. There are lots of parameters here and I will go through each of them. Essentially this code gives you the flexibility to filter the data, create custom conditional formatting etc..

Parameters:

  • @Schema – The schema of the object
  • @object – The object being reported on. This can be a view or a table.
  • @ColList – A comma delimited list of columns that you want to report on. The default is all columns for a given table/view.
  • @FilterCol – The column that should filter the data
  • @FilterOperator – The operand used to filter the data
  • @FilterCriteria – The filter expression
  • @HeaderBGColor – The color of the background for the header row.
  • @Align – The alignment of the column data. The default is left.
  • @ConditionColumn – This is the column that is used to conditionally apply formatting to a row.
  • @ConditionOperator – This is the operand of the conditional expression
  • @ConditionBGColor – The color of the background for the conditional expression, if met.
  • @FontSize – The size of the body font.
--====================================================================================
-- CHANGE DB CONTEXT TO MASTER
--====================================================================================
USE master
GO
 
--====================================================================================
-- DROP THE SP IF IT ALREADY EXISTS
--====================================================================================
IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'sp_Create_HTML_Rpt')
BEGIN
    DROP PROCEDURE sp_Create_HTML_Rpt;
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE sp_Create_HTML_Rpt
    @schema varchar(100) = 'dbo',
    @object varchar(100),
    @ColList varchar(max) = 'ALL',
    @FilterCol varchar(100) = NULL,
    @FilterOperator varchar(6) = '=',
    @FilterCriteria varchar(50) = NULL,
    @HeaderBGColor VARCHAR(7) = '#dcdcdc',
    @Align Varchar(10) = 'LEFT',
    @ConditionColumn varchar(100) = NULL,
    @ConditionOperator varchar(6) = '=',
    @ConditionCriteria varchar(100) = NULL,
    @ConditionalBGColor varchar(7) = '#cd5c5c',
    @FontSize varchar(2) = '10'
AS
BEGIN
 
set nocount on;
 
BEGIN TRY
 
BEGIN TRANSACTION
 
--====================================================================================
-- CHECK PARAMS FOR VALID DATA
--====================================================================================
 
--***********************************************************
-- BEGIN @SCHEMA CHECK
--***********************************************************
--if the schema does not exist raise an error
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = @Schema) AND
    @Schema IS NOT NULL
BEGIN
    RAISERROR ('The specified schema does not exists. SP aborted.',10,1) WITH LOG
    RETURN 99 --ERROR
END
 
--***********************************************************
-- BEGIN @OBJECT CHECK
--***********************************************************
--if the object does not exist raise and error
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = object_id(@object)) AND
    @Object IS NOT NULL
BEGIN
    --RAISEERORR WITH LOG
    RAISERROR ('The specified object name does not exists. SP aborted.',10,1) WITH LOG
    RETURN 99 --ERROR
END
 
--====================================================================================
-- GET FULL QUALIFIED NAME OF OBJECT
--====================================================================================
DECLARE @FullQualifiedName sysname
 
set @FullQualifiedName = '[' + DB_NAME() + '].'
set @FullQualifiedName = @FullQualifiedName + '[' + COALESCE(@schema,'dbo') + '].'
set @FullQualifiedName = @FullQualifiedName + '[' + @object + ']'
 
--***********************************************************
-- BEGIN @CONDITIONCOLUMN CHECK
--***********************************************************
DECLARE @ConditionColumnExists BIT
 
--if the condition column exists make the variable 1 else 0
IF EXISTS(
        select 1
        from sys.columns
        where name = @ConditionColumn and
              object_id = object_id(@FullQualifiedName))
    AND @ConditionColumn IS NOT NULL
BEGIN
    set @ConditionColumnExists = 1
END
ELSE
BEGIN
    set @ConditionColumnExists = 0
END
 
--***********************************************************
-- BEGIN @CONDITIONCOLUMNCRITERIA CHECK
-- The below function will strip out all invalid characters
-- valid chars are letters, numbers and the 
-- symbols "%","/',"\","."
--***********************************************************
IF LEN(@ConditionCriteria) > 0
BEGIN
set @ConditionCriteria = (select master.dbo.sp_RemoveChars(@ConditionCriteria))
END
 
--***********************************************************
-- BEGIN @FILTER CHECK
--***********************************************************
DECLARE @FilterColumnExists BIT
 
--if the condition column exists make the variable 1 else 0
IF EXISTS(
        select 1
        from sys.columns
        where name = @FilterCol and
              object_id = object_id(@FullQualifiedName))
    AND @FilterCol IS NOT NULL
BEGIN
    --filter column exists
    set @FilterColumnExists = 1
END
ELSE
BEGIN
    --filter column does not exist
    set @FilterColumnExists = 0
END
 
--***********************************************************
-- BEGIN @FILTERCRITERIA CHECK
-- The below function will strip out all invalid characters
-- valid chars are letters, numbers and the 
-- symbols "%","/',"\",".",”<”,”>”,”!”
--***********************************************************
IF LEN(@FilterCriteria) > 0
BEGIN
    set @FilterCriteria = (select master.dbo.sp_RemoveChars(@FilterCriteria))
END
 
--***********************************************************
-- BEGIN @Condition Operator CHECK
--***********************************************************
IF @ConditionOperator NOT IN('=','<','>','<=','>=','LIKE','BETWEEN','<>','!=')
BEGIN
 
    --RAISEERORR WITH LOG
    RAISERROR ('The specified filter operator is invlaid. SP aborted.',10,1) WITH LOG
    RETURN 99 --ERROR
 
END
 
--***********************************************************
-- BEGIN @Filter OPerator CHECK
--***********************************************************
IF @FilterOperator NOT IN('=','<','>','<=','>=','LIKE','BETWEEN','<>','!=')
BEGIN
 
    --RAISEERORR WITH LOG
    RAISERROR ('The specified filter operator is invlaid. SP aborted.',10,1) WITH LOG
    RETURN 99 --ERROR
 
END
 
--***********************************************************
-- BEGIN @ALIGN CHECK
--***********************************************************
--check for valid alignments, if the align var is set wrong
--set the alignment to left
IF @Align NOT IN ('Left','Center','Right') AND @Align IS NOT NULL
BEGIN
    SET @Align = 'Left'
END
 
--***********************************************************
-- BEGIN @FONTSIZE CHECK
--***********************************************************
IF @FontSize NOT LIKE '[0-9]'
BEGIN
    SET @FontSize = '10'
END
 
--if the font size is greater than 30 make it 30
IF @FontSize > 30
BEGIN
    SET @FontSize = '30'
END
 
--====================================================================================
-- DECLARE LOCAL VARIABLES
--====================================================================================
declare    @table_header varchar(max),
        @table_data varchar(max),
        @table_footer varchar(max),
        @Cols VARCHAR(max),
        @sql nvarchar(max),
        @table_StyleSheet varchar(max),
        @params nvarchar(500),
        @x XML
 
--====================================================================================
-- INITIALIZE LOCAL VARIABLES
--====================================================================================
set @table_header = ''
set @table_data = ''
set @table_footer = ''
SET @Cols = ''
set @sql = ''
set @table_StyleSheet = ''
set @params = N'@bgColor varchar(7)' +
                CASE WHEN @FilterColumnExists = 1 THEN ', @Filter varchar(100)' ELSE '' END
SET @x = '<i>' + REPLACE( @ColList, ',', '</i><i>') + '</i>'
 
--***********************************************************************
-- SETUP DELIMITED COLUMN LIST, WITH TABLE DESC TAGS
--***********************************************************************
IF @ColList = 'ALL'
BEGIN
 
select  @Cols = COALESCE(@Cols,'') +
    '''<td ' + COALESCE(@Align,'LEFT') + 
    '>'' + CONVERT(VARCHAR(200),COALESCE(' + c.Name + ','''')) + ''</td>'' + '
from sys.columns c
where object_id = object_id(@FullQualifiedName)
order by column_id
 
END
 
ELSE
 
BEGIN 
 
select  @Cols = COALESCE(@Cols,'') +
    '''<td Align= ' + COALESCE(@Align,'LEFT') + 
    '>'' + CONVERT(VARCHAR(200),COALESCE(' + c.Name + ','''')) + ''</td>'' + '
from sys.columns c
    inner join(
        SELECT x.i.value('.', 'VARCHAR(100)') as [ColName]
        FROM @x.nodes('//i') x(i)
) as Cols
    on c.name = LTRIM(RTRIM(Cols.ColName))
where object_id = object_id(@FullQualifiedName)
order by column_id
 
END
 
--====================================================================================
-- CREATE TEMP TABLE TO HOUSE RESULTS
--====================================================================================
CREATE TABLE #Results(
HTML_Seq VARCHAR(2),
TagType VARCHAR(20),
TagText VARCHAR(MAX)
)
 
--====================================================================================
-- BEGIN POPULATING TEMP TABLE RESULTS, WITH HTML DATA
--====================================================================================
--***********************************************************
-- BEGIN INSERT STYLESHEET
--***********************************************************
INSERT INTO #Results (HTML_Seq,TagType,TagText) 
VALUES (1,'STYLESHEET','<style type="text/css">');
 
INSERT INTO #Results (HTML_Seq,TagType,TagText) 
VALUES (1,'STYLESHEET','table {font-size: ' + COALESCE(@FontSize,'10') + 'pt;}');
 
INSERT INTO #Results (HTML_Seq,TagType,TagText) 
VALUES (1,'STYLESHEET','</style>');
 
--***********************************************************
-- BEGIN INSERT TABLE HEADER
--***********************************************************
INSERT INTO #Results (HTML_Seq,TagType,TagText) 
VALUES (2,'HEADER','<HTML><BODY><table border=1 cellpadding=5 cellspacing=1>');
 
INSERT INTO #Results (HTML_Seq,TagType,TagText)
VALUES (2,'HEADER','<tr bgcolor="' + COALESCE(@HeaderBGColor,'#FFFFFF') + '">');
 
IF @ColList = 'ALL'
BEGIN
 
    INSERT INTO #Results (HTML_Seq,TagType,TagText)
    select 2, 'HEADER', '<th>' + c.name + '</th>'
    from sys.columns c
    where object_id = object_id(@FullQualifiedName)
    order by column_id
 
END
 
ELSE
 
BEGIN 
 
    INSERT INTO #Results (HTML_Seq,TagType,TagText)
    select 2, 'HEADER', '<th>' + c.name + '</th>'
    from sys.columns c
        inner join(
            SELECT x.i.value('.', 'VARCHAR(100)') as [ColName]
            FROM @x.nodes('//i') x(i)
    ) as Cols
        on c.name = LTRIM(RTRIM(Cols.ColName))
    where object_id = object_id(@FullQualifiedName)
    order by column_id
 
END
 
INSERT INTO #Results (HTML_Seq,TagType,TagText) 
VALUES (2, 'HEADER','</tr>');
 
--***********************************************************
-- BEGIN INSERT TABLE BODY
--***********************************************************
SET @sql = N'insert into #Results (HTML_Seq,TagType,TagText) 
select ''3'', ''BODY'', ' +
case when @ConditionColumnExists = 1 then
    'case when [' + COALESCE(@ConditionColumn,'') + '] ' + @ConditionOperator + '''' + @ConditionCriteria + ''' then 
        ''<tr bgcolor='' + '''' +  COALESCE(@bgColor,''"#FFFFFF"'') + '''' + ''>'' + '
             + COALESCE(@Cols,'') + ' + ''</tr>'' 
     else ''<tr>'' + ' + COALESCE(@Cols,'') + ' + ''</tr>''
     end'
else
    '''<tr>'' + ' + COALESCE(@Cols,'') + ' + ''</tr>'''
end + '
From ' + @FullQualifiedName +
case when @FilterColumnExists = 1 THEN 
' WHERE ' + COALESCE(@FilterCol,'') + space(1) + @FilterOperator + space(1) + '@Filter'
ELSE '' END
 
IF @FilterColumnExists = 0
BEGIN
EXECUTE sp_executesql    @sql
                        ,@params
                        ,@bgColor = @ConditionalBGColor
 
END
 
IF @FilterColumnExists = 1
BEGIN
 
EXECUTE sp_executesql    @sql
                        ,@params
                        ,@bgColor = @ConditionalBGColor
                        ,@Filter = @FilterCriteria
END
 
--print @Cols
--print @FilterCriteria
 
 
--***********************************************************
-- BEGIN INSERT TABLE FOOTER
--***********************************************************
INSERT INTO #Results (HTML_Seq,TagType,TagText) 
VALUES (4, 'FOOTER','</table><BODY><HTML>');
 
--====================================================================================
-- FINAL SELECT TO RETURN THE NEEDED DATA
--====================================================================================
select TagText
from #Results
order by HTML_Seq
 
COMMIT TRANSACTION
 
END TRY
 
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
 
    ROLLBACK TRANSACTION
 
END CATCH
 
END
GO
 
--====================================================================================
-- Mark the sp as a system object, so you can call it from any db
-- and get the local system metadata.
--====================================================================================
EXEC sys.sp_MS_marksystemobject 'sp_Create_HTML_Rpt'
GO

Now that we have our HTML code, we can create the stored procedure that emails the report. Note: This stored procedure is just a wrapper that calls sp_Create_HTML_Rpt, along with sp_send_dbmail.

Parameters:

  • @Email_To – Email addresses to send the report
  • @Email_Body – The text of the email, which proceeds the HTML report.
  • @Email_Subject – The subject of the email
  • All remaining parameters have been discussed
USE master
GO
 
IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'sp_Mail_HTML_Rpt')
BEGIN
    DROP PROCEDURE sp_Mail_HTML_Rpt;
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE sp_Mail_HTML_Rpt
    --@Email_Profile VARCHAR(25),
    @Email_To VARCHAR(100),
    @Email_Body VARCHAR(max),
    @Email_Subject VARCHAR(100),
    @schema varchar(100) = 'dbo',
    @object varchar(100),
    @ColList varchar(max) = 'ALL',
    @FilterCol VARCHAR(100) = NULL,
    @FilterOperator varchar(6) = '=',
    @FilterCriteria varchar(100) = NULL,
    @HeaderBGColor VARCHAR(7) = '#dcdcdc',
    @Align Varchar(10) = 'LEFT',
    @ConditionColumn varchar(100) = NULL,
    @ConditionOperator varchar(6) = '=',
    @ConditionCriteria varchar(100) = NULL,
    @ConditionalBGColor varchar(7) = '#cd5c5c',
    @FontSize varchar(2) = '10'
AS
 
BEGIN
 
    BEGIN TRY
 
        BEGIN TRANSACTION
 
        --==========================================================================
        -- DECLARE LOCAL VARIABLES
        --==========================================================================
        DECLARE @Email_Query NVARCHAR(500),
                @db sysname
 
        --==========================================================================
        -- INITIALIZE LOCAL VARIABLES
        --==========================================================================
        SET @db = db_name()
 
        --********************************************************
        -- Execute sp to create HTML report
        --********************************************************
        SET @Email_Query = 
        N'EXECUTE sp_Create_HTML_Rpt
           @schema = ''' + COALESCE(@schema,'dbo') + '''
          ,@object = ''' + @object + '''
          ,@ColList = ''' + COALESCE(@ColList,'ALL') + '''
          ,@FilterCol = ''' + COALESCE(@FilterCol,'') + '''
          ,@FilterOperator = ''' + COALESCE(@FilterOperator,'=') + '''
          ,@FilterCriteria = ''' + COALESCE(@FilterCriteria,'') + '''
          ,@HeaderBGColor = ''' + COALESCE(@HeaderBGColor,'#dcdcdc') + '''
          ,@Align = ''' + COALESCE(@Align,'LEFT') + '''
          ,@ConditionColumn = ''' + COALESCE(@ConditionColumn,'') + '''
          ,@ConditionOperator = ''' + COALESCE(@ConditionOperator,'=') + '''
          ,@ConditionCriteria = ''' + COALESCE(@ConditionCriteria,'') + '''
          ,@ConditionalBGColor = ''' + COALESCE(@ConditionalBGColor,'#cd5c5c') + '''
          ,@FontSize = ''' + COALESCE(@FontSize,'10') + ''''
 
        --==========================================================================
        -- EXECUTE SEND MAIL TASK
        --==========================================================================
        --you can make an html attachment
        EXEC msdb.dbo.sp_send_dbmail
                --@profile_name = @Email_Profile,
                @recipients = @Email_To,
                @body = @Email_Body,
                @query = @Email_Query,
                @execute_query_database = @db,
                @attach_query_result_as_file = 0,
                @query_result_header = 0,
                @exclude_query_output = 1,
                @subject = @Email_Subject,
                @body_format = 'HTML',
                @importance = 'NORMAL',
                @append_query_error =1,
                @query_no_truncate = 1
 
        COMMIT TRANSACTION
 
    END TRY
 
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
 
    ROLLBACK TRANSACTION
 
    END CATCH
END

Now that you have the code you can execute it by issuing a statement similar to this.

EXECUTE [sp_Mail_HTML_Rpt] 
   --@Email_Profile = null  --db mail email profile (not needed)
   @Email_To = 'ahaines@somedomain.com' --send email to
  ,@Email_Body = 'Attached is the daily scheduled job report. Please review. <br> <br>' --body text
  ,@Email_Subject = 'Schedule Job Report' --subject
  ,@schema = 'dbo' --object schema
  ,@object =  'vw_ScheduleJobReport' --object name
  ,@ColList = 'ALL' -- specify column list comma delimited. All is default
  ,@FilterCol = null --column to filter data on
  ,@FilterOperator = null --filter operand
  ,@FilterCriteria = null -- filter criteria
  ,@HeaderBGColor = NULL --header background color
  ,@Align = null --detail align
  ,@ConditionColumn = 'descr' --column for conditional formatting
  ,@ConditionOperator = '=' --conditional operator
  ,@ConditionCriteria = 'FAILED' --criteria to highlight column
  ,@ConditionalBGColor = 'RED' --conditional background color
  ,@FontSize = NULL -- detail font size

There you have it. A stored procedure that will take a given table/view and automatically generate and email an HTML report. As you can see this code is easily customizable and I invite you to do so.

2 comments:

Anonymous said...

Hi sir – thank you so much for this great job you did combining HTML report and E-mail. I don’t know much about SQL and DB but I need it in my application. I was able to understand the logic very well – thank you. You also taught me about the risk of SQL injection attack.

When I tested the SP and the execution, I got the error “The specified object name does not exists. SP aborted. Msg 266, Level 16, State 2, Procedure sp_Create_HTML_Rpt, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.” in my Express 2012. Since I am not familiar with the “commit transaction” in relation with “begin”, I don’t know how to fix this. I would appreciate any help.

I was also looking forwards to learn about the daily scheduled job report, thinking you would cover the sp_procoption. But, I am sure I misunderstood this aspect of your excellent article.

Paul

Anonymous said...

Hi sir – thank you so much for this great job you did combining HTML report and E-mail. I don’t know much about SQL and DB but I need it in my application. I was able to understand the logic very well – thank you. You also taught me about the risk of SQL injection attack.

When I tested the SP and the execution, I got the error “The specified object name does not exists. SP aborted. Msg 266, Level 16, State 2, Procedure sp_Create_HTML_Rpt, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.” in my Express 2012. Since I am not familiar with the “commit transaction” in relation with “begin”, I don’t know how to fix this. I would appreciate any help.

I was also looking forwards to learn about the daily scheduled job report, thinking you would cover the sp_procoption. But, I am sure I misunderstood this aspect of your excellent article.

Paul