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.
| Author |
Topic |
|
sue1127
Starting Member
23 Posts |
 Posted - 2009-03-20 : 11:29:16
|
| I need to group and sum the amounts for some accounts. I need to group and sum by pcode(a section of the account number) and by the entire account number. Each account can have a beginning balance and transaction details.This is an example: Date Transaction # Journal Journal Reference Balance 1111 4-999-1111-2345 Account Beginning Balance ($1,425,412.00) 7/1/2008 16666-3 General Journal Close Financial Aid Balances $1,425,412.00 Account Ending Balance $0.00 4-999-1111-6789 Account Beginning Balance $1,428,893.00 7/1/2008 17777-4 General Journal Close Financial Aid Balances ($1,425,412.00) Account Ending Balance $3,481.00 1111 Pcode Total ($1,425,412.00) The pcode in the above example is 1111. I have 2 groups: Group 1 is “table1_group1_pcode” to sum by pcode. The formula is =sum(Fields!AcctTran_1_Amount.Value)+ Fields!calc_beginBal.ValueThis is the sum of the transactions and the beginning balance per pcodeGroup 2 is “group2_account” to sum by account. The formula is=sum(Fields!AcctTran_1_Amount.Value)+ Fields!calc_beginBal.ValueThis is the sum of the transactions and the beginning balance per accountThere are a few different scenarios that can occur, such as no transaction details, one or more transaction details, no beginning balance, a beginning balance. As long as an account has at least a beginning balance or a transaction detail, it will show in the report.Everything works except in certain cases such as the one shown above, where the pcode total should be 3,481.00, but is showing as (1,425,412.00).Does anyone know why this scenario doesn’t work, and how I could fix it without harming all the scenarios where the formulas do work.Thanks very much.Sue |
|
|
|
|
|
|
|
Incorrect account totals in RS 2005