Link to home
Start Free TrialLog in
Avatar of Mark01
Mark01Flag for United States of America

asked on

Crystal Reports Formula

I'm working on a formula to prepare a financial statement that is very similar to a brokerage statement. The brokerage receipts for a specified period of time will be added to the beginning account balance for the same time period to come up with a subtotal. Any assistance would be greatly appreciated.

Here’s a sample layout:
For Account #1
Beginning balance, 1-1-2011, $1,000
Dividend income $1,000
Ending balance, 1-31-2011, $2,000

Tables used:

BrkgkAcctBalDates
Date (field)

BrokerageAccounts
Account (field)

BrokgRecpts
ReceiptDate (field)
ReceiptAmt (field)

BrkgDate_BrkgAcct_Link
brLinkBalance (field)
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

Could you explain what the problem is?
Avatar of Mark01

ASKER

I need help writing the formula.
What is the problem you are having with writing that formula?
Avatar of Mark01

ASKER

I don't know how to write the formula. Please help.
"...for a specified period of time ..."

Specified how? By the user at run time using parameter fields or is it a fixed period based on the current date or what?
Avatar of Mark01

ASKER

Hi Peter,

Specified by the user at run time using parameter fields.
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark01

ASKER

Hi mlmcc,

Yes. There are other fields so the tables can JOIN appropriately. I get the following error when I create the "Sum({BrokgRecpts.ReceiptAmt},{BrokerageAccounts.Account})" formula: There must be a group that matches this field.

I'm using an Access 2003 database.
Avatar of Mike McCracken
Mike McCracken

You missed the first step in my answer

In the report group on the Account number

mlmcc
Avatar of Mark01

ASKER

I did group on the Account number. I created a formula with the Account number field and I grouped on the formula.
Avatar of Mark01

ASKER

I changed the group to group on the Account number and the error went away.
Avatar of Mark01

ASKER

The report functions properly. Thanks, mlmcc.
FWIW, you could group on a formula if you need to.  You would just need to use that formula with the Sum function, instead of the field.  Something like:

Sum({BrokgRecpts.ReceiptAmt},{@account group formula})

 You have to use whatever field or formula the group was created on in the summary function.

 James
Avatar of Mark01

ASKER

Thanks, James.
No problem.