Hi,
I am an FM newbie trying to export summarized data from an FM file.
I can see what I want in a Preview layout but what I want is the same
sort of data but exported out into an Excel file.

(Sorry about my earlier posting.
In the rather laborious example, all the figures were wrong.)

Here is the detailed data as stored in my file
You'll maybe need to reformat this into a fixed font to get it lined up
correctly.

sale date region town price
20/12/2004 North Manchester  66.00 ?
22/12/2004 North Manchester  99.00 ?
24/12/2004 North Manchester  95.00 ?
26/12/2004 North York  76.00 ?
28/12/2004 South London  93.00 ?
30/12/2004 South Bristol  84.00 ?
01/01/2005 South London  92.00 ?
03/01/2005 North Manchester  64.00 ?
05/01/2005 South London  74.00 ?
07/01/2005 South London  89.00 ?
09/01/2005 South Dover  84.00 ?
11/01/2005 North Leeds  49.00 ?
13/01/2005 South London  55.00 ?
15/01/2005 South London  50.00 ?


The object is to export summarized data into another file monthly.
The 'price' field is to be summarized on a breaker field composed of
'Year-and-month' & 'Region' & 'Town'
So I have added a composite 'breaker' field to do that.
So, here's what I have now :

sale date region town price breaker
20/12/2004 North Manchester  66.00 ?  2004/12 - North - Manchester
22/12/2004 North Manchester  99.00 ?  2004/12 - North - Manchester
24/12/2004 North Manchester  95.00 ?  2004/12 - North - Manchester
26/12/2004 North York  76.00 ?  2004/12 - North - York
28/12/2004 South London  93.00 ?  2004/12 - South - London
30/12/2004 South Bristol  84.00 ?  2004/12 - South - Bristol
01/01/2005 South London  92.00 ?  2005/01 - South - London
03/01/2005 North Manchester  64.00 ?  2005/01 - North - Manchester
05/01/2005 South London  74.00 ?  2005/01 - South - London
07/01/2005 South London  89.00 ?  2005/01 - South - London
09/01/2005 South Dover  84.00 ?  2005/01 - South - Dover
11/01/2005 North Leeds  49.00 ?  2005/01 - North - Leeds
13/01/2005 South London  55.00 ?  2005/01 - South - London
15/01/2005 South London  50.00 ?  2005/01 - South - London


Here now is the detail and the summarized data together in one report.


sale date region town price breaker
20/12/2004 North Manchester  66.00 ?  2004/12 - North - Manchester
22/12/2004 North Manchester  99.00 ?  2004/12 - North - Manchester
24/12/2004 North Manchester  95.00 ?  2004/12 - North - Manchester
    260.00 ?  Total 2004/12 - North - Manchester
26/12/2004 North York  76.00 ?  2004/12 - North - York
    76.00 ?  Total 2004/12 - North - York
28/12/2004 South London  93.00 ?  2004/12 - South - London
    93.00 ?  Total 2004/12 - South - London
30/12/2004 South Bristol  84.00 ?  2004/12 - South - Bristol
    84.00 ?  Total 2004/12 - South - Bristol
01/01/2005 South London  92.00 ?  2005/01 - South - London
    92.00 ?  Total 2005/01 - South - London
03/01/2005 North Manchester  64.00 ?  2005/01 - North - Manchester
    64.00 ?  Total 2005/01 - North - Manchester
05/01/2005 South London  74.00 ?  2005/01 - South - London
07/01/2005 South London  89.00 ?  2005/01 - South - London
    163.00 ?  Total 2005/01 - South - London
09/01/2005 South Dover  84.00 ?  2005/01 - South - Dover
    84.00 ?  Total 2005/01 - South - Dover
11/01/2005 North Leeds  49.00 ?  2005/01 - North - Leeds
    49.00 ?  Total 2005/01 - North - Leeds
13/01/2005 South London  55.00 ?  2005/01 - South - London
15/01/2005 South London  50.00 ?  2005/01 - South - London
    105.00 ?  Total 2005/01 - South - London
    1 070.00 ?  Total

Things are looking OK here, I have the details and the subtotals as I want
them.
But my aim is not to print this data but to make it available in MS Excel.
And .... I just want to export the subtotal lines just as they are here :



breaker     price
Total 2004/12 - North - Manchester    260.00 ?
Total 2004/12 - North - York    76.00 ?
Total 2004/12 - South - London    93.00 ?
Total 2004/12 - South - Bristol    84.00 ?
Total 2005/01 - South - London    92.00 ?
Total 2005/01 - North - Manchester    64.00 ?
Total 2005/01 - South - London    163.00 ?
Total 2005/01 - South - Dover    84.00 ?
Total 2005/01 - North - Leeds    49.00 ?
Total 2005/01 - South - London    105.00 ?


It's seems so simple (and basic and straightforward and necessary) I can't
imagine that it is not possible in FM.
I can do it in Excel and on old-fashioned databases from the last century, I
can even manage it in FM but only in Preview Mode.
I can't get it right as an export.
And ... it's driving me nuts.

So I'm looking for a HowTo, guidance, advice, etc.

TIA,

Maude. 

I don't think you'll get the export you're looking for.
Subsummaries/Summary fields don't export, as far as I know.  But I can
think of two workarounds, one long and complicated, the other shorter
and easier.

1) What you might be able to do is create a self-join for each location
and create a calc field that will Total through that self join.  But
I'm a little fuzzy on it and it's going to be very clunky.

Let's see if I can get you halfway there.

You've got a Location field right?  (I'm ignoring the region field,
which I think may be superfluous for this solution).  Create a calc
field called calcManchester = "Manchester".  Relate that field to
Location and call it LocationManchester.  Create a calc field named
calcManchesterPriceTotal = Sum(Price) _within the context of the
Relationship LocationManchester_. Repeat for each location.  That
should get you the same number in the relevant calc fields that you
would get from a Subsummary.

Unfortunately, that's only half your battle, as it ignores the date and
summarizes all the Location occurances in the db.

You could instead modify your calcLocation fields to be
calcLocationMonth fields instead something like calcManchesterMonth =
"Manchester" & gcalcMonth  where gcalcMonth is a global calc field
defined as GetMonth(gMonth) and gMonth is a global Date field that a
user enters a date in the month you're looking for.

So you can export one month's worth for all your locations after a
complex and unsatishfying amount of work.

Or....


2) Export the fields as they are and summarize them in Excel.

-G

I don't think you'll get the export you're looking for.
Subsummaries/Summary fields don't export, as far as I know.  But I can
think of two workarounds, one long and complicated, the other shorter
and easier.

1) What you might be able to do is create a self-join for each location
and create a calc field that will Total through that self join.  But
I'm a little fuzzy on it and it's going to be very clunky.

Let's see if I can get you halfway there.

You've got a Location field right?  (I'm ignoring the region field,
which I think may be superfluous for this solution).  Create a calc
field called calcManchester = "Manchester".  Relate that field to
Location and call it LocationManchester.  Create a calc field named
calcManchesterPriceTotal = Sum(Price) _within the context of the
Relationship LocationManchester_. Repeat for each location.  That
should get you the same number in the relevant calc fields that you
would get from a Subsummary.

Unfortunately, that's only half your battle, as it ignores the date and
summarizes all the Location occurances in the db.

You could instead modify your calcLocation fields to be
calcLocationMonth fields instead something like calcManchesterMonth =
"Manchester" & gcalcMonth  where gcalcMonth is a global calc field
defined as GetMonth(gMonth) and gMonth is a global Date field that a
user enters a date in the month you're looking for.

So you can export one month's worth for all your locations after a
complex and unsatishfying amount of work.

Or....


2) Export the fields as they are and summarize them in Excel.

-G