Solved

Crystal Reports Formula

Posted on 2013-01-30
15
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 2
  • 2
  • +2
15 Comments
 
LVL 23

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 23

Expert Comment

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

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Business Object Server 2 40
Run a batch file when a Crystal Report is opened 5 79
Possibly a grouping issue? 7 44
Crystal Reports - Logos 2 34
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

752 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