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
858 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 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