AnsweredAssumed Answered

Reporting Summary Fields

Question asked by jimscott77 on Nov 17, 2012
Latest reply on Nov 20, 2012 by jimscott77


Reporting Summary Fields


     We are developing reports from an external SQL database for which we do not have writing priviledges.  One of the tables, PAYMENTS, is a list of transactions with the following significant fields.

     ClientID, AccountNumber, TransactionDate, BeginningBalance, PaymentAmount, EndingBalance

     For a given reporting period (monthly) we want to summarize the transactions shown in the table.

     Summarize by year(TransactionDate)
     Summarize by month(TransactionDate)
     Summarize by ClientID
     Summarize by AccountNumber

     Max(BeginningBalance) : shows balance before the first transaction
     Sum(PaymentAmount): shows the total of all payments made
     Min(EndingBalance): shows the balance after the final transaction

     The report has no BODY
     The AccountNumber and other fields are shown in the AccountNumber summary section

     Report subtotals are the problem:

     We cannot summarize the BeginningBalance nor the EndingBalance.
     We need to summarize the Maxes of the BeginningBalances and the Minimums of the EndingBalances

     Thanks for your help

     Jim S