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