8 Replies Latest reply on Feb 3, 2009 3:23 AM by andyg4137

    Report Calculations

    andyg4137

      Title

      Report Calculations

      Post

      Is it possible to calculate a ratio or percentage of 2 fields in a sub summary report?

       

      i have done the calculation and despite placing it in the sub summary it calculates it based on the total records rather than the ones in the sub summary.

       

      please help before the computer flys!!!!!

        • 1. Re: Report Calculations
          TSGal

          andyg4137:

           

          Thank you for your post.

           

          The calculation function you are probably using is:

           

          GetSummary ( summaryField ; breakField )

           

          Since you are running sub-summary report, make sure the field that is based upon the sub-summary is entered as the "breakField".  Otherwise, you will get a result from the entire table.  For example, if you have a Sub-Summary part by the Customer field, make sure the calculation is:

           

          GetSummary ( summaryField ; Customer )

           

          Let me know how this works.

           

          TSGal

          FileMaker, Inc. 

           

           

           

          GetSummary ( summaryField ; breakField ) 

          • 2. Re: Report Calculations
            andyg4137
              

             

             

            Hi TSGAL

             

            the sub summary is sorted by the consultants name to give off their individual results but the ratio box still appears to calc over all records rather than the fields in the sub summary

             

             

            • 3. Re: Report Calculations
              TSGal

              andyg4137:

               

              Thank you for the additional information.

               

              The formula would be:

               

              GetSummary ( summaryField 1 ; Consultant ) / GetSummary ( summaryField 2 ; Consultant )

               

              Make sure this calculation field is located in the SubSummary by Consultant area of the layout.

               

              If this still isn't working, give me a specific example, and I'll try it here.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Report Calculations
                andyg4137
                  

                TSGal

                 

                 

                thank you, works a treat except that if the ratio is zero example had 4 appointments and didn't sell any it shows as a question mark.

                 

                cherrs for your help on this

                 

                Andy

                • 5. Re: Report Calculations
                  TSGal

                  andyg4137:

                   

                  Well, if your denominator is zero, then you will definitely have problems!  You could modify the calculation to:

                   

                  If ( GetSummary ( summaryField 2 ; Consultant ) = 0 ; 0 ; 

                     GetSummary ( summaryField 1 ; Consultant 0 / GetSummary ( summaryField 2 ; Consultant )

                   

                  That is, check to see if the denominator is zero.  If so, then report zero.  Otherwise, perform the calculation. 

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Report Calculations
                    andyg4137
                      

                     

                     

                    All working fine now thanks, next question :)

                     

                    i have created the consultants report in the consultants database to analyse their results which pulls information from 2/3 other databases which works fine although as i am sorting by consultants name it will only show th report for teh whole database and will not let me do alternative searches such as a date range of appointment dates.

                     

                    i imagine it is something to do with an additional sub summary but cannot get it to work 

                     

                    any ideas?

                     

                    thanks as always

                     

                    • 7. Re: Report Calculations
                      TSGal

                      andyg4137:

                       

                      If you perform a Find first, it will limit the number of records.  Then, when you perform the Sort, the calculations will give you information for the found set; not the entire database.

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: Report Calculations
                        andyg4137
                           Hi, thats what i was doing but it would still pull through the info for all records but have managed to solve it now, thanks for all your time and effort