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.
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.
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.
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.
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.
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:
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
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.
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::ReportAccountName::StaffActivityStats::EntryAccount AND gMonth=ApptMonth AND gYear=ApptYear
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.
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".
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
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