Solved

Trying to make alternate lines in an html table generated by sp_send_dbmail have a different background color

Posted on 2008-10-29
8
822 Views
Last Modified: 2010-08-05
I am trying to get the HTML table generated in an email to have alternating background colors for each row.  The HTML table/email are being generated in SQL Server 2005 via the command sp_send_dbmail.  Attached is a example code of what I am doing currently from SQL Server Books Online.  Any help is appreciated thanks in advance.
DECLARE @tableHTML  NVARCHAR(MAX) ;
 

SET @tableHTML =

    N'<H1>Work Order Report</H1>' +

    N'<table border="1">' +

    N'<tr><th>Work Order ID</th><th>Product ID</th>' +

    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +

    N'<th>Expected Revenue</th></tr>' +

    CAST ( ( SELECT td = wo.WorkOrderID,       '',

                    td = p.ProductID, '',

                    td = p.Name, '',

                    td = wo.OrderQty, '',

                    td = wo.DueDate, '',

                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty

              FROM AdventureWorks.Production.WorkOrder as wo

              JOIN AdventureWorks.Production.Product AS p

              ON wo.ProductID = p.ProductID

              WHERE DueDate > '2004-04-30'

                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 

              ORDER BY DueDate ASC,

                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC

              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;
 

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',

    @subject = 'Work Order List',

    @body = @tableHTML,

    @body_format = 'HTML' ;

Open in new window

0
Comment
Question by:SomeGuy892
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22831194
I guess you could use the ranking function row_number() and do a modulus operation (%2) on it to make the even numbers one color and the odds another...make sense?
0
 

Author Comment

by:SomeGuy892
ID: 22831682
Thanks for the quick response  chapmandew.   So that gives me something to use in a conditional statement to determine the row background color however it doesnt create the color itself.  That is the part that has me stuck.  Additionally I will now have to add the html tag of "style='visibility: hidden' " to the td that returns the 0/1 flag.  Unless someone has a better idea of how to ensure that 0 / 1 is not returned in the email that is generated

Here is an untested copy of what we have come up with so far:

  DECLARE @tableHTML  NVARCHAR(MAX) ;
 
SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
                    td = ROW_NUMBER() OVER (ORDER BY DueDate ASC,(p.ListPrice - p.StandardCost) * wo.OrderQty DESC) % 2
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;
 
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22831763
Can you throw some logic in there so that you set the bgcolor?

maybe something like this:

select case ranking % 2 when 1 then 'bgcolor = "silver" else 'bgcolor = "gold"' from
(
select *, ranking = ROW_NUMBER() OVER (ORDER BY DueDate ASC,(p.ListPrice - p.StandardCost) * wo.OrderQty DESC) from tablename
)a

0
 

Author Comment

by:SomeGuy892
ID: 22831945
chapmandew
I believe that I need to get the bgcolor attribute inside of the <tr> which is generated by the query.  So what I am looking to have generated is <tr bgcolor="green">.  If I can just figure out how to set the background color for the row then I could apply a case statement similar to what you have and get the background color to alternate for each row.  But since the <tr> is generated via XML I think??  I have not been able to do it.  I have tried a case statement similar to what you have and I can get bgcolor="green" to be returned in the results just not within the <tr> to get the row to change color properly.  I have been pounding on this for a while with no luck so any help is appreciated.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 60

Expert Comment

by:chapmandew
ID: 22832083
Let me play w/ it a bit...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22834071
this is a nasty one....
0
 

Author Comment

by:SomeGuy892
ID: 22834562
Yes it sure seems to be although I know there has to be an easy answer somewhere.  Here is a link to a related issue in another forum that might provide some clue to you.  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96536
0
 

Accepted Solution

by:
SomeGuy892 earned 0 total points
ID: 22837661
Whoo hooo I found the solution.  Thank you for your help chapmandew you may not have found the answer but your efforts and assistance was appreciated.  Here is where I found the answer:  http://msdn.microsoft.com/en-us/library/ms345137.aspx#forxml2k5_topic4
Look under the "The New PATH Mode" section.  What follows is an untested implementation of the answer to an Adventure works example from SQL Server Books Online:

DECLARE @tableHTML  NVARCHAR(MAX) ;
 

SET @tableHTML =

    N'<H1>Work Order Report</H1>' +

    N'<table border="1">' +

    N'<tr><th>Work Order ID</th><th>Product ID</th>' +

    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +

    N'<th>Expected Revenue</th></tr>' +

    CAST ( ( SELECT CASE WHEN (ROW_NUMBER() OVER (ORDER BY DueDate ASC,

                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC) % 2) = 1 then '#E0E0E0' else 'white' END as "@bgcolor", '',

		    td = wo.WorkOrderID,       '',

                    td = p.ProductID, '',

                    td = p.Name, '',

                    td = wo.OrderQty, '',

                    td = wo.DueDate, '',

                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty

              FROM AdventureWorks.Production.WorkOrder as wo

              JOIN AdventureWorks.Production.Product AS p

              ON wo.ProductID = p.ProductID

              WHERE DueDate > '2004-04-30'

                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 

              ORDER BY DueDate ASC,

                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC

              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;
 

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',

    @subject = 'Work Order List',

    @body = @tableHTML,

    @body_format = 'HTML' ;

Open in new window

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now