DTS ActiveX script to generate sum in excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS ActiveX script to generate sum in excel

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2006-11-18 : 15:26:35

I have a scenario where i'm exporting data from sql server to excel sheet using views. I need to have an ActiveX script that would generate a sum at the end of each column in excel sheet right at the end of last row in each column. Thanks for ur help.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2006-11-21 : 09:33:37

Not a really elegant solution, but create another view with all the sums and export that after you load the data to the excel spreadsheets, you would have 2 transformation tasks on your dts, one with the data, the other with the sum and the sum being executed after the previous one.

---
"There's no sexy way to carry a lunch bag." My boss

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-11-23 : 06:27:26

You could look at using COMPUTE. The only problem would be that it generates multiple resultsets. Alternatively, you could use a UNION to include the totals as part of the same resultset. If you did this within your views, I'd suggest adding a static value as an additional column to identify the Totals row, thereby allowing you to exclude it if desired. I.e.


SELECT  mt.Col1  AS Col1
       ,mt.Col2  AS Col2
--      etc
       ,'Detail' AS RowType
FROM    dbo.MyTable AS mt
UNION ALL
SELECT  SUM(mt.Col1)
       ,SUM(mt.Col2)
--      etc
       ,'Total'
FROM    dbo.MyTable AS mt

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources