Considering the FM database has table - Contacts, with fields - kp_Contact, Name and LocationMap.
Create a 'NameCount' unstored calculation field in contacts table with formula
"select Count(Distinct Name) from contact where LocationMap=?";
Create a layout with sub-summary part sorted by 'LocationMap' field, and place two fields -LocationMap & 'NameCount' in subsumamry section.
I noticed that ExecuteSQL does not notify or prompt if you have typos in your table name and also it does not allow for spaces in table name so once I added an underline to make one name, it worked like a charm. I also found my checkbox to "not store calculations" seemed to be missing so I did that. Additionally, I realized in my haste that I made some typos in my original request so I updated the last name & state properly. Thanks again - I'm so happy to have a working solution that has saved me hours from going crazy!! Here's snapshot of table layout (not the subsummary report).
The SQL in this function is simply text inside of quotes so there is no way that the Specify Calculation dialog can trap for errors in the names inside the quoted string. Instead, you get the dreaded question mark and then have to figure out what you did wrong.
You can enclose your table occurrence and field names in quotes and this is a good idea since many such names will trip a syntac error due to a space, leading underscore or because the name is a reserved word in SQL.
You can quote a name inside the quoted string by using the backslash with the quotation marks: "SELECT \"date\" From.... "
SeedCode's SQLExplorer can be helpful and the SELECT query examples found in the ODBC JDBC quide that you can open from FileMaker help can also provide good examples of correct SQL syntax for use with this function.
Thanks - I did try quotes and it didn't work originally but I didn't know about the backslach. I will check out the SQLExplorer - sounds interesting product.
So - there's a snag I'm running into which is huge performance hit problem . I'm actually using these calculations in a crosstab report which lists monthly sums for fiscal year. The ExecuteSQL field works fine but the dependent field which is Case field that calculates count per month is taking over 10 minutes long. I have 5,000 records which will probably grow to 30,000 or more over course of a year. Is this because the calculations are not stored in database? How should I speed this up so one doesn't have to go grab lunch while it calculates?
Here's example of what I'm referring to in terms of 3 fields that build my quarterly fiscal cross tab report:
Unstored calculation Field: NameCount that is the ExecuteSQL script (see above)
Calculation Field: zName_July_Hours that is: Case(z_ServiceDateMonth =7; NameCount)
Summary Field: zName_July_Total that is: Total Sum of (zName_July_Hours)
The last one seems to have hour glass and just run and run and run with a screen saying Summarizing Field: zName_July_Total
Thanks for the awesome support!
There are other approaches to get your cross tab report, multiple portals--often one row and filtered for each column is a commonly used method.
Some of them may have a lighter computational load for your report.
Another approach is to compute as many summary totals "in advance" as possible. I have a system here that summarizes data from the line items of Purchase Orders (we can get up to a 1000 in one day) where we need reports with monthly totals and averages for each year over a 5 year span. That means computing totals and averages of data from multiple millions of line items.
I get the results that I need by using a script to collect data in a summary table each night after close of business. It creates one record for each day for each material purchased that day with the total weight, total cost, etc stored in number fields. this "condenses" what could be over 4000 records into about a dozen records for that day--greatly reducing the amount of computation needed to produce reports based on this data.
Of course, this is not a method that can be used in every database.