Summarizing off setting amount if account number is found.

[republiccommando]

Hello,

I am trying to create a subreport that will give me outstanding balances for the year by dept.
My difficulty is summarizing the gl data based on the sample data below. Instead of me babbling the details, I know sample data works better, then questions if any comes later. Any assistance is greatly appreciated. Using CR2008. Thanks.

Paid To Date Month: August, 2009
Filter for Accounts 1020, 1030, 1090

Data:

TABLE1 - Transaction Detail
GROUP LINE DEPT ACCOUNT AMOUNT

1 1 6080 5040 100.00
1 2 9999 1020 -100.00

2 1 6070 5040 100.00
2 2 9998 1030 -250.00
2 3 6080 5050 150.00

3 1 9999 1080 -100.00
3 2 6080 5040 100.00

4 1 6060 5020 100.00
4 3 6060 5030 100.00
4 5 6080 5040 100.00
4 6 9997 1090 -300.00


TABLE2 - Transaction Schedule
GROUP PRDBEG YRBEG PRDEND YREND

1 3 2008 11 2009
2 1 2008 12 2010
3 7 2008 6 2010
4 1 2006 10 2010


Desired Result for 2009 Outstanding Balance:

GROUP DEPT ACCOUNT AMOUNT

1 6080 5040 300.00

2 6070 5040 400.00
2 6080 5050 600.00

4 6060 5020 400.00
4 6060 5030 400.00
4 6080 5040 400.00

 

[lbass]

I can't follow this at all. The Transaction Schedule doesn't appear to be relevant. I do not know how the 999N values fit in and I cannot discern how you are calculating the amount in your final display. So you will have to provide a little more narrative detail.

-LB

 

[republiccommando]

Hi lbass. Thanks and here's more information. The transaction detail table is linked to the transaction schedule table via the group field. The 2 transactions for group 1, acct 5040: $100 and acct 1020: $-100, will be expensed every month beginning March 2008 through November 2009 to their respective accounts.

The requirement is to determine outstanding debit account charges from the just ended month, August 2009, to the end of the calendar year or end of term, for certain credit accounts (1020,1030, 1090 is an example, there are about 16 credit accounts that could be inquired via a parameter.)

So for credit account 1020 in group 1, the remaining debits for account 5040, would be Sep, Oct and Nov (end of term) for $100 each giving an outstanding charge of $300. Next year would reset for 2010.

Hope this helps understand my requirements a bit more. Thanks.