WHERE??

[Sweetworks]

I want to create a Formula that does the following
I have Crystal 8.5 in a ORacle 8.1.7.4.1 Database
I only have 1 Table (ACCOUNT_BALANCE)
These are the fields
Account_ID
Debit
Credit
ACCT_Period

For Account_ID ='300011' I want to

(SUM(ACCOUNT_BALANCE.CREDIT) - SUM(ACCOUNT_BALANCE.DEBIT))

If I go to the Report > Edit Selection Formula > Record and put ACCOUNT_BALANCE.CREDIT = '300011'

I get my desired number.

The problem is I want to take results of this formula for these gl codes 300011 - 330002. How do I in the Formula select the 300011 Account

Basically something like this...

(SUM(ACCOUNT_BALANCE.CREDIT) - SUM(ACCOUNT_BALANCE.DEBIT)) where ACCOUNT_BALANCE.CREDIT = '300011'

 

[knitwit]

You can do a couple of things...
Go to report select expert and select account_id and
select gt 300010 and lt 330003.
Then insert a group into your report - group by account_id
Insert a formula into the detail that's account_balance>credit - account_balance.debit.
Pick the formula and insert summary for the group you inserted. Now when you run the report you will get the net total for each account number in account number order.
You can also suppress the detail section so that you are only showing the totals.

If I have misunderstood and you only want 300011, put it in the select expert. There's also a parameter that can be used which can pop up ask you what account do you want.
Melinda

 

[Sweetworks]

What I am trying to do is a simple report like this.

Gross Sales for GL 300011
Less Allowinace (acct 330002)

Less Discount (simple formula of 300011 x 1.2%)
Less Commish (simple formula of 300011 x 5%)
Net sales (sum of above)

Should be simple enough to do

 

[Naith]

At detail level:

If {ACCOUNT_BALANCE.CREDIT} = '300011'
Then {ACCOUNT_BALANCE.CREDIT} - {ACCOUNT_BALANCE.DEBIT}
Else 0

Then perform a sum on the result for totalling at various levels.

If ACCOUNT_BALANCE.CREDIT = '300011' in the selection criteria eliminates records that you in fact need, remove it.

Good luck,

Naith