Not exactly sure if this is a technical, DB link, or report issue... 

I'm new to Crystal Reports.  I'll try to summarize the issues the best that I can.  Here are some facts:

Version: Crystal Reports 2008
DB Connection: SQL Server - ODBC
Table 1: Account Data
Table 2: Payment Data
Table 3: Charge Data

Table 1 is linked with "Left Outer Joins" to table 2 and 3 by account number.

Sample Data Fields:
Table 1 (Account)
Acct#   Acct_Bal   Portfolio
1001    $1,000         A
1002    $1,500         A
1003    $1,200         B

Table 2  (Payments)
Acct#  Pay_Amnt   Pay_Dt
1002      $5           01/05/09
1002      $10         02/01/09
1003      $5           01/01/09
1003      $10         02/01/09
1003      $1           03/05/09

Table 3  (Charges)
Acct#  Charge_Amnt   Charge_Dt
1001        $10             01/01/09
1001        $15             02/01/09
1001        $5               03/04/09
1002        $10             02/07/09
1003        $10             02/01/09
1003        $5               04/01/09
1003        $7               04/05/09

The report would show something like:
Acct#   Acct_Bal   Portfolio   Pay_Amnt   Pay_Dt   Charge_Amnt   Charge_Dt
1001    $1,000          A              $0                0             $10             01/01/09
1001    $1,000          A              $0                0             $15             02/01/09
1001    $1,000          A              $0                0             $5               03/04/09
1002    $1,500          A              $5          01/05/09      $10             02/07/09
1002    $1,500          A              $10        02/01/09      $10             02/07/09
1003    $1,200          B              $5          01/01/09      $10             02/01/09
1003    $1,200          B              $10        02/01/09      $5               04/01/09
1003    $1,200          B              $1          03/05/09      $7               04/05/09


If I were to group this data by portfolio I would get:
Portfolio   Distinct_Acct_#s     Ttl_Bal    Ttl_Paymnts    Ttl_Charges
  A                     2                   $6,000           $15                $50
  B                     1                   $3,600           $16                $12
*items in bold are returning incorrect data

Example Formulas would return:
if {account.portfolio} = "A"
and {charge.chargdate} in "02/01/09" to "02/28/09
then {charge.chargeamnt}

If I then summed this formula and placed it in the report footer it would return $35 instead of the correct amount of $25.



Conclusion:
I understand how/why it creates multiple records per account on the report level, and why it is repeating data, but I can not find an obvious way around this, if there is one.  I am unsure whether the problems I am having are due to the way I am connecting our data, or the way I am trying to summarize the data.  I wish to group the data and also create similar summary formulas similar to the examples I gave above.  Any insight or recommendation would be hugely appreciated.

I tried option 1 by creating some summary tables in access of the payments and charges tables but this limits me from using some of the data fields.  My actual table has 4 or 5 different status/transactional fields that tell me more about the payment/charge and I just don't see how I could summarize it... yet.

After posting I did come across a few sub-report topics that I am going to try, which sound like they will give me similar data to the summary tables, but they will be live(?). I also think variable formulas might do the trick as well...  I was just hoping there was some obvious way around this that I overlooked that would solve the duplicate problem for me , but as usual with programming, you can't expect it to just know what you want.


Edited by jonculp - 29 Oct 2009 at 12:39pm