5 Replies Latest reply on Dec 4, 2014 4:11 PM by planteg

    Creating a report based on sum() per period

    planteg

      Hi,

       

      I understand that the title may not be too clear, I couldn't find a better one.

       

      This is for a system that tracks time entries from employees. The table has these columns:

       

      • Employee
      • Date
      • Hours worked
      • Customer
      • Project

       

      For the report, the user enters two dates that are automatically adjusted for a Sunday as the beginning and a Saturday for the end. The period may span one more weeks.

       

      What I need to do is for each week in the period, get for each customer the number of hours worked for a specific project. And this is where my head bangs on the desk. I don't see how I can do simple calculations using calculated fields or summary fields on the table.

       

      I don't see how I can realize that report without generating a temporary table. This is a way I don't like to go. Which way is the best to realize that report ? I hope I am clear enough.

       

      I just had a thought: I could add a calculated field which would be set to the Sunday a specific entry is attached, this way defining a specific Week. Then the report would get sub-summary totals per Week and per customer. Could that work ?

       

      Thanks

        • 1. Re: Creating a report based on sum() per period
          erolst

          planteg wrote:

          xI just had a thought: I could add a calculated field which would be set to the Sunday a specific entry is attached, this way defining a specific Week. Then the report would get sub-summary totals per Week and per customer. Could that work ?

           

          Thanks

           

          Yes, that is the correct approach; create a calc field, type date, as

           

          Let ( cd = yourDateField ; cd - Mod ( cd ; 7 ) )

           

          Now this field has the same (Sunday) date for all entries belonging to the same Sunday-Saturday period, and you can use it as one of your sort/break fields.

          • 2. Re: Creating a report based on sum() per period
            planteg

            Ok, so I was right on that track. Now is there a way to get a subtotal per customer and project in a summary field ? The report should look like:

             

            Customer 1

             

            Project A   21.0

            Project B   10.0

            Project C  9.0

             

            Customer 2

             

            Project G  10.0

            Project H  10.0

            Project I    10.0

            Project J   10.0

             

            Projects are linked to a specific customer.

             

            Thanks

            • 3. Re: Creating a report based on sum() per period
              erolst

              planteg wrote:

               

              Now is there a way to get a subtotal per customer and project in a summary field ?

               

              Are you asking how summary fields and sub-summary parts work?

               

              IIIUC, create a summary field Total of [your amount field ], add two sub-summmary parts "by Customer" and "by Project", place the summary field into whatever part you need summarized, and sort by customer and project. 

               

              btw, I think I've misunderstood your original request. If you don't want to summarize by weeks, but summarize within a given week, you don't need a calc field; instead, use the calculation within a script to define the the start and end date of a Find request, as in

               

              Let ( [ cd = aDateExpression ; sun = cd - Mod ( cd ; 7 ) ] ; sun & ".." & sun + 6 )

               

              to find all records within that week.

              1 of 1 people found this helpful
              • 4. Re: Creating a report based on sum() per period
                planteg

                "Are you asking how summary fields and sub-summary parts work?"

                 

                Yes finally.

                 

                " I think I've misunderstood your original request."

                 

                My previous post was erroneous, it should have read

                 

                Customer 1

                 

                Week 1

                 

                Project A   21.0

                Project B   10.0

                Project C  9.0

                 

                Week 2

                 

                Project A   21.0

                Project B   10.0

                Project C  9.0

                 

                Customer 2

                 

                ...

                 

                As far as I understand, the summary field will we calculated according the context set by the sub-summary parts ?

                 

                I will give it a try right away.

                • 5. Re: Creating a report based on sum() per period
                  planteg

                  Well, that was that easy. I created a summary field, created three sub-summary part and voilà !

                   

                  You realoly have to think differently when working with FileMaker. When you come from other solutions, it's hard to synchronize your past knowledge to FileMaker ways. But very intereting.