asked
Sending 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. CREATE procedure [dbo].[sp_sendHTMLemail] as begin declare @query nvarchar(max) set @query = ' ' + ' ' + ' ITG Outstanding Training Report Details ' + ' Employee NameDAYSPASTDUE > 60 < 90DAYSPASTDUE > 90 < 120DAYSPASTDUE > 120' + 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 for xml path('tr'), type ) as nvarchar(max)) + ' ' select @query end ITG Outstanding Training Report Details Employee Name DAYSPASTDUE > 60 < 90 DAYSPASTDUE > 90 < 120 DAYSPASTDUE > 120 AAAAAAA AAAAAAA 0 0 2 ABABABAB B BAB 0 1 1 BSBSB BERYLER 3 6 21 YTQB SYLVESTER 0 0 1 John-Michael Trevino 0 1 2 AFT OILES 3 6 21 Kevin J Pounds 0 1 0 QYUTHIGA Sankaran 3 6 21 Radha Lakshminarayanan 0 2 1 Roberto Morais 1 1 0 < td> Venkata Namuduri 0 1 5 Sridevi Kadari 3 6 21 (1 rows affected) If we take a look at the output we can see the HTML Tag and is being displayed along with the names of < td>Venkata Namuduri and YTQB SYLVESTER. Due to this the report is not giving me 100% output . Can you please let me know what is the problem with my code. Thanks in advance Karthik Venkatraman
I don't think the problem is in the code. There is, of course, a problem with that initial in-line style where a bracket is missing, but that wouldn't cause the effect you see. I've done a test version that uses AdventureWorks just in case anyone else wants to investigate. declare @query nvarchar(max) set @query = ' Test DataEmployee NamePhoneEmail' + cast( (SELECT TOP 50 td=REPLACE(REPLACE(COALESCE(Title+' ','')+COALESCE(firstname+' ','') +COALESCE(' '+Middlename,''),' ',' '),' ,',',')+Lastname,'', td =Phone,'', td=EmailAddress FROM person.contact for xml path('tr'), type ) as nvarchar(max)) + ' ' SELECT @Query
Hi Phil, Still its not working well. itm getting one employee name outside the table of the report. The values for that employee are moved to the previous column from the original one. I have this question in the forum section too along with the screenshot of the output. can you please take a look at that and help me. The Forum topic is HTML Report using SQL
http://www.sqlservercentral.com/Forums/Topic1116727-391-1.aspx?Update=1 Can you help me again. Thanks, Karthik
