Solved

Crystal Reports Formula

Posted on 2013-02-04
10
462 Views
Last Modified: 2013-02-06
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. I'm using an Access 2003 database. The specified time period must be coded into the formula. Any assistance would be greatly appreciated.

Here are more details about the current version of the report.

Groups:
Brokerage Account (grouped on the Account number)
Brokerage Receipt Description (grouped on the brkReceiptDescr field)

Formulas

Receipts Sum 1:
Sum({tblBrokgRecpts.brkgReceiptAmt},{tblBrokerageAccounts.AccountNumber})


Receipts And Beg Bal:
{@Receipts Sum 1} + {@Beginning Balance}

Beginning Balance:
{tblBrkgAcctDate_BrkgAcct_Link.brkbdBalnce}


The babdDate field from the tblBrkgkAcctBalDates table has to used in the formula. For example, in the sample layout below, the date, 1-1-2011, is in the tblBrkgkAcctBalDates table. The beginning balance of $1,000 is in the tblBrkgAcctDate_BrkgAcct_Link table.


Here’s a sample layout:
Account #1
Beginning balance, 1-1-2011, $1,000
Dividend income - 1-3-11 $500
Dividend income - 1-7-11 $500
Total Dividend income $1,000
Ending balance, 1-31-2011, $2,000


Tables

There are other fields so the tables can JOIN appropriately.

tblBrkgkAcctBalDates
babdDate :DateTime (field)



tblBrokerageAccounts
AccountNumber :Text (field)


tblBrokgRecpts
brkgReceiptDate :DateTime (field)
brkgReceiptAmt :Currency = 0 (field)


tblBrokgRecptDescr
brkReceiptDescr :Text (field)

NOTE: Examples of data in brkReceiptDescr are "Dividend Income" and "Interest Income."

tblBrkgAcctDate_BrkgAcct_Link
BrokerageAccountID(Long) (field)
BrkgAcctBalDateID(Long) (field)
brkbdBalnce :Currency (field)
0
Comment
Question by:Mark01
  • 4
  • 3
  • 3
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 38853468
What is your issue?

mlmcc
0
 

Author Comment

by:Mark01
ID: 38853592
Field and table namesHi mlmcc,

I don't know how to write the formula(s). This image may help.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38853624
Do you get 1 record per account or several?

If it is several you probably don't need formulas just put the fields in the detail section where you want them to appear.

You probably need to group on Account Number to ensure all records for an account stay together

mlmcc
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 34

Expert Comment

by:James0628
ID: 38854402
Looking at the formulas in your first post:

 The CR summary functions, like Sum, always return the summary for the entire group/report, not for individual records or anything like that.  So, Sum ({tblBrokgRecpts.brkgReceiptAmt},{tblBrokerageAccounts.AccountNumber}) will always give you the total brkgReceiptAmt for the current AccountNumber.  If you put that in the detail section, you'll get the same figure on every line.

 {@Receipts And Beg Bal} should give you the total for the records in the current account, plus the beginning balance, so that should give you the ending balance.  You would probably want that in the group footer, or header.

 If you want a running total with the total brkgReceiptAmt up to the current record, plus the beginning balance, I'd probably create a running total for brkgReceiptAmt (reset on change of AccountNumber group), and then create a formula like {@Receipts And Beg Bal} that added the running total and brkbdBalnce together.


 FWIW, if {@Beginning Balance} is just the brkbdBalnce field, you don't need to use a formula for that.  I would just use the field.  But if you'd rather put it in a formula, that's fine.

 James
0
 

Author Comment

by:Mark01
ID: 38856231
CR table linksCurrent Reportmlmcc and James0628:

Both of you are way ahead of me. The attached "Current Report" image shows the basic problem I'm having which is that I don't know how to code in the date of the beginning balance field in a formula. A balance appears on the current version of the report, but I don't know what date it corresponds to. The second image ("CR table links") shows the report designer table links. Both images are in jpg and pdf for your convenience.
All-Pdf.pdf
0
 
LVL 34

Expert Comment

by:James0628
ID: 38856699
Well, of course, that depends on your data.  If you've got a beginning balance field, there is presumably some kind of date or fiscal period or something that goes with it, so it's the beg balance as of that date/period.

 If you're running the report with a starting date, will your data include a beg balance for that date, or do you need to calculate that beg balance?  If you need to calculate the beginning balance, that will probably mean reading some "extra" data somewhere (the transactions between the beg balance that's on file and your actual starting date).

 Simplest case - You will have a beginning balance for whatever starting date you choose (eg. the first of a month), so add a test to the record selection so that the report only includes the beg balances for the correct month.  Have the record selection only include the transactions for the desired time period, starting on the first of that month.  Add those transactions to that beg balance.

 James
0
 

Author Comment

by:Mark01
ID: 38857475
Desired VersionCurrent VersionThe beginning balance and corresponding date functions properly. The only problem I have is totaling the various types of brokerage receipts for each month. Is Running Total the best way to deal with this? If the answer is yes, how do I write the formula? The attached image shows the type of totals that I need.
All-Pdf.pdf
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 38858032
There is no "BEST" way to calculate totals.  It all depends on the data.

If you just need the totals and are getting only 1 record for each receipt for a given date then you can group the report on the receipt type and use the summary functions to get the total for that group

If you are getting duplicate records, running totals or manual totaling through formulas are the easiest way to go.  

Can you upload the rpt file?

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 38858788
As mlmcc said, it depends on your data, and your report structure.

 If you want each line to show the total so far for the group/report (on the first line you get the amount from that line, on the second line you get the total for the first two lines, and so on), that's what a running total is.  OTOH, a regular summary will always give you the total for the entire group/report.  CR running totals also give you more control over which records they include.

 In your "desired version" examples, you show the first account twice, at the beginning and again at the end.  Do you actually want accounts to appear twice on the report?  If so, that complicates things.  Exactly where do you want the accounts to be repeated (group footer or ...)?

 James
0
 

Author Comment

by:Mark01
ID: 38862158
I solved the problem by creating a new Brokerage Receipt Date group (grouped on the brkgReceiptDate).

I do not want any account to appear twice on the report. In the "desired version" examples, I showed the first account twice, at the beginning and again at the end to show both the sum and running total for the brokerage account income. You can disregard the example.

Thank you, James0628 and mlmcc.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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. …
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

10 Experts available now in Live!

Get 1:1 Help Now