5 Replies Latest reply on Aug 26, 2014 5:32 AM by john.s

    Help adding Percent to report

    john.s

      Title

      Help adding Percent to report

      Post

           Hi,

           I'm adding a financial module to our photography business FM database.  I bring in bank statements and add a category to each transaction.  I have two type summary fields to sum Credits and Debits.  And produce a report by category.  I am now grouping the categories into higher levels.  So I have a Overhead with several expense categories under it and Cost of Good sold with a few categories, etc.  The report looks like this

           Category                   Credits            Debits       Percent

      Deposits                         $  xx,xxx

      Overhead

                    Rent                                                     $ x,xxx

                   Utiilities                                                 $ x,xxx

        Total Overhead                                         $x,xxx              xx%

            

      Cost of Goods Sold

                   Lab Fees                                               $ x,xxx

                   etc                                                         $ x,xxx

                    etc                                                        $ x,xxx

                   etc                                                         $ x,xxx

      Total Cost of Goods Sold                           $ x,xxx            xx%

      Etc

              Grand Total          $ xx,xxx                $ xx,xxx     

           Report is produced using the type summary field for the  Credits and Debits and the dollar amounts are working.  I can not figure out how to get the percent of total for  each major category for the debits.

           Input is:

           BankTransactions::AmountCredit  (type number)

           BankTransactions::AmountDebit   (type number)

           BankTransactions::SumCredits  (type summary  =Total of AmountCredits)

           BankTransactions::SumDebits  (type summary  =Total of AmountDebits)

           BankTransactions::__fk_Category

           BankTransactions::__fk_MajorCategory

           Any help will be greatly appreciated

            John

        • 1. Re: Help adding Percent to report
          philmodjunk

               I assume that the totals shown in bold face are placed in Sub Summary layout parts?

               If so, take a look at the getSummary function. If you refer to a summary field in a calculation, you get the same total that you'd see in a grand summary layout part not the sub total from the sub summary part. But if you use GetSummary and specify the same "break" field specified for the sub summary layout part, you can access that sub total and use it in your calculation.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Help adding Percent to report
            john.s

                 Thanks Phil,

                 I looked at the getSummary function and created type calculation field with the following Calculation on the BankTransactions table

                 (GetSummary ( Sum Expenses ; ReportCategory::_pk_MajorCategory )  / Sum Expenses)    (Calculation result is Number)

                 My original input description above is not correct.  I've added a table for the Major Categories called ReportCategory so the relationship looks like this.

                 BankTransactions::__fk_Category >-----Category::_pk_Category

                                                                                 Category::__fk_ReportCategory >--------ReportCategory::_pk_MajorCategory

                 The Report is based on the BankTransactions table with a Summary Part for the BankTransactions::_fk_Category break field and uses the BankTransactions::Sum Expenses field.

                 A second Summary Part is based on the break field ReportCategory::_pk_MajorCategory and uses the same Sum Expenses field.

                 A Trailing Grand Summary Part also uses the Sum Expenses field.  

                 All summary totals are correct but the calculation field I added shows 0.   What am I missing?

                  

                  

            • 3. Re: Help adding Percent to report
              philmodjunk

                   You've hit your toe on a significant detail where what you have works for the sub summary part ,but not the GetSummary function. For a sub summary part, your break field can refer to a field in a related table. but this does not work in the GetSummary calculation where both summary field and break field must come from the same table occurrence.

                   But you can usually get around this limitation by adding a calculation field in your summary field's table (BankTransaction) that copies the field from the related table. You then need to include this field in your sort order. So a calculation field that simply has:

                   ReportCategory::_pk_MajorCategory

                   as its sole calculation term should work in your getsummary function if you include this new calculation field in your current sort order.

              • 4. Re: Help adding Percent to report
                john.s

                     ok...I'm still getting a result of 0.

                     I added the Calculation field and verified the result on a layout.   I also added the calculation field to the report layout and to the script in the current sort order.  Do I need to redo the report or have I missed something else?

                      

                      

                • 5. Re: Help adding Percent to report
                  john.s

                       Ignore my last post...I made a dumb mistake.  Your solution works perfectly Phil...Thank you again.  You're the best.