By using this website, you agree to our Terms of Use (click here)
Hello, I'm trying to sum some values in Report Designer, and am not sure how to proceed. Any suggestions are appreciated.
I recently built a report that outputs the chart of accounts row by row, and the ending balance of each account to the right of each account number, in either the debit or credit column. The ending balance for each account is displayed as either a debit or credit, depending on the account type and amount +/-. I built in the logic for different account types to display on the other side if they are negative, so an asset account with positive balance is a debit balance, but a negative balance displays on the credit side, etc.
The trick that I need help thinking around is how to arrive at the sum of these amounts. I've looked at the sum formulas of other system-standard reports with no real flash of insight. Okay, with no flicker of insight -- let's be realistic.
For background information, to reach the debit or credit amounts that print on each row of this report for the given account, here's the formula I used on the debit side:
=SWITCH(
([Account.Type] = 'A' Or [Account.Type] = 'E') and [AHLast.FinYtdBalance] > 0,
[AHLast.FinYtdBalance],
([Account.Type] = 'L' Or [Account.Type] = 'I') and [AHLast.FinYtdBalance] < 0,
-[AHLast.FinYtdBalance],
)
The credit side formula just swaps the I and L for A and E, and vice versa.
If you have any suggestions for how I might add these columns I would appreciate it.
Thanks,
Daniel
Hi Daniel,
How is this different from the out-of-the-box Trial Balance Summary (GL632000) report?
Hi Tim,
The report I'm creating displays ending balances for each account. There are two columns for displaying the ending balances on this report, debit and credit. The values displayed in either of these columns is always a positive number.
The report Trial Balance Summary (GL632000) displays the ending balance for each account as well, but just in one column.
Because I have to formulate the ending balance on the basis of a positive debit or credit (depending on the account type), this adds a wrinkle for arriving at the total.
I attached the report file that I'm working with. Thanks for taking a look! I'm not sure if this is possible, but any insight is appreciated.
What we really need in Report Designer is the ability to reference fields in the calculations of other fields like you can do in Excel. But that functionality doesn't exist as far as I know.
I would try just wrapping your whole SWITCH statement with Sum to see if you get lucky. But you'll have to change every step of your SWITCH statement to return numbers. Currently you are returning text in some of the steps.