10 Replies Latest reply on Sep 13, 2016 12:32 PM by philmodjunk

    Report with Multiple Sum Fields

    Annette

      Hello,

       

      I am trying to create a statistics report.  I have four tables that various relationships are set up on with loads of different calculations.  Then summary fields giving me totals from these calculation fields.  All works as desired.  I am trying to create a report layout which has all these various summary fields on it.  The problem is, if I put the summary fields themselves on the layout it is constantly refreshing the fields which is not only annoying but takes up a few minutes as there are so many.  Is there a better way to do reports like this...other than creating LOADS of variables and pasting variables into text fields?  Are there fields that can grab the sum based and I can that put those fields on the report layout?

        • 1. Re: Report with Multiple Sum Fields
          PeterWindle

          take a look at getsummary

          consider using execute SQL

          Depending on how many records you're dealing with, SQL may be quicker... but I think you might want to experiment with one field first, then create multiples.

          • 2. Re: Report with Multiple Sum Fields
            fmpdude

            How much data do you have? I've never seen this issue. Are you running this report on an old version of FM on perhaps older hardware with not that much memory? FileMaker likes lots of CPU power to run. In a recent posting, a user was trying to import a 8 GB CSV file on Windows with only 2 GB RAM. His machine crashed after several HOURS. I imported a 16 GB file on the Mac version (with 16 GB RAM) in about 20 minutes with no issues. (FileMaker wants a powerful machine to run.)

             

            ------

             

            Expanding on what Peter said, you could even do reports remotely using a separate tool like RazorSQL to run SQL against your actual FileMaker Database.

             

            If you have LOTS of data (hundreds of thousands of records), SQL in FM may be slow. You will need to benchmark SQL in your application as SQL can also be fine in FM.

             

            HOPE THIS HELPS.

            • 3. Re: Report with Multiple Sum Fields
              philmodjunk

              A long standing method for accelerating the display of aggregate data is to compute the aggregate values in advance and store them in their own table. This can be extremely effective or totally useless depending on how and when your data is created and modified.

               

              Many years ago, I set up a system for one client where data from the day's purchase orders are summarized into a summary report table each night. The script uses a loop and performs finds to isolate groups of line items all in the same category and then creates a single summary record for that category, date and with simple number fields that store aggregate values such as a sum and an average. This process "condenses" the total number of records from about a thousand to about 20 or so records in their case which means that they can look at aggregate values in cross tab type reports comparing them by month (rows) over a 5 year period (columns) without waiting for any of the values to compute as the number of calculations per "cell" in the report are quite small.

               

              This works for them because once a Purchase Order is finalized and printed, it is never changed again. Other types of data that need frequent updates don't lend themselves well to this method.

              • 4. Re: Report with Multiple Sum Fields
                Annette

                There are about 100 summary fields on the layout.  Some have less, that would be the largest report. I am using FM 15 and it's not a hardware issue.  I have no experience with RemoteSQL so am trying to work within what I can do at the moment.  I just thought maybe there was a function that could capture what appears in summary fields as I've already done the work of creating calculations that give the numbers/summary fields to total them based on the multiple relationships.

                • 5. Re: Report with Multiple Sum Fields
                  Annette

                  Whilst I don't know how to go about your method, it sounds like it would be ok.  Basically this report is for monthly statistics.  The data "In theory" shouldn't change unless the users forgot to amend/add activity within the month period.  I find it crazy that there is not a simple straight forward way to do this.  I've spent ages making the calculation fields for each statistic needed along with the summary fields needed to total them.  Or....maybe there is, and I am just unaware due to my lack of experience.

                  • 6. Re: Report with Multiple Sum Fields
                    philmodjunk

                    You also have not really described what you have in enough detail for us to be able to provide much in the way of detailed assistance. It's possible that your design could be altered to be much more efficient. "100 summary fields" sounds like a pretty extreme design that should be re-examined to produce better efficiency, but that's an opinion based on very limited information.

                     

                    The script that I set up followed this basic outline:

                     

                    Loop

                        Find all records with today's date that do not have a "mark" value in a specific field

                        Exit loop if no records found

                        Constrain found set to just records of the same category as the current record

                        Use set field steps to copy values from summary fields into number fields of new related record

                        "mark" this found set by setting the mark field to a value that keeps records from being found again by this script

                    End Loop

                     

                    I used a relationship that matched by category and date with "create" enabled so the act of setting a field in that related table created a new related record.

                    • 7. Re: Report with Multiple Sum Fields
                      Annette

                      Apologies.  Basically, every month each staff member has to return statistics of various information.  On the staff main screen I have global fields where they select the name they are running a report for (they can run for some other staff members as well as their own), the month of the stats, the year of the stats, and a specific service.

                       

                      There are many different totals that are required in the rerport month.  Number of appointments, numbers referred, number of assessments, waiting, etc.  So on each of the appropriate tables I have created calculation fields which determine the requirements to need each figure, if they meet the requirements it returns 1 otherwise 0.  Then I have a summary field for each of those calculations.

                       

                      I have the report layout based on MainMenu2 and the relationships set up on that. 

                       

                      I have the following tables/relationships:

                       

                      MainMenu2::gStaff=ServiceActivityStats::Caseload

                      MainMenu2::gStatService=ServiceActivityStats2::EOC_Location

                      MainMenu2::gStatService=ServiceActivityStats3::Caseload

                      MainMenu2::gMgrID=ServiceActivityStats5::AssocMgrID

                      MainMenu2::ReportAccountName::StaffActivityStats::EntryAccount AND gMonth=ApptMonth AND gYear=ApptYear

                      MainMenu2::gMgrID=StaffActivityStats2::AssocMgrID

                       

                      Again, all the calculations work as desired and produce the correct numbers on testing.  My issue is, when you go to the report page which has all the summary fields on it, its taking time refreshing all of the fields on it.  I'm looking for getting the results onto the page as quickly as possible so that it doesn't do all that refreshing.  I'm not sure if that offers any more information to you guys, or even useful information, but that's basically how it's designed in a nut shell.

                      • 8. Re: Report with Multiple Sum Fields
                        philmodjunk

                        Ultimately you may need a consultant to go over your design in detail.

                         

                        One simple question: Do you need all of those summary fields on the same layout? What if you had several layouts that each displayed a different sub set of the total, the individual layouts would update more quickly than the one you have with "over 100 summary fields".

                        • 9. Re: Report with Multiple Sum Fields
                          Annette

                          Ok, your losing me now, because how do different layouts then help me generate one full report?  Surely it can't be this difficult. :O

                          • 10. Re: Report with Multiple Sum Fields
                            philmodjunk

                            Well I asked this as a question. 100 different summary fields seems more than you would for a single report so I offered this as an option IF you didn't use them all in a single report.

                             

                            A screenshot of your layout created while in layout mode might be enlightening.

                             

                            Sent from my iPhone