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
|
Gracias por tu publicación, amigo. Estaré siguiendo tu blog.
ReplyDeleteSaludos.
If I just want to change one cell's background color, how can I do it? Thank you very much!
ReplyDeleteHow to change the single cell background color???? Please help me out
ReplyDeleteI need the single cell :(
ReplyDeletecould you please suggest me for more than two conditions how can i handle like i want two change the background of cell if the number range between 0-50 then cell is red and for 51-90 it should be yellow and for 91-100 should be green
ReplyDeleteSorry, didn't see this question first time I logged in. Please see my response below, within subquery SELECT put in a CASE statement for multiple conditions something like of the subquery:
Delete(
select 'Left' As [@align],
CASE When [column] between 0 and 50 Then 'red'
When [column] between 51 and 90 Then 'yellow'
When [column] between 91 and 100 Then 'green'
End as [@bgcolor],
[column] As [data()]
For XML Path('td'),
Type
),
To change a single just make subquery SELECTs within parent SELECT. Something like:
ReplyDeleteCast( ( SELECT .....case when column = somevalue then '#F0000' end as @bgcolor,
(select ...'left' as @align, case when column = somevalue then '#F0000' end as @bgcolor, column as [data()],
For XML Path('td'),
Type
),
( select ...next column
),
( select ...next column
),
FROM table
WHERE condition
FOR XML Path ('tr'), Type
) As NVARCHAR(max) );
Took me a while to figure logic but keep in mind @bgcolor and @align are HTML tags. Here's a tag most folks didn't know they
can use in SQL and that's @style. I had to figure out myself couldn't find nothing about this on Google.
This comment has been removed by the author.
ReplyDelete