excel destination and sum

Question

Post reply

excel destination and sum

coool_sweet

SSCommitted

Points: 1892
More actions
April 1, 2015 at 1:49 pm

#316771

i am creating excel destination which has 6 column,out of 6 column 4 r amount.
i can load data into excel
but after loading want sum at the end of each column to come automatically.
how to do it using ssis.
i have to create different file each time so i cannot create template also.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

The easiest option would be to calculate the totals in the SQL script at the source.

SSIS cannot put formulas in an Excel sheet.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

coool_sweet SSCommitted Points: 1892 More actions · Answer 2

hi

even if i calculate in my sql script it will come as a column

i need last row only sum in my excel sheet

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

coool_sweet (4/4/2015)
hi
even if i calculate in my sql script it will come as a column
i need last row only sum in my excel sheet

Quick suggestion, use a template Excel spreadsheet with a macro which adds the summary line when it is opened.

😎

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

coool_sweet (4/4/2015)
hi
even if i calculate in my sql script it will come as a column
i need last row only sum in my excel sheet

Can you give some sample data and desired output?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

coool_sweet SSCommitted Points: 1892 More actions · Answer 5

sample data:

date amount1 amount2 amount3

01/01/2012 567.32 345.54 234.43

01/02/2012 234.54 345.21 456.76

01/02/2011 02.32 23.45 34.56

total xxxx xxxx xxxx

here everything is coming from stored proc and loading into excel.

except total row,i need when i load data it should automatically calculate total row also

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

This post is nearly two weeks old and, up to this instant, you don't have an answer even though it's for an incredibly simple problem. Before you post again, take a look at the article at the first link in my signature line below under "Helpful Links" to find out why that might be. 😉

On to business...

Here's the test data that you should have posted...

--===== Create a test table so we can check results.

-- This is NOT a part of the solution

SELECT Date = CONVERT(DATETIME,Date),Amount1,Amount2,Amount3

INTO dbo.YourTable

FROM (

SELECT '01/01/2012', 567.32, 345.54, 234.43 UNION ALL

SELECT '01/02/2012', 234.54, 345.21, 456.76 UNION ALL

SELECT '01/02/2011', 02.32, 23.45, 34.56

)d(Date,Amount1,Amount2,Amount3)

;

...and here's a T-SQL solution. I don't even know how to spell "SSIS" so I'll leave that bit of integration up to you.

--===== Solve the problem.

SELECT Date = CASE WHEN GROUPING(yt.Date)=0 THEN CONVERT(CHAR(10),yt.Date,101) ELSE 'Total' END

,Amount1 = SUM(yt.Amount1)

,Amount2 = SUM(yt.Amount2)

,Amount3 = SUM(yt.Amount3)

FROM dbo.YourTable yt

GROUP BY Date WITH ROLLUP

ORDER BY GROUPING(Date),yt.Date

;

Results:

Date Amount1 Amount2 Amount3

---------- ------- ------- -------

01/02/2011 2.32 23.45 34.56

01/01/2012 567.32 345.54 234.43

01/02/2012 234.54 345.21 456.76

Total 804.18 714.20 725.75

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

coool_sweet SSCommitted Points: 1892 More actions · Answer 7

thanks

but i cannot group by date ,most of the time i have same date or max 3 different dates.

will it give me same output