2 Replies Latest reply on Nov 20, 2012 10:54 AM by jimscott77

    Reporting Summary Fields



      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

        • 1. Re: Reporting Summary Fields

               You cannot use aggregate functions such as Max, Sum or Min to get an aggregate value from a single field. It's like saying what is the maximum of 5? The correct answer is 5, but that's not what you want here. These functions require a list of fields either from a set of related records or that are specifically listed as parameters in the function such as Max ( Field1 ; field 2 ; field 3 ) and then these are taken only from the current record. Max ( RelatedTable::Field ) on the other hand returns the largest value in Field from the set of related records.

               You'd need a summary field to get a value for either the entire found set or a sorted group of records in your table. Or you need a relationship in order to get a value either for all records in the table or for a related group of records. This second option can work with either aggregate functions or summary fields.

          • 2. Re: Reporting Summary Fields

                 Thanks for the comment and for being so responsive!

                 The MINs and MAXs are generated across multiple records using executeSQL() and GROUPing on the shown summarizing fields.

                 I installed SQL Runner (add in) to try and solve the problem.  It is able to CREATE, DELETE, INSERT, UPDATE tables/fields in pretty standard SQL syntax.  However when I ran an INSERT query using aggregate SELECT functions the results were anomalous; the SELECTed values for column 1 showed up in columns 1&2, the SELECTed values for column 2 showed up in columns 3&4, etc.  I submitted my results to DRAConventions (11/19/2012) and suspect they will resolve this problem shortly.

                 Since then I have gone back to the external SQL database and created the VIEWS I need there and then attached them to FM.  This approach also has the advantage of offloading some of the data processing to the server.  Though I am relatively new to FM I think the reporting I need to do will be straight forward from here.

                 NOTE: Since I am more familiar with SQL than with FM I do have a tendency to look at a problem from an SQL perspective first.  Your feedback, however, is helping me bridge some of that knowledge gap.

                 Thanks again,

                 Jim S