Creating Excel Report From Dynamic Table

Last Post 20 Apr 2006 12:07 AM by Ado_SQL. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ninelg
New Member
New Member

--
05 Apr 2006 07:27 AM
I have a table with the following structure:


<br>
<br>CREATE TABLE [#TEST] ( 
<br> [sCalldate] [varchar]((20) NULL , 
<br> [sProject] [varchar] (20)  NULL , 
<br> [dHours] numeric (10,4) NULL)  
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060401', 'A', 12) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060401', 'B', 0) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060401', 'C', 2) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060402', 'A', 2) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060402', 'B', 5) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060402', 'C', 4) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060403', 'A', 3) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060403', 'B', 5) 
<br>
<br>INSERT [#TEST] (sCalldate,sProject, dHours) 
<br>VALUES ('20060403', 'C', 1) 
<br>
<br>


I need to create an excel report that contains the calldate down the first column and the projects as cloumn headings across the top with the amount of hours worked each projects per day.

I created a cross tab table with project codes across the top as columns. One major issue here is that project codes are created daily. One day I can have 3 next day I can have 5.

So I have the table and the data populated the way I need, but I can't create the excel spreadsheet in a dts package (this report needs to be run on a nightly basis).

Can anyone help me? Is there any way of accomplishing this?

Thanks,
Ninel

Ado_SQL
New Member
New Member

--
20 Apr 2006 12:07 AM
I have a similar setup which produces and mails a spreadsheet of hours worked per employee. My solution was as follows;

Create an excel workbook on the server called <<workbook.xls>> with a worksheet called TimeSht.

Within the DTS package create a connection to the workbook and a connection to the SQL server, the first task is to drop the worksheet as 'drop table TimeSht' using the <<workbook>> connection. Next I create a temp table of all required data, before using an insert statement using the <<workbook>> connection as:

insert into TimeSht
<<column names>>
select

before finally dropping the temp table.

It may be a cludge and there may be better ways, but it is scheduled to run weekly at midnight and I have not had any problems; other than people not completing their electronic timesheet!!

HTH
You are not authorized to post a reply.

Acceptable Use Policy