Running Total - Unable to sum formula 1

[manrique83]

I couldn’t find an answer to my question in any of the posts. So I ask for your help.

Crystal 9
Three tables:
Account_Balance
Account_Transaction
Account_Profile

All joined on account. I have 1 group (account) on my report and I display the following in the Group Sections., but I only display the balance if the count of withdrawals is greater than (>) 0.

field formula runningTotal

Account Balance Withdrawals
12345 55.00 2
44444 0
45638 100.00 9

Formula for Balance:
If {#Withdrawal} > 0 Then {account_balance}

RunningTotal – Withdrawals
Summary: Count of Transaction Account (# of times account had transaction)
Evaluate Formula: {tran_code} = “W”
Reset: On change of Group 1 (account)

Here is THE PROBLEM.
I’m unable to run a running total to sum the Balance because the Balance formula won’t show up as a field I can do a summary on.

Also, I tried a running total for account_balance and evaluate: {tran_code} = “W”. The problem with this is that it reads the first transaction record for that account, which gives me inaccurate totals. i.e. Using sample data above, say transaction records are:

tran_acct tran_code tran_amount
123456 W 20.00
123456 W 40.00
45638 D 45.00
45638 W 40.00

It won’t sum the balance for account 45638 because the first record is a “D” instead of “W”. Please help, I tried every possible solution.

 

[manrique83]

Can someone please help with this problem?

 

[lbass]

I don't think you need to use running totals. Create a formula like:

//{@withdrawcnt}:
if {table.tran_code} = "W" then 1

Insert a sum (NOT count) on this at the group level to get the count of withdrawals.

I am unclear about this: Is the {account_balance} that you use in your balance formula an actual field that appears the same in both detail and group levels or is it a summary? What is it?

-LB

 

[manrique83]

lbass, thanks for your reply. I don't have a problem summing the count of withdrawals. The problem I have is that I can't sum the balance.

I don't have anything appearing in my detail section. I have one group, account. The balance that appears in this group is a formula. The formula is used to display a balance only for accounts that have 1 or more withdrawals, fairly straightforward: f {#Withdrawal} > 0 Then {account_balance}

I can't, however, sum this field. I need to get a total balance for these accounts that had 1 or more withdrawal. Can you help?

 

[lbass]

The problem you are having is BECAUSE you are using a running total when you don't have to. Also, whether or not you are displaying detail fields, they do contribute to summary results.

Try this:

Create a running total of {table.account_balance}, which I guess is a database field (you didn't answer my question). In the evaluation section, choose "use a formula"->x+2 and enter:

(
onfirstrecord or
{table.tran_acct} <> previous({table.tran_acct})
) and
sum(@withdrawcnt},{table.tran_acct}) > 0

//where {@withdrawcnt} is the conditional formula in my first post.

Reset never.

-LB

 

[manrique83]

lbass,

You're awesome!!! It worked! I've delivered migraines over this issue.

Can you briefly explain the logic behind the code in the running total? I'm no expert in formulas.

(
onfirstrecord or
{table.tran_acct} <> previous({table.tran_acct})
) and
sum(@withdrawcnt},{table.tran_acct}) > 0

Thank you VERY much!!!

 

[lbass]

The first part (before the "and") says to evaluate on the first record per group. If the account number is the first one or is not the same as the previous one, then it is a new group. The last clause tests whether there is at least one record in the account group that is a withdrawal.

-LB