Re: Report summary with multiple totals

In article <1149860828.860151.91270@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
"Carla" <carla_sloan@xxxxxxxxxxx> wrote:

This must be simple, but I have a mental block about it.

In a file of invoices, I need a report which lists all the invoices for
each customer. So I've got a summary field which counts invoice_id,
and a sub-summary for each customer. I then need a summary at the end
of the report, with a total no. of invoices in the report (for which I
use the count_invoice_id_summ field). Works fine.

But in the trailing grand summary, I also need the total no. of
CUSTOMERS, e.g.

Cust A
Inv 1
Inv 2
Cust B
Inv 3
Total invoices - 3
Total customers - 2

...and I can't work out how to do it. If I have another summary field,
count_cust_id_summ, it obviously comes up with the same total as the
invoice count.

Any help much appreciated!

The first one, Total Invoices, is easy and you seem to already have it
working. You can count the number of invoices with a simple Summary
field that counts any field that always has data in it.
eg.
s_NumInvoices Count(InvCode)

This then goes in a Sub-summary or Grand Summary part of the layout


The second one, Total Customers, is a bit more difficult to do, but not
hugely difficult and is probably a lot more difficult to explain than
it is to set-up ... and it's definitely a lot easier to use. Luckily
you're already part-way there with your Totial Invoices Summary field.

The problem is that Summary field functions always work across ALL the
records in the table / found set / sorted sub-set. They can't
individualise a sub-set of records as being one entity.

The way around this problem is via a bit of mathematical trickery using
fractions. If you knew how many invoices a customer has then you could
give each of their records a value of 1/x. Summing these fractions
would then give you a total of 1 for each customer, and therefore
summing those in turn would give you the actual number of individual
customers.
ie.
Cust A
Inv 1 1/2
Inv 2 1/2

Cust B
Inv 3 1/1

Cust C
Inv 4 1/3
Inv 5 1/3
Inv 6 1/3

Total Customers = (1/2 + 1/2) + (1/1) + (1/3 + 1/3 + 1/3)
= 1 + 1 + 1
= 3

Now, you may not know it, but you already have the needed field to know
how many records each customer has within the Found Set - the
s_NumInvoices Summary field from above (your field probably has a
different name) used to get the overall "Total Invoices". If you were
to add a Sub-summary Sorted by Cust_ID part to the layout and put this
same field into it you will see that it also sub-totals the number of
records per customer.
ie.
Cust A
Inv 1
Inv 2
s_NumInvoices = 2

Cust B
Inv 3 1/1
s_NumInvoices = 1

Cust C
Inv 4 1/3
Inv 5 1/3
Inv 6 1/3
s_NumInvoices = 3

Total invoices = s_NumInvoices = 6

For various reasons you can't use Summary fields directly in other
calculations, but by using the GetSummary function you can obtain this
sub-total value for each unique Cust_ID (Note: I'm not sure if the
function name has been changed in FileMaker 7 or 8). This means you can
create a new normal Calculation field that gives each record the value
of 1/x that we need.
eg.
NumInvoicesFraction {Calculation, Number Result, Unstored}
= 1 / GetSummary(s_NumInvoices, Cust_ID)

Because you want the fraction to be for each individual customer,
Cust_ID must be the name of the field you're using to uniquely
distinguish between the different customers (eg. the one that lets you
know that a particular invoice belongs to Cust A). This will probably
be the same field you're using to sort the records to obtain the above
report structure. By changing this field to Inv_Date (for example) you
could obtain the number of invoices for each individual Date for a
different report layout that is sorted (or sub-sorted) by the date of
the invoice.

All you now need is a new Summary field to total these fractions and
give you the number of customers.
eg.
s_NumCustomers Total of NumInvoicesFraction

Put this new field in the Grand Summary part of your layout in the
appropriate place and you're done. Find the records, sort them and then
Preview or print this report layout to see the results.

This is the easiest approach, but it's not necessarily the fastest if
you're trying to summarise LOTS of records and / or using a slow
computer.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
.

Relevant Pages

  • Re: Report summary with multiple totals
    ... In a file of invoices, I need a report which lists all the invoices for ... So I've got a summary field which counts invoice_id, ... and a sub-summary for each customer. ... Inv 1 ...
    (comp.databases.filemaker)
  • Re: Report summary with multiple totals
    ... In a file of invoices, I need a report which lists all the invoices for ... So I've got a summary field which counts invoice_id, ... and a sub-summary for each customer. ... Inv 1 ...
    (comp.databases.filemaker)
  • Re: Report summary with multiple totals
    ... In a file of invoices, I need a report which lists all the invoices for ... So I've got a summary field which counts invoice_id, ... and a sub-summary for each customer. ... Inv 1 ...
    (comp.databases.filemaker)
  • Report summary with multiple totals
    ... In a file of invoices, I need a report which lists all the invoices for ... So I've got a summary field which counts invoice_id, ... and a sub-summary for each customer. ... of the report, with a total no. of invoices in the report (for which I ...
    (comp.databases.filemaker)
  • Re: Rate Customers by Invoiced this Year Field
    ... a negative amount in the "Invoiced This Year" field. ... Dim dbAny As DAO.Database ... rank each customer should receive. ... Is it based on a customer's percentage of total company invoices? ...
    (microsoft.public.access.modulesdaovba)