Hi Mikhail,
I got the first part of your solution working. But I don't see how to get
the "Total of Qty by Type" working. Starting with defining it as 13
repetitions similarly to "Qty by Type" and then converting it to a Summary
field turns off the repetitions and converts it to a single value object.
----- Original Message -----
From: "Mikhail Edoshin" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Sunday, July 22, 2007 11:12 AM
Subject: Re: Cross tabs possible?
Hi Nicholas,
I don't see a "GET()" function in my version of Filemaker which is Pro
5.0
So you have FM5 :) It's possible in FM5 too, but may be somewhat slow.
Create a global number with 13 repetitions. Fill it with numbers from 1
to 13. This will be a substitute for Get ( CalculationRepetitionNumber ).
Let's name it Repetition Number.
Now define an unstored calculation Qty by Type with 13 repetitions like
that:
Case( Extend( ProductCode ) = Repetition Number, Extend( Qty ) )
Now if you place the field on the layout and display all repetitions
horizontally, you'll see that it shows something like that:
ProductCode Qty by Type
[1] [2] [3] [4] [5] ... [13]
1 5
3 2
1 4
13 ... 4
Here 5, 2, 4, and 4 are sample quantities. Now you define a summary field
= Total of Qty by Type and set it to summarize repetitions individually.
It automatically catches it needs to be 13 repetitions long as well.
Place the field in the summary part and it will give you summaries by
column.
ProductCode Qty by Type
[1] [2] [3] [4] [5] ... [13]
1 5
3 2
1 4
13 ... 4
--------------------------------------
Total 9 2 ... 4
It should work correctly with all summary types and with all subsummary
parts, etc. Basically it's same as if you defined 13 individual fields
like
Case( ProductType = 1, Qty )
and then 13 summary fields for them. This method uses less fields and is
more flexible. The disadvantage in FM5 is that it has to use a global
field and this makes the calculation unstored. If this is a problem,
resort to the 13+13 fields solution.
I didn't actually test all this with FM5, sorry :) But it should work,
please tell me if it doesn't.
--
Mikhail Edoshin
Information Analyst
Skeleton Key
[EMAIL PROTECTED]
On Jul 18, 2007, at 6:59 PM, Nicholas Geti wrote:
I checked the article but I still have some questions:
1. The article creates a repeating field based on the statement,
"GET(CalculationRepetitionNumber)" I don't see a "GET()" function in my
version of Filemaker which is Pro 5.0
2. What is the variable, "CalculationRepetitionNumber"? Is this a script
or another field?
3. When the amount is placed in the repeating field, does it get
accumulated (i.e., added) to a running total or does it replace the
previous value?
4. My situation is more complex than simply converting month to an index
number. I have 13 different product types so I created another field
based on a case statement e.g.:
ProductCode=
CASE(Product Name="42399",1, Product Name="52233",2, etc)
This converts the Product Name to a numerical index. How would I use
this field in the definition of the repeating field? Does the following
definition of the repeating field make sense?
ProductRepeatField=
CASE(ProductCode, Qty)
where Qty is the amount field in the current record being read from the
input table.
----- Original Message ----- From: "Mikhail Edoshin" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, July 13, 2007 10:51 AM
Subject: Re: Cross tabs possible?
On Jul 13, 2007, at 6:15 PM, Nicholas Geti wrote:
I need to create what I think can be called a cross-tab report. I
have a file of records; one column is product type. I need to display
a count of each product type as a column in a report. There are
thirteen possible types.
Is there a way to use SQL selects to count the types or do I have to
use brute force and create a table of thirteen columns then scan the
original table?
You can do this using repeating fields. The idea is that you create a
calculated repeating field with 13 repetitions, use the product type
to evaluate the corresponding repetition to 1 or 0 and them summarize
the repetitions individually.
Check the following link for a sample:
http://edoshin.skeletonkey.com/2006/12/crosstab_report.html
Hope this helps,
--
Mikhail Edoshin
Information Analyst
Skeleton Key
[EMAIL PROTECTED]
