I have a client that wants an excel-style report composed of data from
a single table. In this case, the records contain "city" data and
creation dates, and they want a report that will have cities
populating the 'rows', and records-per-month populating the columns
(columns would be Jan, Feb, March, etc.). So... San Francisco: 4
records in Jan, 6 records in Feb, 1 record in Mar etc.

My plan was to base the layout on the primary table (with a sub-
summary part sorted by city), but I'm stymied by how to get the
records-per-month data into the vertical columns in any kind of an
elegant manner (without too many subsidiary tables, added fields,
etc.).

I'm sure this is a common challenge, and that there's a 'best-
practice' way of achieving it. My hope is that some kind expert out
there will see this and enlighten me!!

The client is using FM9, with FM 9 Server (all windows xp).

Many thanks in advance.

On 2011-12-07 14:59:43 -0800, lansingoogle <ls (AT) jillo (DOT) com> said:

I don't think you can use Virtual Lists in FM9, as the GetValue
function didn't exist then. Do you think you can get them to upgrade,
at least the client machines if not the server? (if my recall is
faulty and GetValue exists, go for it).

Virtual lists make such a report relatively easy. You make records in a
reporting table, each one with a serial number. A field for each column
reads an indexed value from a variable with a calc such as
GetValue($$column1array, Serial).

Then you build the variables by looping through the records and setting
the values you want into the lines of the variables. So if your column
1 is city names, then you'd loop through the records and the variable
looks like this:

Chicago
Detroit
Los Angeles
Phoenix

So the calc field in column1, record #2 reads "Detroit."

Then you repeat this for each monthly value (column) you want. Column2,
March 2011, for example might read:

134
148

789

Am I making any sense? Virtual lists are a very powerful tool. I've
done the above kind of cross-tab report getting summary data by doing
finds using the values in the first variable, then setting the found
set totals into the other variables as needed. Think of each variable
as a column in an array.

Because all this is taking place in the memory space of the file, it
goes really really fast. UNLESS you have very large data sets. At more
than 2000 records, creating the arrays by setting them to themselves
plus the new line of data starts to get slow.

If you need subsummaries in the reporting table, create them there, to
total the columns. They'll be fast too.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

lansingoogle <ls (AT) jillo (DOT) com> wrote:

Create global fields for the year and the month. Build a reation to a
second table occurence where the city matches, and the global fields for
year and month on the left side with the data fields for year and month
with the right side.

You can now create a calc field to get the month's total for the city
with the sum function.
--
http://clk.ch

On Dec 8, 1:32*am, clkaufm... (AT) gmx (DOT) ch (Christoph Kaufmann) wrote:
Thank you Christopher. At the moment I'm caught up in the strange
world of Virtual Tables that Lynn alerted me to. Your approach is
probably closer to what I would naturally gravitate towards - though
I'm not certain I'm smart enough to fill in the unspoken assumptions.
But I'll give it a whirl...

THANKS!!!

On Dec 7, 6:49*pm, Lynn Allen <l... (AT) NOT-semiotics (DOT) com> wrote:

Thanks, Lynn - you introduced me to a whole world of possibilities
here. GetValue is indeed included in v.9, and I'm busily looking into
all the information I can find on Virtual Lists. As a self-taught,
part-time FM developer, who doesn't participate in the big scary world
of major FM development, I was totally unaware of this technique, and
I've spent the last day trying to wrap my head around the
implications. Actually, it captures the essence of why I enjoy
Filemaker, and the community it engenders: flexibility, innovation,
and a creativity that continually amazes me (not to mention a
generous, unselfish willingness on the part of the experts to share
their knowledge).

Again, Thanks.