The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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 snippet 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
> [td/@class]='anyclassname1
> [td/@class]='anyclassname2
I have tried this and substituted [td/@class]='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='s_smith@sampl
> @from_address = 's_smith@sample.com',
> @subject = @subject,
> @body = @nbody,
> @body_format = 'HTML' ;
Table.JPG
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
> [td/@class]='anyclassname1
> [td/@class]='anyclassname2
I have tried this and substituted [td/@class]='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='s_smith@sampl
> @from_address = 's_smith@sample.com',
> @subject = @subject,
> @body = @nbody,
> @body_format = 'HTML' ;
Table.JPG
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Thanks for your help. I am in the same boat with you on CSS knowledge as well. Could you advise as to where I would nominate the class in the query to refer back to it? I am truly at a complete loss here on how to do this.
Thanks!
Thanks!
> #box-table
box-table is a class name. Now look back at your question. You will see box-table toward the top of your code there.
So you already have some class names
In html the table tag should look something like
<table class="box-table">
Then elements auch as <td> are controlled by the box-table td already defind.
Can you capture the generated @tableSummary result and provide it?
In a code block please.
box-table is a class name. Now look back at your question. You will see box-table toward the top of your code there.
So you already have some class names
In html the table tag should look something like
<table class="box-table">
Then elements auch as <td> are controlled by the box-table td already defind.
Can you capture the generated @tableSummary result and provide it?
In a code block please.
Think I have found a blog page that will clarify what you are attempting:
Format DBMail with HTML and CSS
extract:
DECLARE @Style NVARCHAR(MAX)= '';
...
SET @Style += ...
+ N'.tg .tg-9ajh{font-weight:bold;backgrou
+ N'.tg .tg-hgcj{font-weight:bold;text-ali
+ N'</style>';
.....
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="2">Order Information</th>'
+ N'<th class="tg-hgcj" colspan="2">Summary</th>'
+ N'</tr>'
/*
Define Column Sub-Headers
*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Order Date</td>'
...
Format DBMail with HTML and CSS
extract:
DECLARE @Style NVARCHAR(MAX)= '';
...
SET @Style += ...
+ N'.tg .tg-9ajh{font-weight:bold;backgrou
+ N'.tg .tg-hgcj{font-weight:bold;text-ali
+ N'</style>';
.....
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="2">Order Information</th>'
+ N'<th class="tg-hgcj" colspan="2">Summary</th>'
+ N'</tr>'
/*
Define Column Sub-Headers
*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Order Date</td>'
...
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,
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@sampl
@from_address = 's_smith@sample.com',
@subject = @subject,
@body = @nbody,
@body_format = 'HTML' ;
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,
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@sampl
@from_address = 's_smith@sample.com',
@subject = @subject,
@body = @nbody,
@body_format = 'HTML' ;
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
CSS
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
> [td/@class]='anyclassname2
the classes are: anyclassname1 & anyclassname2
My CSS knowledge is rudimentary at best, but once you nominate a class, then the class itself should contain the wanted alignment