Solved

Crystal Reports Formula

Posted on 2013-01-30
15
295 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

20 Experts available now in Live!

Get 1:1 Help Now