Normally if we like to track our data from the DB on a daily basis, either we generate some reports using SSRS and schedule the reports to specific mail ID, or we write some queries and send the summary data as a report in a mail using sp_send_dbmail in an HTML format.
If there are some specfic values which we are tracking on a daily basis, and if we want to highlight the particular row with specific color based on some conditions, it is easy to set the color dynamically in SSRS.
To achieve the same using sp_send_dbmail , we have to write SQL queries which generate such kind of XMLs.
Consider the below example. Suppose we have a table which has data as below
place
|
Temperature
|
Sirsi
|
30
|
Bangalore
|
35
|
Hyderabad
|
45
|
Mumbai
|
41
|
We have to highlight the data for which the temperature is more than 40 degrees. We can highlight either by making it as bold/changing background color/changing font color
Dynamic Background Color Change
To dynamically change background color we need xml as given below:
<tr bgcolor = "red">
<td>Hyderabad<td>
<td>45</td></tr>
<tr bgcolor = "white">
<td>Sirsi<td>
<td>30</td></tr>
Note: This is just a sample xml
We can generate such XML using below query
SELECT
CASE WHEN temperature > 40 THEN '#F78181'
ELSE 'white'
END AS [@bgcolor],
td = place, '',
td = temperature, ''
FROM test_fontcolor
FOR XML PATH('tr')
To send an email use the below format
declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th>
<th>Temperature</th>' +
N'</tr>' +
CAST ( (
SELECT
CASE WHEN temperature > 40 THEN '#F78181'
ELSE 'white'
END AS [@bgcolor],
td = place, '',
td = temperature, ''
FROM test_fontcolor
FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='WP Store BI Notifications',
@recipients='v-raheg@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Background Color',
@body = @tableHTML,
@body_format = 'HTML' ;
The output of would be as below:
Test Data
TEST_sub_header
TEST_header_1
place
|
Temperature
|
Sirsi
|
30
|
Bangalore
|
35
|
Hyderabad
|
45
|
Mumbai
|
41
|
Dynamaic Font Color Change
To change the color of the font dynamically the XML should look like as below:
<tr>
<font color = "red">
<td> Hyderabad </td>
<td> 45 </td>
</font>
</tr>
<tr>
<font color = "black">
<td> Sirsi</td>
<td> 30 </td>
</font>
</tr>
SQL query to generate XML as above would look like :
SELECT
CASE WHEN Temperature>40 THEN 'red'
ELSE 'black'
END AS "font/@color",
place as "font/td",'',
CASE WHEN Temperature>40 THEN 'red'
ELSE 'black'
END AS "font/@color",
Temperature as "font/td"
FROM
test_fontcolor
ORDER BY place
FOR XML PATH('tr')
NOTE: Above query does not generate XML exactly like above. For every td tag there would be one font tag. It will generate like:
<tr>
<font color = "red">
<td> Hyderabad </td>
</font>
<font color = "red">
<td> 45 </td>
</font>
</tr>
To send an email use the below code:
declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th><th>Temperature</th>' +
N'</tr>' +
CAST ( ( SELECT
CASE WHEN Temperature>40 THEN 'red'
ELSE 'black'
END AS "font/@color",
place as "font/td",'',
CASE WHEN Temperature>40 THEN 'red'
ELSE 'black'
END AS "font/@color",
Temperature as "font/td"
FROM
test_fontcolor
ORDER BY place
FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='WP Store BI Notifications',
@recipients='v-raheg@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;
The out put would be :
Test Data
TEST_sub_header
TEST_header_1
place
|
Temperature
|
Sirsi
|
30
|
Bangalore
|
35
|
Hyderabad
|
45
|
Mumbai
|
41
|
