4 Replies Latest reply on Mar 16, 2012 7:05 AM by mora_1

    Calculating Sub-Totals from 3 Summaries

    mora_1

      Title

      Calculating Sub-Totals from 3 Summaries

      Post

      Hi

      I am very new to FMP and databases, and am on FMP Pro 11. Can someone please help me get a sub total from 3 summaries created in a report.

      There are different products, and each product have different types of sales.

      Example of Product Field records = table cloth, dinner napkins, table runners

      Sales has 3 fields = Direct Sales, Commission Sales, Referred Sales.

       

      I have created a report that summarizes the different sales for each product. How do I get a sub-total sales for each product?

       

      Example:

      Table Cloth Summary:  Direct sales = $1200, Commission Sales = $600, Referred Sales = $500. How do I get sub-total sales for Table Cloth?

      Curtains Summary: Direct sales = $2000, Commission Sales = $800, Referred Sales = $400. How do I get sub-total sales for Curtains?

        • 1. Re: Calculating Sub-Totals from 3 Summaries
          philmodjunk

          Create a new layout based on the same table and enter layout mode.

          Double click the label for the body layout part to bring up the part setup dialog.

          Change the body part into a sub summary part "when sorted by" your Product field.

          put your product and summary fields in this sub summary part. You can resize the body part by dragging the bottom boundary line up until you have a sub summary part that is just a narrow row containing your fields.

          Save your changes and return to your original layout--not the new layout. Perform a find to pull up just the recors you want for your report. (you can't manually perform the find on the new layout.) Switch to the new layout, specify view as list and sort your records by the Product field you specified as the "when sorted by" field for the sub summary part. (The layout will be blank until you sort the records by this field.)

          For more on summary reports and what you can do with them, see this tutorial thread: Creating Filemaker Pro summary reports--Tutorial

          (This thread has been around long enough that any comments posted to it do not put the thread back up into the Recent Items List. If you have questions or other comments about this tutorial, please post them in this thread or start a new thread so that I don't miss your comments.)

          • 2. Re: Calculating Sub-Totals from 3 Summaries
            mora_1

            Thanks PhilModJunk for your reply. But I am unable to change the body part into a sub summary part by Product filed because I already have a sub summary fo products. I think I wasn't clear enough - sorry.

            I have my report sorted according to Products field. Then I have a summary that totals the 3 different sales figures for each Product.

            Table Cloth has 3 total summary: Direct sales = $1200, Commission Sales = $600, Referred Sales = $500.

            How do I create a sales sub total for Table Cloth that does this below the Table Cloth summary totals:  $1200 + $600 + $500 = $2300

             

            Then Curtains has a total summary: Direct sales = $2000, Commission Sales = $800, Referred Sales = $400.

            How do I create a sales sub total for Curtains that does this below the Curtain summary totals:  $2000 + $800 + $400 = $3200

             

            And the same for the other Products.....

             

            I can't create a calculation to do this and can't seem to figure out how to go about it. Thanks for your help PhilModJunk.

            • 3. Re: Calculating Sub-Totals from 3 Summaries
              philmodjunk

              Define a calculation field as

              GetSummary ( Direct Sales ; Products ) + GetSummary ( Commission Sales ; Products  ) + Get SUmmary ( REferred Sales ; Products )

              GetSummary is a function that enables you to use the totals you'd get in a sub summary part in a calculation. THe second field listed shold be the same field you'd use as your "when sorted by" field for the sub summary part.

              • 4. Re: Calculating Sub-Totals from 3 Summaries
                mora_1

                THANK YOU VERY VERY MUCH PhilModJunk. I'd hug you if I could.

                It seems that I have much to learn.