Re: One last time... Sub summaries, variable lists, and whatnot...
- From: Helpful Harry <helpful_harry@xxxxxxxxxxxxxxxx>
- Date: Fri, 24 Aug 2007 18:13:06 +1200
In article <1187901641.545349.77660@xxxxxxxxxxxxxxxxxxxxxxxxxxx>,
"Teresa K." <teresa.kabourek@xxxxxxx> wrote:
I am still having issues with this problem.. Many of you all have
helped me so much, and what I've learned I've been able to apply
elsewhere, but this is a true monkey on my back. Please help, or ask
for clarification if somethings doesn't make sense.
Using Filemaker v6.04
Part setup (top to bottom)
--> Leading Grand Summary
--> Sub-summary b Project Name (leading)
--> Sub-summary by SiteID (leading)
--> Body
--> Sub-summary by Total # of calls (trailing)
--> Sub-summary by total minutes
--> Sub-summary by PROJ_SITE******* <<-this is the one I think is the
troublemaker
--> Sub-summary by Project Name (Trailing)
Image of Report (as best as I can do it in ascii) when completed
======================================================
Monthly Site Report
------------------------------
PROJECT NAME: MICKEY
SITEID: ABC
Control # Site ID Date Proj Name Total calls
Total Minutes
-------------- -------------- --------------
-------------- -------------- --------------
12345 ABC 08-13-07 MICKEY 1
00:12
23456 ABC 08-13-07 MICKEY 2
00:20
SITE: DEF
Control # Site ID Date Proj Name Total calls
Total Minutes
-------------- -------------- --------------
-------------- -------------- --------------
34567 DEF 08-01-07 MICKEY 3
00:20
SITE: GHI
Control # Site ID Date Proj Name Total calls
Total Minutes
-------------- -------------- --------------
-------------- -------------- --------------
34567 GHI 08-06-07 MICKEY
6 01:05
-------------------------------------------------------------------------------
---------------------------
Total Control #: 6
Total # of Sites: ???
Total # of Calls: 16
Total # of Time: 02:15
======================================================
Now, also on the report are other project summaries. What our people
are tracking is the number of calls related to a particular Control #,
how long the sum of the calls are, and of course, what sites are
calling in for help on any particular project.
All of the sub-summaries are set up so that at the end of every
Project Grouping, I also get a summary of Total # of Control # log
entries made, Total # of calls made on this project, Total number of
Minutes of assistance given on this project, and total # of Sites
calling in.
My issue is that no matter what I do, I cannot make the Total # of
Sites calculation come up correct. Everything else calculates fine
and gives me summaries for all the projects as expected.
If anyone thinks they can be of assistance on this problem, I'd be
happy to send them a clone with sample information in it, or screen
shots of how I have my layout made out.
I know I probably need to use variable lists, but I just cannot get my
head wrapped around how to calculate totals using them. I feel so
completely out of my league on this problem. Help!
- Teresa K.
"Variable lists"??? I'm not quite sure what those are, but you don't
need them anyway.
I'm not sure why you're repeating the Site ID and Project Name on every
line of the report (seems a bit of a waste of space), and you're
totalling mathematics is off in your example, but the real problem here
is that you appear to have far too many Sub-summary Parts.
You don't need a separate sub-summary for each of the totals - since
you're trying to total records for each Project you really only need
one Sub-summary Part, summarising by Project ... BUT, the hiccup is the
"Total # of Sites", which I'll get back to later.
The Layout for your example report should probably be something along
the lines of ...
Project Name: [ProjectName]
| Sub-summary by ProjectName (Leading)
---------------------
Site ID: [SiteID]
Control # Site ID Date Proj Name Total Calls
Total Minutes
| Sub-summary by SiteID (Leading)
---------------------
[Control] [SiteID] [Date] [ProjectName] [Calls] [Minutes]
| Body
---------------------
Total Control #: [s_TotalControl]
Total # of Sites: [s_TotalSites]
Total # of Calls: [s_TotalCalls]
Total Time: [s_TotalTime]
| Sub-summary by ProjectName (Trailing)
---------------------
where the [] denote the fields.
Three of these summaries are very easy to define since they're totalled
over all the records for a Project and so can use normal Summary
fields.
ie.
s_TotalControl Summary (Count of Control)
s_TotalCalls Summary (Total of Calls)
s_TotalTime Summary (Total of Minutes)
Now we get back to the problem of the "Total # of Sites". This field is
not a simple summary of all records since each Site can have multiple
Control records - Summary Fields can only summarise EVERY record, not
groupings of records. The easiest way to get the number you want is to
use a little mathematical trickery and give each record a value of
"1/X", where X is the number of records per site within the Found Set.
To get the value for X you first need create to a new Summary Field
that counts the number of Sites.
eg.
s_CountSites Summary (Count of SiteID)
Next create a new Calculation field (unstored) that uses the GetSummary
function.
eg.
c_OneXth Calculation, Unstored, Number result
= 1 / GetSummary(s_CountSites, SiteID)
By using SiteID as the second parameter for the GetSummary function
we're telling FileMaker to only count the records that have the same
SiteID value.
eg. For your example report,
- each ABC record is given the value 1/2 since
there are 2 ABC records
- each DEF record is given the value 1/1 since
there is 1 DEF record
- each GHI record is given the value 1/1 since
there is 1 GHI record
This means adding together all the "1/X" values for each SiteID gives a
total of 1 (ie. it counts that SiteID once no matter how many records
it is in).
eg. For SiteID "ABC" you can add 1/2 + 1/2 = 1.
Since you're using the s_CountSites Summary field, this "1/X" value
will only reflect the records in the Found Set. If we had used a
Relationship link you would have counted ALL records in the table with
the same SiteID (or at least you would unless you do a lot of extra
work).
Now that we have the "1/X" value in each record, we can just Total (not
Count) this field to obtain the number of Sites using a normal Summary
field.
eg.
s_TotalSites Summary (Total of c_OneXth)
For your eample report this field will total as 1/2 + 1/2 + 1/1 + 1/1 =
3.
All done!
The only real problem here is that the GetSummary function can be slow
when summarising LOTS of records, but then so is using Summary Parts
and fields.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
.
- Follow-Ups:
- References:
- One last time... Sub summaries, variable lists, and whatnot...
- From: Teresa K.
- One last time... Sub summaries, variable lists, and whatnot...
- Prev by Date: Re: How to sift through spam on FMPro Gruop
- Next by Date: Re: development of file which log all the debug script
- Previous by thread: Re: One last time... Sub summaries, variable lists, and whatnot...
- Next by thread: Re: One last time... Sub summaries, variable lists, and whatnot...
- Index(es):
Relevant Pages
|
