4 Replies Latest reply on Jun 10, 2010 8:31 AM by philmodjunk

    tricky summary problem?

    symbister

      Title

      tricky summary problem?

      Post

      Hi all

       

      using 8.5 on mac os 10.5, progressing newbie..

       

      I have a bunch of raw sales data with individual records in the form:

       

      Trans date

      Amount

      MOP (Mastercard, Visa, Cash, Cheque or EFTPOS)

      Event (one of up to ten events)

       

      what I'm wanting is a summary report, giving me a listing like:

       

      TransDate  | Event |  Total M/C  |  Total Visa |  Total Cash |  Total Chq |  Total EFT |  Day's total for that show |  Day's Grand Total

       

       

      is this a Filemaker possible scenario?

       

      not sure how to go about it, any help appreciated. (I know I can do it in Excel with quite a bit of filtering, but would like it to auto-run in FM)

       

      thanks in advance

       

       

       

        • 1. Re: tricky summary problem?
          Ender

          It would be tricky to do your summary in one horizontal line like that.  FileMaker likes to break summaries up more in a vertical orientation.

           

          What you'd do to achive this is use a sub-summary report.  It's basically a list layout with additional parts above and below the Body part to see each break.  This is what the parts might look like:

           

          Header

          ---------------------

          Date (leading)

          ---------------------

          Event (leading)

          ---------------------

          MOP (leading)

          ---------------------

          Body

          ---------------------

          MOP (trailing)

          ---------------------

          Event (traling)

          --------------------

          Date (trailing)

          ---------------------

          Footer

           

           

          The different parts can have different fields, depending on how you want it to look.  Sometimes a part just has a line to provide a visual break.  I'd suggest putting the Date field on the leading Date part, the Event Name on the leading Event part, and the MOP field on the leading MOP part.  When the records are sorted by the break fields (Date, Event, MOP,) the parts will appear in Preview Mode and on printouts.  The Body part can be omitted if you only want to see the subtotals, and not see the records that contribute to them. 

           

          The other thing you would need is summary fields for counting or totaling the Amount.  Go ahead and define a summary Total of Amount, and insert that field in the parts where you want to see them.

           

           

           

          • 2. Re: tricky summary problem?
            philmodjunk

            This is possible, but just like Excel, it takes a bit of work to set up.

             

            Presumably you want one such row for each event for each day that a transaction is recorded.

             

            You'll need calculation fields like this:

             

            If ( MOPS = Mastercard ; Amount ; "" )

             

            To isolate each type of payment.

            You can then define summary fields for each of these columns, place them in a sub-summary part and then delete the body part so that your multiple rows of records will be collapsed down into one such row for each event/date.

             

            If you are new to summary reports, you might want to take a look at this tutorial for some tips and ideas:


            Creating Filemaker Pro summary reports--Tutorial

            • 3. Re: tricky summary problem?
              symbister

              Ok, got it sorted - many thanks

               

              only thing I can't do is get a sub-sub-total of the mastercard and visa payments as a separate amount per day...but that's probably just being picky...

               

              Regards

               

               

              • 4. Re: tricky summary problem?
                philmodjunk

                Treat it like another column. Your payment calc field could be:

                 

                If ( ( MOPS = "Mastercard" ) or ( MOPS = "VISA" ; Amount ; "" )

                 

                Or you could just add the two earlier calcu fields.

                 

                If the calc fields are called cMasterCard and cVisa, the calc would be

                 

                cMasterCard + cVisa

                 

                Either way, you can then define a summary field to compute the total of this field to get sub-totals.

                 

                A Third option would be to use the Get Summary function to add the summary fields for both VISA and MasterCard