4 Replies Latest reply on Dec 8, 2009 12:30 PM by mrvodka

    How do I create a summary report?

    jurgmay_1

      Title

      How do I create a summary report?

      Post

      Hi there,

       

      I'm trying to summarise a number of records into a specific format and am having real difficulty! I wonder if someone can point me in the right direction?!

       

      I need to create a report that looks something like this...

       

                    Mon   Tue   Wed   Thu   Fri   TOTAL

      Product 1      1     4     2     11    8      26

      Product 2      0     8     5      6   12      31

      Product 3      3     2     1      8    2      16

                

      My records are imported into my database each day and I need to run a report at the end of every week that tells me what products were ordered and how many of each product was ordered on each day. The records contain the product name and order date amongst other information.

       

      I can get a TOTAL count for each product but can't work out how to show the count for each day and display it in the format shown above.

       

      Can anyone help me, please?

       

      Many thanks,

       

      Juergen 

       

        • 1. Re: How do I create a summary report?
          philmodjunk
            

          The report format you specify is sometimes called a "cross tab" report. Filemaker can produce such a report, but it takes some extra work. The following format is much easier to create in Filemaker:

           

          Product 1

             Mon 1

             Tue 4

             Wed 2

             Thu 11

             etc.

          Product 1 Total:  26

           

          Product 2

             Mon 0

           

          and so forth.

           

          To use the columns approach, you'll need two tables a a separate relationship for each day of the week with a different calculation for each--using the sum function.

          Here's a some calculation fields that you can use for "Monday" totals:

           

          cMonday defined in Product Table (Unstored):

          Let ( Today = get ( currentdate ) ; Date ( Month ( Today ) ; Day ( Today ) - dayofWeek ( Today) + 1; Year (Today) )

           

          cMonday defined in "Data" table (Stored):

          Let ( D = DateField ) ; Date ( Month ( D ) ; Day ( D) - dayofWeek ( D) + 1; Year (D) )

           

          Define a relationship and name the TO box for the Data table side "TotalsforMonday":

          ProductTable:: ProductID = DataTable:: ProductID AND

          ProductTable::cMonday = DataTable::cMonday

           

          Now define a calculation in your Product table, cMondayTotal:

          Sum(TotalsforMonday::YourField)

           

          You'll have to do all of the above 4 more times to get Monday through Friday columns. If you don't want the totals computed based on your current date, you can define a global date field in your Product table and use it in your calculation fields in place of Get ( CurrentDate )

          • 2. Re: How do I create a summary report?
            mrvodka
              

            PhilModJunk wrote:

            cMonday defined in Product Table (Unstored):

            Let ( Today = get ( currentdate ) ; Date ( Month ( Today ) ; Day ( Today ) - dayofWeek ( Today) + 1; Year (Today) )

             

            cMonday defined in "Data" table (Stored):

            Let ( D = DateField ) ; Date ( Month ( D ) ; Day ( D) - dayofWeek ( D) + 1; Year (D) )



             

            There is a small typo here: Let ( D = DateField; Date ( Month ( D ) ; Day ( D) - dayofWeek ( D) + 1; Year (D) )  // extra paranthesisin Let ()

             

            However this can be simplified to just: DateField - DayOfWeek ( DateField )

             

             



            • 3. Re: How do I create a summary report?
              philmodjunk
                

              Thanks! but since DayofWeek returns the values 1...7 for Sunday through Saturday, I believe this should be:

               

              DateField - DayOfWeek ( DateField ) + 1

               

              Otherwise you are computing a date for Saturday of the previous week and that might cause some confusion.

               

               

              • 4. Re: How do I create a summary report?
                mrvodka
                  

                Yes indeed. I forgot to type the 1.

                 

                Anyway, for this report I may recommend using the "fast summaries" technique.

                 

                http://www.kevinfrank.com/download/kf-fast-summary.zip