9 Replies Latest reply on Feb 5, 2009 10:57 AM by TigerKim

    Sub Summary  Report  - 2 problems I'd like some help with.

    TigerKim

      Title

      Sub Summary  Report  - 2 problems I'd like some help with.

      Post

      I've created a Summary Report of my Work Orders using Sub Summary Parts.

      The Summary Report looks something like this:

       

       

      1. Film Title                                   

          2. Service Type (HD, SD)----------4.Total#of ServiceType (HD,SD)-----5.Total Amount = (Total#ofServiceType  X  )

               3.Spot/Clip Title                                                                                                      Service Rate Price        )

       

       

      Firstly, I can't get 5. "Total Amount" to work correctly. The only thing I can think of is using a Calculation Field but it won't work. Sometimes the Amount number is off by +1000 for some reason.

       

      Secondly, how do I get the Summary Report to list everything with SERVICE TYPE (HD) and then continue listing for the same FILM TITLE but with SERVICE TYPE (SD)? 

       

      Thank you Filemaker Pro Masters for any and all help.

       

      Tiger KIm

       

        • 1. Re: Sub Summary  Report  - 2 problems I'd like some help with.
          Orlando
            

          Hi Tiger Kim

           

          For your first question, how are you getting the Total#of ServiceType value, is this a number, Calculation or a summary field? And the same for the Service Rate Price.

           

          Secondly, is your second Sub Summary Part on your layout set to summarise by SERVICE TYPE? and if so is the sort order in the same order as your Sub Summary Parts? And if it is what result are you getting?

          • 2. Re: Sub Summary  Report  - 2 problems I'd like some help with.
            TigerKim
              

            Orlando!

             

            Thanks for replying. Hoping you would drop some knowledge once again.

             

            I got the summary report to give me a TOTAL OF # all the SERVICE HD TYPES for let's say FILM TITLE "A". And under that, it lists the various SPOT/CLIP TITLES of the same film. 

             

            I've got different SERVICE VALUES LISTED for SERVICE HD and SERVICE SD. They're just dollar figures-Numbers.

            The TOTAL#OF SERVICE TYPE isn't anything right now. I've tried making it a Summary field and a calculation. Problems understanding.

             

            The Sub Summary parts on my report are: FILM TITLE, SERVICE TYPES, SPOT/CLIP TITLES so far.  I believe the sort order is the same as my Sub Summary Parts. It looks good so far. I a list that breaks it down by FILM TITLE, SERVICE TYPE, SPOT/CLIP TITLE. It even numbers each SPOT/CLIP TITLE for me. Also gives me a TOTAL of HOW MANY SERVICE TYPES each film got. Can I send you a screen shot? Will this help? 

             

            Thanks again!

             

            Tiger Kim

             

            • 3. Re: Sub Summary  Report  - 2 problems I'd like some help with.
              TigerKim
                

              Sorry. Forgot to mention one important thing for the 1st part of my question. Each FILM TITLE can have 2 SERVICE TYPE fields. The first SERVICE TYPE field can list either HD, SD, QT or FLV. Now if the FILM TITLE with the same SPOT/CLIP NAME needs some more work done on it, The 2nd SERVICE TYPE field can also list QT, FLV in addition to HD or SD.

               

              So this is how I want it to look:

               

               FILM TITLE 1

                    SERVICE TYPE 1 (HD) -----------------------------------TOTAL #6 OF SERVICE TYPE HD FOR FILM TITLE 1

                           SPOT/CLIP NAME 1 ------------------------------------------------------------------------------------------------HD #2                                                                                                                         

                           SPOT/CLIP NAME 2 ------------------------------------------------------------------------------------------------HD #2

               

                           SPOT/CLIP NAME3  ------------------------------------------------------------------------------------------------HD #2

               

                    SERVICE TYPE 2 (QT) ------------------------------------TOTAL #4 OF SERVICE TYPE QT FOR FILM TITLE 1

                           SPOT/CLIP NAME 1 ------------------------------------------------------------------------------------------------QT #1

                          

                           SPOT/CLIP NAME 2 ------------------------------------------------------------------------------------------------QT #2

                          

                           SPOT/CLIP NAME 3 ------------------------------------------------------------------------------------------------QT #1

               

              I can only get the SERVICE TYPE 1 to list with the corresponding SPOT/CLIP NAMES & TOTAL # OF SERVICE TYPE. 

               

               

                    

                  

                    

                    

              • 4. Re: Sub Summary  Report  - 2 problems I'd like some help with.
                Orlando
                  
                Hi TigerKim
                 
                Sorry for the delay, busy weekend. 
                 
                Lets start with getting the Total Amount to work correctly.

                You seem to be along the right track with this, however just missing one vital step, you need a calculation to sum the total amount for your Sub-summary part by using a the GetSummary function and the field your sub-summary is using as the break.

                I will walk through the process from scratch to make sure we cover everything.

                • To start off you need to add a field TotalRecords and specify the Type as "Summary", this will give us a count of the number of records in an individual sub-summary row.
                • Now when you hit "Create" a dialog will appear called Options for Summary "TotalRecords"
                • In the area titled 'Available Fields' select a fields that will always have a value like a RecordID field and make sure to the left it is set to "Count Of" and click 'OK' 

                This will count all the records in the found set, or summary part, as long as the field chosen has a value in.

                Now you need to create a calculated field with a GetSummary function so you can use that TotalRecords count in your calculation to work out the Total Amount.

                • So add a field SumTotalRecords_ServiceType and Specify the type as "Calculation"
                • Now in the Specify Calculation dialog input the following

                GetSummary ( TotalRecords ; Service Types )

                • Now click OK and go back to your Total Amount Calculation and change it to

                SumTotalRecords_ServiceType * Service Rate Price

                This should now give you the correct amount in your Total Amount calculation.

                Now with regards to the Service Types, is there one field with different values or two separate fields with different values, you mention Service Types 1 and service Types 2 so I am not sure. The summary will only allow you to summarise by one field and if you have values in two fields this may explain the issue you are having.

                With the Spot/Clip Title, are you summarising these so any with the same name are grouped? Otherwise you may just want to make this a standard Body layout part so any with the same name do not merge into the same row, depending on how you want this.

                A screen shot would be very helpful.
                • 5. Re: Sub Summary  Report  - 2 problems I'd like some help with.
                  TigerKim
                    

                  Orlando,

                   

                  Thank you so much! I followed the steps regarding GETSUMMARY FUNCTION and it worked like butta! Big Ups to you once again.

                   

                  Regarding the 2nd part of my problem, yes. I have 2 different SERVICE TYPE fields, each one having different values.

                   

                  I want the report to list everything for say FILM TITLE A>SERVICE TYPE 1>SPOT/CLIP NAMES - with their corresponding SERVICE TYPE 1 TOTAL and TOTAL AMOUNTS (SERVICE TYPE 1 TOTAL X SERVICE RATE). And then I want this list to continue going right to SERVICE TYPE 2>SPOT CLIP NAMES - with their corresponding  SERVICE TYPE 2 TOTAL and TOTAL AMOUNTS (SERVICE TYPE 2 TOTAL X SERVICE RATE). I don't want it to re-list the FILM TITLE. Just 1 FILM TITLE for both SERVICE TYPES. In the end, I might have to expand to have 5 different SERVICE TYPE FIELDS.

                   

                  Here's a link to see a screen grab of my WORK ORDER and my MONTHLY REPORT. Use the zoom/all image buttons for a better look:

                   

                  http://www.flickr.com/photos/tigerk/?saved=1

                   

                  Best,

                   

                  TigerKim

                  • 6. Re: Sub Summary  Report  - 2 problems I'd like some help with.
                    Orlando
                      

                    I just have a couple more questions to get a better understanding of what you have.

                     

                    Will all your records have a value in both of the SERVICE TYPE fields, or will they be one or the other?

                     

                    Looking at your screen shot, could you have more than 2 service types? There appears to be fields for up to 5, or am I reading it wrong?

                    • 7. Re: Sub Summary  Report  - 2 problems I'd like some help with.
                      TigerKim
                        

                      There will always be at least 1 SERVICE TYPE for a FILM TITLE - SPOT/CLIP NAME. But depending on the job we get assigned, there could be 2, 3, 4, or 5 SERVICE TYPES for one SPOT/CLIP NAME.

                       

                      WORK ORDER EXAMPLE: FILM TITLE A

                                                                   SPOT/CLIP NAME

                                                                           FOOTAGE TYPE

                       

                                                                           SERVICE TYPE 1>HD 

                                                                           SERVICE TYPE 2>SD

                                                                           SERVICE TYPE 3>QUICK TIME FILE

                                                                           SERVICE TYPE 4>FEDEX OVER NIGHT

                      • 8. Re: Sub Summary  Report  - 2 problems I'd like some help with.
                        Orlando
                          

                        Hi TigerKim, apologies for the delay in getting back to you.

                         

                        How are you getting on with this. What I think will work best for you it to split out the Service Type data into separate records when running the report, so creating a table just to hold that data when the report is run, and basing the Service Type row on that table. Does this make sense?

                         

                        To do this you will need to run a script that goes through each record, checking each Service Type field and creating a record in the Temp Table when the is a value in that field, then modify the report layout, we would then need to look at how you want to display the SPOT CLIPS, does the SPOT CLIP have a type that matches your Service Type, or do you want all displayed for each Service Type.

                         

                        Should not be to difficult to do and if you do want to go down this route I will put a script together for you.

                         

                        How does this sound? 

                        • 9. Re: Sub Summary  Report  - 2 problems I'd like some help with.
                          TigerKim
                            

                          Thanks for getting back to me to Orlando! You had me worried there for a sec.

                           

                          I'm down with whatever approach you can make this work.

                           

                          Ultimately, I want the report to list for each specific FILM TITLE, all the SERVICE TYPES performed for the related SPOT/CLIP TITLES. I'd like it to look like this for example: 

                           

                          a) Film Title

                                  b) Service Type 1

                                                  c) Spot/Clip Title A

                                                  d) Spot/Clip Title B (if there is one)

                                                  e) Spot/Clip Title C (if there is one)

                                  d) Service Type 2 (if there is another)

                                                  f) Spot/Clip Title A (if there is one)

                                                  g) Spot/Clip Title B (if there is one)

                                                  h) Spot/Clip Title C (if there is one)

                                  i) Service Type 3 (if there is another)

                                                  j) Spot/Clip Title A (if there is one)

                                                 k) Spot/Clip Title B (if there is one)

                                                  l) Spot/Clip Title C (if there is one)

                                  m) Service Type 4 (if there is another)

                                                   n) Spot/Clip Title A (if there is one)

                                                   o) Spot/Clip Title B (if there is one)

                                                   p) Spot/Clip Title C (if there is one)

                                  q) Service Type 5 (if there is another)

                                                  r) Spot/Clip Title A (if there is one)

                                                  s) Spot/Clip Title B (if there is one)

                                                  t) Spot/Clip Title C (if there is one)

                           

                          There can only be a maximum of 5 SERVICE TYPES for each FILM TITLE.

                          But there can be any number of SPOT/CLIP TITLES for each SERVICE TYPE.

                          Does that make sense?

                           

                          And lastly there's the SERVICE TYPE TOTALS for each SERVICE TYPE, and

                          the TOTAL AMOUNT (i.e. SERVICE TYPE TOTALS X SERVICE TYPE RATE). We've

                          got these parts working, but not sure if it'll need to change with your new script.

                          Please refer to the screen grabs I sent earlier.

                           

                          Thanks again for all your help Orlando. 

                           

                          TigerKim