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

    Reporting Summary Fields

    jimscott77

      Title

      Reporting Summary Fields

      Post

           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
          philmodjunk

               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
            jimscott77

                 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