?
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
Medium Priority
?
896 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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