Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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)
0
Mark01
Asked:
Mark01
  • 8
  • 2
  • 2
  • +2
1 Solution
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Could you explain what the problem is?
0
 
Mark01Author Commented:
I need help writing the formula.
0
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
What is the problem you are having with writing that formula?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark01Author Commented:
I don't know how to write the formula. Please help.
0
 
peter57rCommented:
"...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
 
Mark01Author Commented:
Hi Peter,

Specified by the user at run time using parameter fields.
0
 
mlmccCommented:
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
 
Mark01Author 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
 
mlmccCommented:
You missed the first step in my answer

In the report group on the Account number

mlmcc
0
 
Mark01Author Commented:
I did group on the Account number. I created a formula with the Account number field and I grouped on the formula.
0
 
Mark01Author Commented:
I changed the group to group on the Account number and the error went away.
0
 
Mark01Author Commented:
The report functions properly. Thanks, mlmcc.
0
 
James0628Commented:
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
 
Mark01Author Commented:
Thanks, James.
0
 
James0628Commented:
No problem.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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