3 Replies Latest reply on Mar 14, 2013 6:54 AM by mvreade

    Summarizing fields on a report

    mvreade

      Hi,

       

      I'm having a hard time getting summary reports running on the child table to total correctly.

       

      I'm trying to write a report which is "Total Expenses by Customer, Detail".

       

      3 Tables to think about: Customers, Expenses, Items.

       

      The report runs from an Items layout, because I want to show all the items.

       

      Relevant fields are:

      Expenses::Total_cn = Sum (Expenses_Items::Cost_n)

      Expenses::Total_sum = Summary fields totalling Total_cn

       

      I have a summary fields sorted by Customer::CompanyName. If I use the Total_sum in this subsummary, it doesn't add up correctly.

       

      I can make it work by by creating a self relationship on the Expenses table based on the customer_id.

       

      But I'm wondering if there is a simpler way to do this.

       

      And no matter what I try, I can't get the Trailing Grand Summary to add up all the totals for different customers.

       

      Thank you,

      Michael Reade

       

      I can, of course, get the summaries and grand total to work if I run the report from the Expenses table. But I also need a detailed report.

        • 1. Re: Summarizing fields on a report
          LyndsayHowarth

          You are trying to do this as a circular reference via relationship rather than use the natural abilities of the base table.

           

          You have Items as the body.

          You then have either leading and/or trailing summary parts for each ot the following:

           

          Year Number

          Month Number

          CustomerName (id?)

          Expense (id?)

           

          ... and on each part you place the summary of cost field... yes the same field on every one... and in the trailing grand summary.

           

          WHEN you sort it in the same order it will break down the costs for each of your summary parts. You do not have to refer back to any table for anything other than to place a field for display/print purposes.

           

          HTH

           

          - Lyndsay

          • 2. Re: Summarizing fields on a report
            mvreade

            Thanks for your reply Lyndsay.  I provided a somewhat simple problem, hoping that I wouldn't need to dwelve into greater complexity, but I think we need to do it.  Sorry for not being more clear.  We also have a Payments table, and a PayITEMS table (so as to allow a many-to-many relationship between Payments and Expenses).

             

            Expenses:

            Expenses::Subtotal_cn       =        Sum (Expenses_Items::Cost_n)

            Expenses::TaxRate_n         =        a number

            Expenses::Total_cn             =        Expenses::Subtotal_cn * Expenses::TaxRate_n

            Expenses::Total_sum          =        Summary field totalling Total_cn

            Expenses::Payments          =        Sum(PayITEMS::Amount_n)

            Expenses::Payments_sum  =       Summary field totalling Expenses::Payments

            Expenses::Balance_cn        =       Expenses::Total_cn - Expenses::Payments

            Expenses::Balance_sum     =       Summary field totalling Expenses::Balance_cn

             

            I can actually pull the total back to the Items table by doing this:

             

            Items::Cost_n                =        a number

            Items:CostPlusTax        =        Items::Cost_n * Expenses::TaxRate_n

             

            And by doing this, the values to cost in the subsummaries work. 

             

            The problem is displaying the correct Balance, since the Payments are applied to Expenses as a whole.

             

            Any ideas?

             

            Thanks,

            Michael

            • 3. Re: Summarizing fields on a report
              mvreade

              OK, I got it to work, though it looks very convoluted...  Please let me know what you think:

               

               

              CostPlusTax_cn =   

              Cost_cn + (Cost_cn * ExpenseTMS_Expenses::TaxRate)

               

              CostPlusTax_sum =  

              Total of CostPlusTax_cn

               

              Payments_cn =

              Sum(ExpenseTMS_Expenses_PayTMS::Amount_n) / ExpenseTMS_Expenses::Total_cn * CostPlusTax_cn

               

              Payments_sum =

              Total of Payments_cn

               

              Balance_cn =

              (Cost_cn + (Cost_cn * ExpenseTMS_Expenses::TaxRate))

              -

              (Sum(ExpenseTMS_Expenses_PayTMS::Amount_n) / ExpenseTMS_Expenses::Total_cn * CostPlusTax_cn)

               

              Balance_sum =

              Total of Balance_cn