Using CSS to control formatting in dbmail
-
I have written a query and currently have a job that runs and sends an email using sp_send_dbmail in a formatted table if certain conditions are met. 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, i.e. control the text alignment, data type, color, etc. of the value column data independently of the column header. I would like to format the individual columns below to a currency if possible and right justify them. Even if I can not format to currency, if i could format to a number format with a comma separated value (XX,XXX) I can concatenate the $ sign. I have attached a snipped of the table output as well as the code.
FYI- there is another table below the summary table shown here but I did not send it. Much similar in nature, I just need guidance on getting the style sheet embedded to work.I found during my search on the web a query written almost identical to mine with the same issue and the response was as follows:
Note: create a css classes for each td above.
CAST
(( SELECT='anyclassname1', td = [Column Name 1], "*"='',
='anyclassname2', td = [Column Name 2], "*"='', etc.
I have tried this and substituted
='text-align: right', and can not get it to work.
Any help would be greatly appreciated.DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableSummary nvarchar(max)SET @subject = 'Matter Alert for 189274'
SET @tableSummary =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: left;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
padding: 5px;
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
padding: 5px;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">Matter Management Alert</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green">
<th>Base Rate Value (B)</th>
<th>Value at A Rate</th>
<th>Value at C Rate</th>
<th>Value at $125/Hr</th>
<th>Threshold</th>
</tr>' +
CAST ( (SELECT
td = cast(sum(itdr.[Base Amt]) as numeric(25,2)),'',
td = cast(sum(itdr.[A Rate Value]) as numeric(25,2)),'',
td = cast(sum(itdr.[C Rate Value]) as numeric(25,2)),'',
td = cast( sum(
CASE WHEN itdr.[Employee Code] = 'TLW'
THEN (itdr.[Base Hrs] * 125)
ELSE (itdr.[Base Amt])
END
) as numeric(25,2)),'',
td = '5000'
FROM Intl_Tax_Data_Run itdr
where itdr.[Matter Code] = '189274'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +N'</table>'
declare @nbody varchar(max)
set @nbody = @tableSummary
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients='stevesmith@abcco.com',
@from_address = 'stevesmith@abcco.com',
@subject = @subject,
@body = @nbody,
@body_format = 'HTML' ;
-
Have you looked at using the FORMAT function in SQL Server? This might be able to avoid the necessity of having to let CSS convert numeric values to strings, and you can just do the color stuff with it, while the text itself would already contain any format characters like $ and/or commas.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Sorry for the delayed response but I found the solution. When I converted the td to money, it right aligned the columns. It also did this when I concatenated the $ and added the "," inside the 5000.
In the latter part of the query which is not written above, I did have to do some tweaking to the query. It has both varchar and numeric columns in the td. I had to redefine the classes of my table. See below:
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @Style nvarchar(max)= '';
DECLARE @tableHTML nvarchar(max)= '';SET @subject = 'Matter Alert for 101853'
SET @Style +=
+ N'<style type="text/css">' +
N'.tg
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: left;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
' + N'.tg td
{
padding: 5px;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
' + N'.tg th
{
padding: 5px;
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
} ' + N' tr:nth-child(odd) { background-color:#eee; }
' + N' tr:nth-child(even) { background-color:#fff; }
' + N'<tr><font color="Green">
' + N'</style>'
;SET @tableHTML += @Style + @tableHTML +
N'<table class="tg">' --DEFINE TABLE
/*
Define Column Headers and Column Span for each Header Column
*/+ N'<tr>'
/*
Define each th
*/+ N'<th>Employee Name</th>'
+ N'<th>Base Rate Value</th>'
+/*
Define data for table and cast to xml
*/CAST ( (
SELECT
'left' as 'td/@align', td = itdr.[Employee Name],'',
'right' as 'td/@align', td = '$' + PARSENAME(CONVERT(VARCHAR,CAST(cast(sum(itdr.[Base Amt]) as numeric(25,2)) AS MONEY),1),2)
FROM Intl_Tax_Data_Run itdr
where itdr.[Matter Code] = '189274'
group by itdr.[Employee Name]FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'declare @nbody varchar(max)
set @nbody = @tableHTML
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients='s_smith@sample.com',
@from_address = 's_smith@sample.com',
@subject = @subject,
@body = @nbody,
@body_format = 'HTML' ; -
Kudos for getting it to work but I can't help feeling that you are building up some technical debt here. Managing that email going forwards could prove difficult. Have you looked at delivering the infomation via Reporting Services and subscriptions, (I believe that you need enterprise edn for subscriptions)
As a general principle I would shy away from getting SQL to do the formatting. SQL for data, reporting tools for presentation
- aaron.reese - Monday, December 4, 2017 5:29 AM
Heh... you say that but I have a bit of personal hatred for SSRS and subscriptions. They're more of a pain to maintain than a nice, simple, stored procedure.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The problem we have is who I am sending to is not going to open an attachment in a report. They need to be able to see the information in the body of the email. I agree that it is much more to manage, but I have to worry about presentation to the core audience.
-
I feel your pain and although I also agree with Jeff that subscriptions can be a pain, I would still be looking at reporting services as an option.
If it is their job to get this information then SSRS, especially if you can install the copy with PowerBI rendering built in would be the way to go. As part of SSRS you get an automatic log of who ran what reports, when and with what parameters so these 'lazy' users can be performance managed as to whether they are actually running their operational reports.
According to this article, you can embed the report in the body of the email rather than as an attachement. Don't know which version of SSRS or edition you need for this facility
- katie.vaughan - Tuesday, December 5, 2017 8:20 AM
Ditto that. Same problem here... especially if I want them to actually look at what the message says. So, I format this stuff into the email body. A lot of folks say not to do such things in SQL Server but it's a whole lot faster than SSRS and takes very few resources (couple of milliseconds) to do so.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply