[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 309

# 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)

0
Mark01
• 8
• 2
• 2
• +2
1 Solution

Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Could you explain what the problem is?
0

Author Commented:
I need help writing the formula.
0

Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
What is the problem you are having with writing that formula?
0

Author Commented:
0

Commented:
"...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?
0

Author Commented:
Hi Peter,

Specified by the user at run time using parameter fields.
0

Commented:
I assume there are other fields so you can JOIN the tables appropriately.

You can use the date range  entered by the user to limit the data selected for the report to records here the receipt date is in the date range.

In the report group on the Account number
You can get the total of the receipts with

Sum({BrokgRecpts.ReceiptAmt},{BrokerageAccounts.Account})

In the group header or footer add that to the beginning balance

mlmcc
0

Author Commented:
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.
0

Commented:
You missed the first step in my answer

In the report group on the Account number

mlmcc
0

Author Commented:
I did group on the Account number. I created a formula with the Account number field and I grouped on the formula.
0

Author Commented:
I changed the group to group on the Account number and the error went away.
0

Author Commented:
The report functions properly. Thanks, mlmcc.
0

Commented:
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
0

Author Commented:
Thanks, James.
0

Commented:
No problem.
0

## Featured Post

• 8
• 2
• 2
• +2
Tackle projects and never again get stuck behind a technical roadblock.