views:

74

answers:

0

I am trying to work up a general ledger appearance in an asp.net page. Here's the data i have so far.

AccountName   Desc     Type      Amount
-----------------------------------------
Account1      Stuff    Debit     5000
Account1      Stuff    Credit    4000
Account2      Other    Debit     3000
Account2      Other    Credit    6000
Account3      Other    Debit     7000
Account3      Other    Credit    2000
Account4      Stuff    Debit     4000
Account4      Stuff    Credit    3000
Account5      Other    Debit     4000
Account5      Other    Credit    8000

I could also query it as this

AccountName  Desc   Debit   Credit
-------------------------------------
Account1     Stuff  5000    4000
Account2     Other  3000    6000
Account3     Other  7000    2000
Account4     Stuff  4000    3000
Account5     Other  4000    8000

What i want is to display it in an aspx page (preferrably in a datagrid so i can export to excel) looking like this

AccountName     Stuff          Other
            Debit  Credit   Debit  Credit
---------------------------------------------
Account1    5000   4000
Account2                    3000    6000
Account3                    7000    2000
Account4    4000   3000    
Account5                    4000    8000
---------------------------------------------
            9000   7000     14000   16000
               2000            -2000

Is this even doable in a datagrid? I was thinking Pivot in TSQL if possible, but can I pivot with subheadings? How would i bind to it? Any suggestions on how to do it in C# if i have to (i can output to a html table if i had to)? I could export to excel by creating an openxml document using Microsoft's API.

Thanks, Rob