Solved

Crystal Reports Formula

Posted on 2013-01-30
15
298 Views
Last Modified: 2013-02-02
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
Comment
Question by:Mark01
  • 8
  • 2
  • 2
  • +2
15 Comments
 
LVL 22

Expert Comment

by:Ido Millet
ID: 38835815
Could you explain what the problem is?
0
 

Author Comment

by:Mark01
ID: 38836001
I need help writing the formula.
0
 
LVL 22

Expert Comment

by:Ido Millet
ID: 38836024
What is the problem you are having with writing that formula?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 

Author Comment

by:Mark01
ID: 38836079
I don't know how to write the formula. Please help.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38836354
"...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 Comment

by:Mark01
ID: 38837062
Hi Peter,

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

Accepted Solution

by:
mlmcc earned 500 total points
ID: 38837898
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 Comment

by:Mark01
ID: 38840506
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 38842267
You missed the first step in my answer

In the report group on the Account number

mlmcc
0
 

Author Comment

by:Mark01
ID: 38844170
I did group on the Account number. I created a formula with the Account number field and I grouped on the formula.
0
 

Author Comment

by:Mark01
ID: 38844266
I changed the group to group on the Account number and the error went away.
0
 

Author Comment

by:Mark01
ID: 38844974
The report functions properly. Thanks, mlmcc.
0
 
LVL 35

Expert Comment

by:James0628
ID: 38846559
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 Comment

by:Mark01
ID: 38846841
Thanks, James.
0
 
LVL 35

Expert Comment

by:James0628
ID: 38846902
No problem.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question