create total record in html email
-
Thanks to you BOTH for taking the time posting the code, lot to digest...
I just have a question, would it be possible to also include a row total for each entity?
-
@jeff When I ran your code and seeing what @SQL has in it, that's exactly what I was trying to achieve. How to you get that into an
email to be sent using dbmail
- Bruin wrote:
Thanks to you BOTH for taking the time posting the code, lot to digest...
I just have a question, would it be possible to also include a row total for each entity?
Yes, but you've not posted any data that would need it. 😉
--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) - Bruin wrote:
@jeff When I ran your code and seeing what @SQL has in it, that's exactly what I was trying to achieve. How to you get that into an email to be sent using dbmail
Simple... visit the following URL and see example "B" and "C". You've got to have some of the fun on this. 😀
--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) -
If by row total - you mean an additional column at the end, then please review the thread I linked to in a previous message. That code adds a totals column and shows how to incorporate the totals column into the HTML table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Won't the header record have to be built dynamically?
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
From (
Values ('Entity', 'Description', 'Total')
) As html(hdr1, hdr2, hdr3)
For xml Path('tr'), elements) As varchar(max));
Since what's in @SQL is exactly what I need in an HTML(table) email I thought there would be a way to reuse that, since it's already done the dynamic build.
THanks.
-
There were a couple of small mistakes in the header code I posted - here is the updated version:
--==== Create the table header
Select @tableHeader = concat('<tr>', cast(stuff((Select h.Entity As th, ''
From (Select Distinct
ft.Entity
From #frt_tracker ft
) As h
Order By
h.Entity
For Xml Path('')), 1, 0, '<th></th>') As varchar(max)), '</tr>');
Select cast(@tableHeader As xml);The results from this:
<tr>
<th />
<th>
200
</th>
<th>
300
</th>
<th>
600
</th>
</tr>This will dynamically create the column headers. Add a new row in the #frt_tracker table for Entity 400 and a new column header will be added.
To add a 'Totals' column - we can modify the above code:
--==== Create the table header
Select @tableHeader = concat('<tr>', cast(stuff((Select h.Entity As th, ''
From (Select Distinct
ft.Entity
From #frt_tracker ft
) As h
Order By
h.Entity
For Xml Path('')), 1, 0, '<th></th>') As varchar(max)), '<th>Totals</th></tr>');
Select cast(@tableHeader As xml);To get the total across the group - we just need to add the sum of the TotAmt:
--==== Create the cross-tab query and format as XML for table details
Declare @sqlCommand nvarchar(max) = '
Select @xmlResults = cast((Select Case When grouping_id(ft.[Description]) = 1 Then ''Totals'' Else ft.[Description] End As td, ''''
' + @columnList + '
, sum(ft.TotAmt) As td, ''''
From #frt_tracker ft
Group By Grouping Sets (
()
, (ft.[Description])
)
Order By grouping_id(ft.[Description])
For Xml Path(''tr'')) As varchar(max))';Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
That worked perfect except if there is no value for a description can it put a 0(zero) than the total at the end of the line should populate.
currently if not values exist(description Totlamt) for a entity then it's a blank and you never get a line total populated.
Thanks for quick reply.
-
Small change - you need to use this:
--==== Build the table data and return in the xmlResults variable
Declare @columnList varchar(max) = cast((Select concat(', coalesce(sum(Case When ft.Entity = ', h.Entity, ' Then ft.TotAmt End), 0) As td, ''''')
From (Select Distinct
ft.Entity
From #frt_tracker ft
) As h
Order By
h.Entity
For Xml Path('')) As varchar(max));Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Totally Awesome!!!!
Many Thanks!!!!
- Bruin wrote:
Totally Awesome!!!!
Many Thanks!!!!
Ok... so let's see your final output as an attached file, please.
--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) -
Yes - please show the full script/procedure. This can and will help someone else and will be a good contribution to the community.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Will upload shortly...
Thanks again to you Both...
-
I update a piece in the Row totals section to format for Currency, but I couldn't get it to work for total amounts for each description
and column totals.
Any chance you can look?
Thanks.
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = 'xxx@.com'
, @cc_recipients varchar(max) = '';
--==== Create the table header
Select @tableHeader = concat('<tr>', cast(stuff((Select h.Entity As th, ''
From (Select Distinct
ft.Entity
From freight_Tracker ft
) As h
Order By
h.Entity
For Xml Path('')), 1, 0, '<th></th>') As varchar(max)), '<th>Totals</th></tr>');
Select cast(@tableHeader As xml);
Select @tableHeader;
--==== Build the table data and return in the xmlResults variable
Declare @columnList varchar(max) = cast((Select concat(', coalesce(sum(Case When ft.Entity = ', h.Entity, ' Then ft.total End), 0) As td, ''''')
From (Select Distinct
ft.Entity
From freight_tracker ft
) As h
Order By
h.Entity
For Xml Path('')) As varchar(max));
--==== Create the cross-tab query and format as XML for table details
Declare @sqlCommand nvarchar(max) = '
Select @xmlResults = cast((Select Case When grouping_id(ft.[Description]) = 1 Then ''Totals'' Else ft.[Description] End As td, ''''
' + @columnList + '
, format(sum(ft.total),''C'', ''en-US'' ) As td, ''''
From freight_tracker ft
Group By Grouping Sets (
()
, (ft.[Description])
)
Order By grouping_id(ft.[Description])
For Xml Path(''tr'')) As varchar(max))';
Print @sqlCommand;
Execute sp_ExecuteSql @sqlCommand, N'@xmlResults varchar(max) out', @xmlResults = @xmlResults out;
Select cast(@xmlResults As xml);
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:9pt; text-align:center; border:1px DarkCyan solid; padding:1px 5px 1px 5px;}';
Set @body += 'th {font-size:10pt; text-align:center; padding:2px 5px 2px 5px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body>
Daily Tracker ';
--==== Setup the table with the header and details
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
--==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'xxxx'
, @from_address = 'xxx@.com'
, @recipients = @recipients
, @subject = 'Subject'
, @body_format = 'HTML'
, @body = @body;
END -
For something like this - you can probably get away with using FORMAT although it is incredibly slow. You can also use concat and convert to get the same result: concat('$', convert(varchar(12), sum(ft.TotAmt), 1)).
So - where do you build out the code for the other dollar amounts? Find that and you will know where to make the changes.
The next issue will be defining the alignment of the money fields. Generally you want those to align to the right and the CSS style has that set to align to the center. You will want to modify the CSS for td to align:right - and then overwrite the alignment for the Description column. To overwrite add this before the description column:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply