?
Solved

Controlling the format of individual columns in a query using sp_send_dbmail

Posted on 2011-03-22
2
Medium Priority
?
1,965 Views
Last Modified: 2012-05-11
I have written a simple query to email data using sp_send_dbmail as per the example in Experts Exchange.
I use style sheets to control the format and this works fine for all columns returned using the style for td but I have a need to control the format of individual columns of data only e.g. control the text alignment/font size/background colour etc of the value column data independently of the column header.
Can this be done? If so how please.

DECLARE @tableHTML  NVARCHAR(MAX);

SET @tableHTML =
 N'
      
<head>
      <STYLE TYPE="text/css">

p.title{font-family:arial;font-size:12pt;color:blue;font:bold}
th{font-family:arial;font-size:10pt;color:white;background-color:blue;text-align:left;padding-right:10px}
td{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black}
</STYLE>
</head>
      <p class="title">Sales Report UK</p>
    <table>
    <tr>
    <th>Period</th>
    <th>Posting Date</th>
    <th>Salesperson</th>
    <th>Ship To Customer</th>
    <th>Item.....</th>
    <th>Description</th>
    <th>Qty</th>
    <th>Value (£)</th></tr>' +
    CAST ( ( SELECT td = Period,'',
                              td = [Posting Date],'',
                    td = Salesperson, '',
                    td = Customer, '',
                    td = Item, '',
                    td = Description, '',
                    td = Qty, '',
                    td = [Line Val (LCY)]
              FROM [$ UK Sales Analysis] AS SA
              WHERE [Product Series]='ProductA'
              ORDER BY Period,[Posting Date],Salesperson,Customer
              --GROUP BY Period
              --ORDER BY [Product Series]
             
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>
    <p class="title">Sales Report France</p>
    <table class="table">
    <tr class="header">
    <th>Period</th>
    <th>Posting Date</th>
    <th>Salesperson</th>
    <th>Ship To Customer</th>
    <th>Item</th>
    <th>Description</th>
    <th>Qty</th>
    <th>Value(€)</th></tr>' +
    CAST ( ( SELECT td = Period,'',
                              td = [Posting Date],'',
                    td = Salesperson,'',
                    td = Customer,'',
                    td = Item,'',
                    td = Description,'',
                    td = Qty,'',
                    td = [Line Val (€)]
              FROM [$ FR Sales Analysis] AS SA
              WHERE [Product Series]='ProductA'
              ORDER BY Period,[Posting Date],Salesperson,Customer
              --GROUP BY Period
              --ORDER BY [Product Series]
             
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table> '

EXEC msdb.dbo.sp_send_dbmail
      @recipients='xxx@yyy.com',
    @subject = 'Sales Report',
    @body = @tableHTML,
    @body_format = 'HTML',
      @profile_name ='SQL'
0
Comment
Question by:Paul G
2 Comments
 
LVL 4

Accepted Solution

by:
Ztinel earned 2000 total points
ID: 35188492
hi, try changing portion of your code as below:
...
CAST ( ( SELECT 
                [td/@class]='anyclassname1',
                td = Period,
                "*"='',
                [td/@class]='anyclassname2',
                td = [Posting Date],
                "*"='',
                [td/@class]='anyclassname3',
                td = Salesperson,
                "*"='',
                [td/@class]='anyclassname4',
                td = Customer,
                "*"='',
                [td/@class]='anyclassname5',
                td = Item,
                "*"='',
                [td/@class]='anyclassname6',
                td = Description,
                "*"='',
                [td/@class]='anyclassname7',
                td = Qty,
                "*"='',
                [td/@class]='anyclassname8',
                td = [Line Val (LCY)]
              FROM [$ UK Sales Analysis] AS SA 
              WHERE [Product Series]='ProductA'
              ORDER BY Period,[Posting Date],Salesperson,Customer
              --GROUP BY Period 
              --ORDER BY [Product Series]
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
...

Note: create a css classes for each td above.

Open in new window

0
 

Author Closing Comment

by:Paul G
ID: 35188920
Brilliant. Thanks.
Watch out for the next question following shortly.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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