June 20, 2011 at 5:03 am
Hi ,
I Generate reports using Database Mail. By default the alignment for columns in the Table is LEFT Justified. For the below example I want Center alignment for the column Work Order ID, Product ID, Order Qty, Due Date & RIGHT Alignment for Expected Revenue. How can I do this?
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1" >' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='nithin@mystifly.com',
@profile_name = 'Test2',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
Regards,
Nithin
June 20, 2011 at 5:40 am
One option is to do this:
SET @tableHTML = REPLACE(@tableHTML,'<td>','<td align="right">');
There might be a better way to do via the XML also - I don't know how to do that 😉
June 20, 2011 at 7:11 am
I need to align td as i require. Like some column with right align and some column with center align ans so on.I tried your solution but doesn't work for me. I have very little knowledge of html so please help.
June 20, 2011 at 8:50 am
I got the solution.
I had to add "'<ALIGNMENT TYPE>' AS 'td/@align'," before each td as highlighted in the code shown below:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1" >' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT
'center' AS 'td/@align',
td = wo.WorkOrderID, '',
'center' AS 'td/@align',
td = p.ProductID, '',
'left' AS 'td/@align',
td = p.Name, '',
'center' AS 'td/@align',
td = wo.OrderQty, '',
'center' AS 'td/@align',
td = wo.DueDate, '',
'right' AS 'td/@align',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='nithin@mystifly.com',
@profile_name = 'Test2',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
Thanks a lot for the time taken to help me out!!!!!!:-):-):-)
June 21, 2011 at 12:53 am
And thanks for taking the time to post the solution - this is good stuff to know (for me at least :-))
December 20, 2013 at 2:31 pm
This is a really old post i dont expect nothing just mind blowing to me. How in the bleep did you figure that out....... Anyway thanks guys for SQL central for leaving that around and you guys for posting that, saved me possible hours.
December 20, 2013 at 9:34 pm
freewaresucks (12/20/2013)
This is a really old post i dont expect nothing just mind blowing to me. How in the bleep did you figure that out....... Anyway thanks guys for SQL central for leaving that around and you guys for posting that, saved me possible hours.
Oh lordy! Be careful here. The code is using "cell level" formatting and the given code above creates a body of more than 29 MILLION bytes!!! That's a really, really expensive eMail. Even if you're using it for much smaller stuff, using CSS would help trim the length of such emails tremendously.
I'm trying to work up an example but I don't know if I'll finish it tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2013 at 9:20 pm
ok good point ill try using css, but tomorrow
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply