Solved

Controlling the format of individual columns in a query using sp_send_dbmail

Posted on 2011-03-22
2
1,743 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:kcoxon
2 Comments
 
LVL 4

Accepted Solution

by:
Ztinel earned 500 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:kcoxon
ID: 35188920
Brilliant. Thanks.
Watch out for the next question following shortly.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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