Creating Excel Report From Dynamic Table

#1

I have a table with the following structure:

Code:

CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20)  NULL ,
[dHours] numeric (10,4) NULL)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

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

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200604/1

#2

hi,
Why not?
We've got several dts scheduled by Sql Server Agent on night-to-night basis.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)

"ngorbunov via SQLMonster.com" wrote:

Quote:

I have a table with the following structure:

Code:

CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20)  NULL ,
[dHours] numeric (10,4) NULL)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

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

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200604/1

#3

I'm sorry, I don't understand.
Did you answer my question?

Enric wrote:

Quote:

hi,
Why not?
We've got several dts scheduled by Sql Server Agent on night-to-night basis.
I have a table with the following structure:

[quoted text clipped - 48 lines]
Thanks,
Ninel

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200604/1

#4

hi again ngorbunov,
No exactly, I was thinking aloud. Why you can't do this by dts, day in day
out?

Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)

"ngorbunov via SQLMonster.com" wrote:

Quote:

I'm sorry, I don't understand.
Did you answer my question?

Enric wrote:
hi,
Why not?
We've got several dts scheduled by Sql Server Agent on night-to-night basis.
I have a table with the following structure:

[quoted text clipped - 48 lines]
Thanks,
Ninel

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-dts/200604/1

#5

I created a crosstab table to get data that I need. Looks like this:

Calldate ProjectA ProjectB ProjectC
20060401 5 2 0
20060402 4 5 1

I was trying to create a DTS package to do this, but because the table
structure changes daily (Today I may have up to project C, but tomorrow I may
have project D and projectE as well )

How can I create an excel transformation if the table structure changes?

Enric wrote:

Quote:

hi again ngorbunov,
No exactly, I was thinking aloud. Why you can't do this by dts, day in day
out?

Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)

I'm sorry, I don't understand.
Did you answer my question?
[quoted text clipped - 7 lines]
Thanks,
Ninel

--
Message posted via http://www.sqlmonster.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Creating Excel Report From Dynamic Table

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Creating Excel Report From Dynamic Table - 04-05-2006 , 10:29 AM

RE: Creating Excel Report From Dynamic Table - 04-05-2006 , 10:42 AM

RE: Creating Excel Report From Dynamic Table - 04-05-2006 , 10:54 AM

RE: Creating Excel Report From Dynamic Table - 04-05-2006 , 11:01 AM

RE: Creating Excel Report From Dynamic Table - 04-05-2006 , 11:10 AM