Introduction
One thing you learn in Endpoint Management rather quickly is the importance of reporting.
Reports help proactively manage your IT environment, track it, and plan for its ever-evolving future. The criticality of this feature has meant that through the years Symantec has gifted the Altiris portfolio a strong reporting capability. Each solution comes with helpful ‘out-of-box’ reporting templates, and for those administrators who needed more there's been a neat report builder which eases the scavenging data from the Configuration Management Database (CMDB). In recent years, Altiris Administrators have also had IT Analytics for deeper multi-dimensional data analysis.
Even with such comprehensive reporting, there is gap. Scenarios do crop up where we need data to be consumed outside the current Altiris templates,
- Reports which require mulitple data entities to be presented
For example, a report with a summary section followed by a more detailed data sections.
- Reports which need to be made available without authentication and without incurring an Altiris reporting overhead
- Reports where graphical or iconic representations would be useful to ease data consumption
Several years ago, these tasks cropped up with such regularity that I decided to create something to make these ‘edge-case’ report scenarios easier to produce.
History
To solve this issue of creating reports outside of the Altiris framework, back in 2010 I created with a stored procedure which would on the fly convert a SQL table into an HTML table. This meant that once I'd written the T-SQL to generate the table data, it could be transformed on-the-fly into HTML.
I published my first article which demonstrated this stored procedure SpCustomTable2HTML later in 2011.
http://www.symantec.com/connect/downloads/sql-stored-procedure-convert-sql-table-html-v10
This worked nicely to HTML-ify the results of my T-SQL queries. As I started using this more and more, I found myself needing to enhance it so that it could create HTML tables with both horizontal and vertical alignments. I also began to find the first-row and first-column highlighting method I used cumbersome (it needed the addition of an extra style class as an argument stored procedure). In 2012 I created version 2, but didn't publish as I considered it still a revision in progress.
Later in the year, I found time to simplify, returning to a single table style class to define how the table should be formatted in the browser. This simplification was possible because first-row and first-column formatting can be achieved (as I'll demonstrate) through Cascading Style Sheets (CSS) using the :first-child pseduo class. I also demonstrated how tables could be prettied up with a little CSS.
This formed version 3 which I considered good enough to publish,
http://www.symantec.com/connect/downloads/sql-stored-procedure-convert-sql-table-html-v3
It’s now 2017 and this stored procedure is still heavily used, both my myself and others in the SQL community. It underlies our internal reports for our computer build processes, and also to simplify our server monitoring. We’ve found that being able to write a query in SQL and then instantly squirt that out as HTML incredibly powerful.
Below is an snippet of one of our server reports which allows anyone in our team to view disk utilisation across our server estate,
The version 4 is nothing special -it offers a further simplification in that it no longer needs a table as an argument to the stored procedure. If you have tables with Null entries, you might also enjoy a bug fix for an issue we had there.
Installing the CustomTable2HTMLv4 Stored Procedure
To start using this stored procedure download the attached zip file CustomTable2HTMLv4.zip, within which you'll find the file Create_SQL2HTML_v4.sql.txt . Copy the contents of this text file into SQL Management Studio and execute it.
After execution, you should see the message "Command Completed Successfully". This means the stored procedure has now been loaded into your selected database. Note in the screenshot above I have the USE eXpress statement to which will load the stored procedure into my eXpress database on the SQL Server.
The arguments that this stored procedure accepts are as follows,
- TSQL_QUERY
Mandatory variable. This it the T-SQL query whose output we want to HTML-ify.
- OUTPUT
Mandatory variable. The variable we want to dump the HTML output too.
- TBL_STYLE
Optional variable. The class string to insert in the table tag.
- ALIGNMENT
Optional variable. Integer to define the table alignment type.
0= Horizontal (default) 1=vertical. Optional variable.
Using the Stored Procedure
To show how to use this procedure, let's test it out with the with a table. Paste the following query into your SQL Server Management query window (Create_StarWars_Table.sql.txt),
Create table StarWars (
episode int,
score int,
name nvarchar(100)
)
insert into StarWars VALUES (1,90,'A New Hope')
insert into StarWars VALUES (2,95,'The Empire Strikes Back')
insert into StarWars VALUES (3,85,'Return of the Jedi')
insert into StarWars VALUES (4,15,'The Phantom Menace')
insert into StarWars VALUES (5,20,'Attack of the Clones')
insert into StarWars VALUES (6,35,'Revenge of the Sith')
insert into StarWars VALUES (7,90,'The Force Awakens')
Select * from StarWars
So what we've done is create the StarWars table in our database and then populated it with data on the Star Wars movies. I've given each Episode a personal score. The result of the above SQL on execution should be,
To get this table outputed in both horizonaly and verticaly HTML forms, the following T-SQL can be used,
DECLARE @HTML1 NVARCHAR(MAX)
DECLARE @HTML2 NVARCHAR(MAX)
EXEC dbo.CustomTable2HTMLv4 'select * from StarWars',@HTML1 OUTPUT,'class="horizontal"',0
EXEC dbo.CustomTable2HTMLv4 'select * from StarWars',@HTML2 OUTPUT,'class="vertical"',1
SELECT @HTML1+@HTML2
Here we have put the horizonally aligned table into the variable @HTML1 and the vertically aligned table into the variable @HTML2. The final select gives us the combination which is suitable for injection into an HTML document.
This query outputs the Starwars table in both horizonal and vertical formats as HTML,
<table class="horizontal">
<tr>
<td>episode</td>
<td>score</td>
<td>name</td>
</tr>
<tr>
<td>1</td>
<td>90</td>
<td>A New Hope</td>
</tr>
<tr>
<td>2</td>
<td>95</td>
<td>The Empire Strikes Back</td>
</tr>
<tr>
<td>3</td>
<td>85</td>
<td>Return of the Jedi</td>
</tr>
<tr>
<td>4</td>
<td>5</td>
<td>The Phantom Menace</td>
</tr>
<tr>
<td>5</td>
<td>10</td>
<td>Attack of the Clones</td>
</tr>
<tr>
<td>6</td>
<td>15</td>
<td>Revenge of the Sith</td>
</tr>
<tr>
<td>7</td>
<td>90</td>
<td>The Force Awakens</td>
</tr>
</table>
<table class="vertical">
<tr>
<td>episode</td>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
<td>5</td>
<td>6</td>
<td>7</td>
</tr>
<tr>
<td>score</td>
<td>90</td>
<td>95</td>
<td>85</td>
<td>5</td>
<td>10</td>
<td>15</td>
<td>90</td>
</tr>
<tr>
<td>name</td>
<td>A New Hope</td>
<td>The Empire Strikes Back</td>
<td>Return of the Jedi</td>
<td>The Phantom Menace</td>
<td>Attack of the Clones</td>
<td>Revenge of the Sith</td>
<td>The Force Awakens</td>
</tr>
</table>
Which as HTML by itself presents rather uninspringly (Starwars_Output_Raw.html),
This however is easy to fix with a bit of CSS.
Adding the CSS (Cascading Style Sheet)
To make the above HTML output more presentable, I use HTML templates. These templates contain <style> tags which store all my formatting.
Below I show an example of a simple HTML template (Starwars_Template.html),
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<style type='text/css'>
table td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
}
table.horizontal tr:first-child {
background-color: DarkBlue;
font-weight: bold;
text-transform: capitalize;
font-variant: small-caps;
color: #fff;
}
table.vertical tr>td:first-child {
background-color: DarkBlue;
font-weight: bold;
text-transform: capitalize;
font-variant: small-caps;
color: #fff;
}
</style>
<title></title>
</head>
<body>
%RESULTS%
</body>
</html>
Where the %RESULTS% variable is where I inject my HTML output from the stored procedure. This simple style template gives us the following output (Starwars_Output_With_CSS.html),
As you can see, this instantly makes a bit of a difference. The snazzy bits in here are the first-element pseudo classes,
table.horizontal tr:first-child {
background-color: Gray!important;
font-weight: bold;
color: #fff;
}
table.vertical tr>td:first-child {
background-color: Gray!important;
font-weight: bold;
color: #fff;
}
The first selector,
table.horizontal tr:first-child is a
selector chain. The first selector matches on the
horizontal table class, and the second matches on the pseudo-class
first-child, which in turn matches the first child element of the <tr> element i.e. the first row. This is the style which turns the first row's background grey, and formats the text as bold and white.
The second selector, table.vertical tr>td:first-child is a also selector chain. The first selector matches on the vertical table class, and the second matches on the first-child element of each row i.e. the first row using the child selector, >. This is the style which turns the background of the first column grey, formatting the text within as bold and white.
Very powerful stuff, and many thanks to Ian Senior at Oxford University for pointing this out.
Another Cool Style
Another nice thing that can be done with a little bit of extra tweaking, is to move beyond simple text in the HTML output. This can be done by injecting a bit of fanciness into the cells. If you want to see how that's done, take a look at Create_StarWars_Funky.sql.txt and StarWars_Funky_Template.html in the archive.
Acknowledgements
Thanks to Jordan Stone at Wells Fargo and Doug Foster of Foster Solutions for the v4 tweaks. Many thanks also to Ian Senior in Oxford's IT Services for his keen css knowlege and humor. And, as usual, thanks for Oxford's many IT Support Staff who are better than Google.
The T-SQL Code for CustomTable2HTMLv4
For Googlability here explicitly is the T-SQL code that I've got in the download.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomTable2HTMLv4] (
@TSQL_QUERY NVARCHAR(4000),
@OUTPUT NVARCHAR(MAX) OUTPUT,
@TBL_STYLE NVARCHAR(1024) = '',
@ALIGNMENT INT =0 )
AS
--------------------------------------------------------------------------------------------------
-- Author: Ian Atkin (ian.atkin@it.ox.ac.uk)
-- Description
-- Stored Procedure to take an ad-hoc SQL Query and return
-- table output as an equivalent HTML string.
-- Version History
-- v1 -First Release For Symantec Connect
-- v2 -Introduced table alignments
-- v3 -Release for Symantec connect.
-- Table to be outputed both horizonally and vertically. IsNull used
-- on cell value output to prevent NULLs creaping into HTML string
-- v4 -Release for Symantec connect.
-- Implemented fix from Jordan Stone (Wells Fargo) for remote IsNull bug
-- Implemented tweak from Doug Foster (Foster Solutions) to replace input temp table with query
--
--
--------------------------------------------------------------------------------------------------
-- @exec_str stores the dynamic SQL Query
DECLARE @exec_str NVARCHAR(MAX)
-- @ParmDefinition stores the parameter definition for the dynamic SQL
DECLARE @ParmDefinition NVARCHAR(500)
IF @ALIGNMENT=0
BEGIN
--We need to use Dynamic SQL at this point so we can expand the input table name parameter
SET @exec_str= N'
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @DEBUG INT
SET @DEBUG=0
IF @DEBUG=1 Print ''Table2HTML -Horizontal alignment''
--Make a copy of the original table adding an indexing column. We need to add an index column to the table to facilitate sorting so we can maintain the
--original table order as we iterate through adding HTML tags to the table fields.
--New column called CustColHTML_ID (unlikely to be used by someone else!)
--
select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM (' + @TSQL_QUERY + ') SUB
IF @DEBUG=1 PRINT ''Created temporary custom table''
--Now alter the table to add the auto-incrementing index. This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
IF @DEBUG=1 PRINT ''Added counter column to custom table''
-- @HTMLROWS will store all the rows in HTML format
-- @ROW will store each HTML row as fields on each row are iterated through
-- using dymamic SQL and a cursor
-- @FIELDS will store the header row for the HTML Table
DECLARE @HTMLROWS NVARCHAR(MAX) DECLARE @FIELDS NVARCHAR(MAX)
SET @HTMLROWS='''' DECLARE @ROW NVARCHAR(MAX)
-- Create the first HTML row for the table (the table header). Ignore our indexing column!
SELECT @FIELDS=COALESCE(@FIELDS, '' '','''')+''<td>'' + name + ''</td>''
FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
SET @FIELDS=@FIELDS + ''</tr>''
IF @DEBUG=1 PRINT ''table fields: '' + @FIELDS
-- @ColumnName stores the column name as found by the table cursor
-- @maxrows is a count of the rows in the table, and @rownum is for marking the
-- ''current'' row whilst processing
DECLARE @ColumnName NVARCHAR(500)
DECLARE @maxrows INT
DECLARE @rownum INT
--Find row count of our temporary table
SELECT @maxrows=count(*) FROM #CustomTable2HTML
--Create a cursor which will look through all the column names specified in the temporary table
--but exclude the index column we added (CustColHTML_ID)
DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
ORDER BY column_id ASC
--For each row, generate dymanic SQL which requests the each column name in turn by
--iterating through a cursor
SET @rowNum=1
SET @ParmDefinition=N''@ROWOUT NVARCHAR(MAX) OUTPUT,@rowNum_IN INT''
While @rowNum <= @maxrows
BEGIN
SET @HTMLROWS=@HTMLROWS + ''<tr>''
OPEN col
FETCH NEXT FROM col INTO @ColumnName
IF @DEBUG=1 Print ''@ColumnName: '' + @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
--Get nth row from table
--SET @exec_str=''SELECT @ROWOUT=(select top 1 ['' + @ColumnName + ''] from (select top '' + cast(@rownum as varchar) + '' * from #CustomTable2HTML order by CustColHTML_ID ASC) xxx order by CustColHTML_ID DESC)''
SET @exec_str=''SELECT @ROWOUT=(select ['' + @ColumnName + ''] from #CustomTable2HTML where CustColHTML_ID=@rowNum_IN)''
IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
EXEC sp_executesql
@exec_str,
@ParmDefinition,
@ROWOUT=@ROW OUTPUT,
@rowNum_IN=@rownum
IF @DEBUG=1 SELECT @ROW as ''@Row''
SET @HTMLROWS =@HTMLROWS + ''<td>'' + IsNull(@ROW,'''') + ''</td>''
FETCH NEXT FROM col INTO @ColumnName
END
CLOSE col
SET @rowNum=@rowNum +1
SET @HTMLROWS=@HTMLROWS + ''</tr>''
END
SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @FIELDS + @HTMLROWS + ''</table>''
DEALLOCATE col
'
END
ELSE
BEGIN
--This is the SQL String for table columns to be aligned on
--the vertical. So we select a table column, and then iterate
--through all the rows for that column, this forming one row
--of our html table.
SET @exec_str= N'
DECLARE @exec_str NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @DEBUG INT
SET @DEBUG=0
IF @DEBUG=1 Print ''Table2HTML -Vertical alignment''
--Make a copy of the original table adding an indexing column.
--We need to add an index column to the table to facilitate sorting
--so we can maintain the original table order as we iterate through
--adding HTML tags to the table fields.
--
--New column called CustColHTML_ID (unlikely to be used by someone
--else!)
select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM (' + @TSQL_QUERY + ') SUB
IF @DEBUG=1 PRINT ''CustomTable2HTMLv2: Modfied temporary table''
--Now alter the table to add the auto-incrementing index.
--This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1
-- @HTMLROWS will store all the rows in HTML format
-- @ROW will store each HTML row as fields on each row are iterated
-- through using dymamic SQL and a cursor
DECLARE @HTMLROWS NVARCHAR(MAX)
DECLARE @ROW NVARCHAR(MAX)
SET @HTMLROWS=''''
-- @ColumnName stores the column name as found by the table cursor
-- @maxrows is a count of the rows in the table
DECLARE @ColumnName NVARCHAR(500)
DECLARE @maxrows INT
--Find row count of our temporary table
--This is used here purely to see if we have any data to output
SELECT @maxrows=count(*) FROM #CustomTable2HTML
--Create a cursor which will iterate through all the column names
--in the temporary table (excepting the one we added above)
DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id(''tempdb..#CustomTable2HTML'')
AND name not like ''CustColHTML_ID''
ORDER BY column_id ASC
--For each **HTML** row, we need to for each iterate through
--each table column as the outer loop.
--Once the column name is identified, we use Coalesc to
--combine all the column values into a single string.
SET @ParmDefinition=N''@COLOUT NVARCHAR(MAX) OUTPUT''
OPEN col
FETCH NEXT FROM col INTO @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
--Using current column name, grab all column values and
--combine into an HTML cell string using COALESCE
SET @ROW=''''
SET @exec_str='' SELECT @COLOUT=COALESCE(@COLOUT + ''''</td>'''','''''''') + ''''<td>'''' + Cast(IsNull(['' + @ColumnName + ''],'''''''') as nvarchar(max)) from #CustomTable2HTML ''
IF @DEBUG=1 PRINT ''@exec_str: '' + @exec_str
EXEC sp_executesql
@exec_str,
@ParmDefinition,
@COLOUT=@ROW OUTPUT
SET @HTMLROWS =@HTMLROWS + ''<tr>'' + ''<td>'' + @ColumnName + ''</td>'' + @ROW + ''</tr>''
IF @DEBUG=1 SELECT @ROW as ''Current Row''
IF @DEBUG=1 SELECT @HTMLROWS as ''HTML so far..''
FETCH NEXT FROM col INTO @ColumnName
END
CLOSE col
SET @OUTPUT=''''
IF @maxrows>0
SET @OUTPUT= ''<table ' + @TBL_STYLE + '>'' + @HTMLROWS + ''</table>''
DEALLOCATE col
'
END
DECLARE @ParamDefinition nvarchar(max)
SET @ParamDefinition=N'@OUTPUT NVARCHAR(MAX) OUTPUT'
--Execute Dynamic SQL. HTML table is stored in @OUTPUT
--which is passed back up (as it's a parameter to this SP)
EXEC sp_executesql @exec_str,
@ParamDefinition,
@OUTPUT=@OUTPUT OUTPUT
RETURN 1
Useful Links
A few links which I found useful when writing this up,