Summarizing according to multiple criteria

[KenP]

I'm in need of creating a layout which would contain summaries for each combination of values in several fields. i.e. field1 could be A, B, or C. field2 could be P, Q, or R. Field3 can be X, Y, and Z. I'd like to create a report where for each value in Field1, it displays a chart of field2 values against field3 values...

Maybe I'm making it too confusing...

The nuts and bolts of it is that I want to create a formula which would do the following:

foreach (value in field1)

count all records for which field2 == [P] AND field3 == [Y]

(P + Y being examples)

I can currently do this by making subsummaries of subsummaries, etc., but the report comes out to be 100 pages long. Is there any way to create a hybrid where I could have the body be a chart, but under a sub-summary?

I've tried GetSummary(), but I don't think it buys be anything. I've looked into Aggregate functions, but I can't specify qualifications on the Count() function...

Any help would be appreciated.

- K

[LiveOak]

You can use aggregate functions, but you will need to create fields for each combination:

Test1 (calculation, number) = (field2 = "P" and field3 = "Y") <-- or whatever conditions or combinations you need to add up.

A constant field:

Constant (calculation, number, indexed) = 1

A self relationship:

"Rel" with Constant <--> Constant

TotlTest1 (calculation, number) = Sum(Rel::Test1)

This will sum all the records in the file regardless of the found set. If you need to limit the records totaled, you must do so by modifying the self relationship.

OBTW, I think surveys are one of the hardest things to do well in FM. Purpose built survey programs are better at this.

-bd