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
847 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 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