HTML Report using SQL
-
Hi,
I am creating a sql script that will generate a HTML report based on table contents. The below is the query that i designed for creating a HTML report based on the results of a Select Query. The query is working fine but i am facing a single problem in the query. In the result, i am getting the output as shown below.
declare @query nvarchar(max)
set @query =
'<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<style type=''text/css''>
<!--
table {
border-collapse: collapse;
border: 1px solid #3399FF;
font: 10pt verdana;
color: #343434;
}
table caption { font-weight: bold; background-color: white; }
table td, table th, table caption { border: 1px solid #3399FF; }
table th { background-color: #3399FF; font-weight: bold; }
-->
</style>
<title>Generated table</title>
</head>
<body>
<table>
<caption>Test Data</caption>
<tr><th>Employee Name</th><th>Days PastDue > 60 < 90</th><th>Days PastDue > 90 < 120</th><th>Days PastDue > 120</th></tr>' +
cast(
(select
td = EMPLOYEE_NAME, ' ',
td = Sum(case when [PASTDUE>60<90] is null then '0' else [PASTDUE>60<90] end), ' ',
td = Sum(case when [PASTDUE>90<120] is null then '0' else [PASTDUE>90<120] end), ' ',
td = Sum(case when [PASTDUE>120] is null then '0' else [PASTDUE>120] end)
from db_ITG.dbo.training_report where [PASTDUE>60<90] <> 0
or [PASTDUE>90<120] <> 0 or [PASTDUE>120] <> 0
group by Employee_Name
ORDER by Employee_Name
forxml path('tr'), type ) as nvarchar(max)) +
'</table>
</body>
</html>'
select @query
The above code is working perfectly except with one issue. I have attached the report generated by the above query for reference. Some of the values in the report are displayed bfore the table and not in the appropriate column highlighted in the attached screenshot.
.The values of two columns are displayed out the table and the place where it has to be displayed by default is showing as empty.
I want all the values to be displayed only inside the table and not outside.
Can you please help me how to overcome this error and can have all the values inside the table itself.
Thanks in advance
Karthik V
-
run the query without the html formatting and check if the xml result is what you're looking for. If so, manually add the html format to the xml result.
The error might be caused by some special character inside the generated xml.
-
Hi Lutz,
previously i had the coding without HTML Tag and i had the same output. below is my HTML tag
declare @query nvarchar(max)
set @query =
'<style type = ''text/css''>' +
'table { border-collapse:collapse; border:1px solid #3399FF; font:10pt verdana; color:#343434; }' +
'table td, table th, table caption { border:1px solid #3399FF; }' +
'table caption { font-weight:bold; background-color:white }' +
'table th background-color:#3399FF; font-weight:bold; }' +
'</style>' +
'<table>' +
'<caption>ITG Outstanding Training Report Details</caption>' +
'<tr><th>Employee Name</th><th>DAYSPASTDUE > 60 < 90</th><th>DAYSPASTDUE > 90 < 120</th><th>DAYSPASTDUE > 120</th></tr>' +
cast(
(
(select
td = '',
td = EMPLOYEE_NAME,' ',' ',
td = Sum(case when [PASTDUE>60<90] is null then '0' else [PASTDUE>60<90] end),' ',
td = Sum(case when [PASTDUE>90<120] is null then '0' else [PASTDUE>90<120] end),' ',
td = Sum(case when [PASTDUE>120] is null then '0' else [PASTDUE>120] end)
from db_ITG.dbo.training_report where [PASTDUE>60<90] <> 0
or [PASTDUE>90<120] <> 0 or [PASTDUE>120] <> 0
group by Employee_Name
ORDER by Employee_Name
forxml path('tr'), type ) as nvarchar(max)) +
'</table>
select @query
-
I've seen the html tag before. No reason to repost it. 😉
I don't think you had "the same" output with and without the html tag...
My question was: what is the XML output without the html formatting? Have a look at it and see if there's the same issue.
If you'd like us to have a look at it, please provide ready to use sample data as described in the first link in my signature.
Without having the table and the data we can't narrow down the issue.
-
Hi LutzM
Here is the XML output of the query
select
EMPLOYEE_NAME,
Sum(case when [PASTDUE>60<90] is null then '0' else [PASTDUE>60<90] end),
Sum(case when [PASTDUE>90<120] is null then '0' else [PASTDUE>90<120] end),
Sum(case when [PASTDUE>120] is null then '0' else [PASTDUE>120] end)
from db_ITG.dbo.training_report where [PASTDUE>60<90] <> 0
or [PASTDUE>90<120] <> 0 or [PASTDUE>120] <> 0
group by Employee_Name
ORDER by Employee_Name
FOR XML PATH
Is this what you are speaking about XML Output. As i am a newbie i dont know much about sql server. kindly spare me if there are any mistakes. The table structure is as follows
EMPLOYEE_NAMEvarchar(50),
[PASTDUE>60<90] INT,
[PASTDUE>90<120] INT,
[PASTDUE>120] INT
The columns [PASTDUE>60<90] , [PASTDUE>90<120] and [PASTDUE>120] consists of numbers only.
Let me know how can i provide u sample data.
Thanks,
Karthik
-
Please remove the xml sample since it obviously contains personal information (you crrectly obfuscated it in your original screenshot).
I don't think it's the output of the original statement except the html stuff since I can't see td or tr as tag names. I also asked to provide table def and sample data instead of just the xml result.
-
Hi LutzM,
Here is the XML Output for .
The HTML output of the query is
The table def is
NameOwnerTypeCreated_datetime
training_reportdbouser table53:55.5
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
EMPLOYEE_NAMEvarcharno50 yesnoyesLatin1_General_CI_AI
PASTDUE>60<90intno4100yes(n/a)(n/a)NULL
PASTDUE>90<120intno4100yes(n/a)(n/a)NULL
PASTDUE>120intno4100yes(n/a)(n/a)NULL
IdentitySeedIncrementNot For Replication
No identity column defined.NULLNULLNULL
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
How can i send you the sample data?
-
I copied your result into a html file and it looked just fine. Based on the data I cannot verify the result you see.
What do you use to view the result? Did you try to save it as an html file?
-
Hi,
i am sending this HTML output as a web report to email. The query that i use to send email is as follows
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SmarterMail',
@recipients = abc@myemail.com',
@subject = 'ITG Outstanding Training Report Details',
@query = 'exec db_ITG.dbo.sp_sendHTMLemail',
@body_format = 'html',
@query_no_truncate = 1
When i copied it to a HTML file, im also getting the correct result. But when in email the result is showing as the one in screenshot.
-
Did you try to email it as an attached html file?
It seems like it's an issue with the embedded html data of sp_send_dbmail.
At least we exluded the query being a possible cause.
-
hi,
i tried sending the report to gmail and it worked fine. I did not find any data mislocated but i am not able to have the table colors and formattings. I think there are some formatting issues with the mail server that i use locally. due to that i am not able to get the desired output.
I will try to sort it out and will post it for you.
Anyways thanks for helping me out. Because of you I have learnt a new concept of XML output in sql server. 🙂
Thanks,
Karthik V
-
This seems like a lot more work than it should be...is SSRS available in your environment or can it be made available? SSRS is the recommended solution for this type of problem (replaces sp_makewebtask too). SSRS can generate reports from a tabular resultset and those reports can easily be delivered as an HTML email. Don't get me wrong, XML skills are critical, but in this case SSRS would alleviate the need for all the manual Frankensteining issues you're working through 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (5/31/2011)
(replaces sp_makewebtask tooBWAA-HAAA!!!! In my eyes, there will never be a sufficient replacement for that wonderful tool. 🙂
--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) -
You would have something to say about that...does xp_cmdshell know about your love affair with sp_makewebtask? I bet she'd be jealous 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Ahhhh... you've left out the third member of the team to make it all happen securely; the Master database. 😉
--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)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply