excel destination and sum

  • 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.

  • 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

  • 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

  • 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.

    😎

  • 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

  • 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

  • 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)

  • 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

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

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