Solved

Summarising data in a query using sp_send_dbmail

Posted on 2011-03-22
4
874 Views
Last Modified: 2012-08-14
I have written a simple query to email data using sp_send_dbmail as per the example in Experts Exchange.
I need to add another row at the bottom of the table(s) in which I can summarise the data in the column above e.g. sum Qty or Line Val

Help would be much appreciated.
Thanks.

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}
.data{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:left;padding-right:20px}
.datara{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:right;padding-left:10px}
</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/@class]='data',
                td = Period,
                "*"='',
                [td/@class]='data',
                td = [Posting Date],
                "*"='',
                [td/@class]='data',
                td = Salesperson,
                "*"='',
                [td/@class]='data',
                td = Customer,
                "*"='',
                [td/@class]='data',
                td = Item,
                "*"='',
                [td/@class]='data',
                td = Description,
                "*"='',
                [td/@class]='datara',
                td = Qty,
                "*"='',
                [td/@class]='datara',
                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/@class]='data',
                td = Period,
                "*"='',
                [td/@class]='data',
                td = [Posting Date],
                "*"='',
                [td/@class]='data',
                td = Salesperson,
                "*"='',
                [td/@class]='data',
                td = Customer,
                "*"='',
                [td/@class]='data',
                td = Item,
                "*"='',
                [td/@class]='data',
                td = Description,
                "*"='',
                [td/@class]='datara',
                td = Qty,
                "*"='',
                [td/@class]='datara',
                    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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35189966
I would be inclined to incapsulate this into a stored procedure. Inside the sp, create a temporary table matching your output. Insert the rows from the UK query then, insert another row with the totals. Repeat the process for France. For example:

DECLARE @UKOut TABLE(Period VARCHAR(50),
[Posting Date] VARCHAR(50),
Salesperson VARCHAR(50),
Customer VARCHAR(50),
Item VARCHAR(50),
Description VARCHAR(50),
Qty VARCHAR(50),
[Line Val (LCY)] VARCHAR(50));

INSERT INTO @UKOut
SELECT Period,
[Posting Date],
Salesperson,
Customer,
Item,
Description,
Qty,
[Line Val (LCY)]
FROM [$ UK Sales Analysis] AS SA
WHERE [Product Series]='ProductA'
ORDER BY Period,[Posting Date],Salesperson,Customer;

INSERT INTO @UKOut (Qty, [Line Val (LCY)])
SELECT SUM(Qty),
SUM([Line Val (LCY)])
FROM [$ UK Sales Analysis] AS SA
WHERE [Product Series]='ProductA';

Once you have your temporary tables, use these in your statement to populate @tableHTML. One thing to note that took me hours to find. Even though you have defined @tableHTML as NVARCHAR(MAX), you can't add chunks any larger than 8,000 (if memory serves correctly) characters at a time. So, you'll want to modify your code to be something like:

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}
.data{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:left;padding-right:20px}
.datara{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:right;padding-left:10px}
</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>';

SET @tableHTML = @tableHTML +
...add the next logical group of lines here, probably the select statement for the table.

Keep adding each "logical group" like this so you are not adding more than 8,000 characters at a time.

One more thing to note with HTML emails: web clients such as Gmail strip out all of your styles and only keep the body of your message. We have had to style each individual tag to get our emails to display correctly for client based email as well as web based email.
0
 

Author Comment

by:kcoxon
ID: 35191244
Thanks. I have already fallen foul of the HTML formatting in different mail clients. It's a pain!

Your suggestion works weel, although I'll have to play around creating the 'logical groups' to which you refer.

What I do find is that the second insert into UKOut for the sum inserts at the top of the table.

What I have so far is this.

DECLARE @UKOut TABLE(Period VARCHAR(50),
[Posting Date] VARCHAR(50),
Salesperson VARCHAR(50),
Customer VARCHAR(50),
Item VARCHAR(50),
Description VARCHAR(50),
Qty VARCHAR(50),
[Line Val (LCY)] VARCHAR(50));

INSERT INTO @UKOut
SELECT Period,
[Posting Date],
Salesperson,
Customer,
Item,
Description,
Qty,
[Line Val (LCY)]
FROM [$ UK Sales Analysis] AS SA
WHERE [Product Series]='ProductA'
ORDER BY Period,[Posting Date],Salesperson,Customer;

INSERT INTO @UKOut (Qty, [Line Val (LCY)])
SELECT SUM(Qty),
SUM([Line Val (LCY)])
FROM [$ UK Sales Analysis] AS SA
WHERE [Product Series]='ProductA';


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}
.data{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:left;padding-right:20px}
.datara{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:right;padding-left:10px}
</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/@class]='data',
                td = Period,
                "*"='',
                [td/@class]='data',
                td = [Posting Date],
                "*"='',
                [td/@class]='data',
                td = Salesperson,
                "*"='',
                [td/@class]='data',
                td = Customer,
                "*"='',
                [td/@class]='data',
                td = Item,
                "*"='',
                [td/@class]='data',
                td = Description,
                "*"='',
                [td/@class]='datara',
                td = Qty,
                "*"='',
                [td/@class]='datara',
                td = [Line Val (LCY)]

              FROM @UKOut
             
              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 = 'LanXPLORER Sales Report',
    @body = @tableHTML,
    @body_format = 'HTML',
      @profile_name ='NAVSQL'
0
 
LVL 4

Accepted Solution

by:
jimtpowers earned 500 total points
ID: 35192550
You could add an identity column to the temp table so that when you query the temp table to output, order by the identity column and the sum row will end up at the bottom.

As far as the "logical groups" is concerned, I resorted to a SET @tableHTML = @tableHTML + ... for each line that would normally appear if you were writing it as a regular HTML file. It is a bit more work but I'm a bit picky about everything lining up. A good way to break up your code would be to add the title and table header row as a group, the query as a group, the next title and header row as a group, and so on.
0
 

Author Closing Comment

by:kcoxon
ID: 35192891
Tried as you suggested and now I have the summary values at the bottom.
Thanks.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 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