sql server dts: Creating Excel Report From Dynamic Table -- sql server dts

RE: Creating Excel Report From Dynamic Table

Enric
4/5/2006 8:42:01 AM

sql server dts:

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)

[quoted text, click to view]

RE: Creating Excel Report From Dynamic Table

Enric
4/5/2006 9:01:02 AM

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)

[quoted text, click to view]

Creating Excel Report From Dynamic Table

ngorbunov via SQLMonster.com
4/5/2006 3:29:02 PM

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)
[/code]

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

RE: Creating Excel Report From Dynamic Table

ngorbunov via SQLMonster.com
4/5/2006 3:54:05 PM

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

[quoted text, click to view]

--
Message posted via SQLMonster.com

RE: Creating Excel Report From Dynamic Table

ngorbunov via SQLMonster.com
4/5/2006 4:10:58 PM

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?

[quoted text, click to view]

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.