Solved

Crystal Reports Formula

Posted on 2013-01-30
15
294 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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 34

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 34

Expert Comment

by:James0628
ID: 38846902
No problem.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now