Mark01
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)
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)
Could you explain what the problem is?
ASKER
I need help writing the formula.
What is the problem you are having with writing that formula?
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?
Specified how? By the user at run time using parameter fields or is it a fixed period based on the current date or what?
ASKER
Hi Peter,
Specified by the user at run time using parameter fields.
Specified by the user at run time using parameter fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.ReceiptA mt},{Broke rageAccoun ts.Account })" formula: There must be a group that matches this field.
I'm using an Access 2003 database.
Yes. There are other fields so the tables can JOIN appropriately. I get the following error when I create the "Sum({BrokgRecpts.ReceiptA
I'm using an Access 2003 database.
You missed the first step in my answer
In the report group on the Account number
mlmcc
In the report group on the Account number
mlmcc
ASKER
I did group on the Account number. I created a formula with the Account number field and I grouped on the formula.
ASKER
I changed the group to group on the Account number and the error went away.
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.ReceiptAm t},{@accou nt group formula})
You have to use whatever field or formula the group was created on in the summary function.
James
Sum({BrokgRecpts.ReceiptAm
You have to use whatever field or formula the group was created on in the summary function.
James
ASKER
Thanks, James.
No problem.