Easier CrossTab Reports

[The Shadow]

This file demonstrates a new technique for generating a cross-tab report.

There is a second table in the file called "CrossReport" whose sole purpose is to be a cross-tab summary of the data table.

The script regenerates the CrossReport table when run, with the assistance of a multi-predicate join to decide what bucket of the report to total onto.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[Tripod]

This is hard to do in FMP. I do a lot of survey research, and I often use FMP for data entry and reporting of open-ended (long text) responses. I always export any fixed (numeric) data to SPSS and run crosstabs and frequencies from there. I would love to be able to do simple 2 way crosstabs (not statistics other than percent or N) in FMP.

I see from your example that I have a lot to learn about FMP7. At this point, I can't even manage to get the crosstab and crossreport to open in a separate window, and I do not understand the join at all. Going back to the documentation and see if I can figure this out.

I guess you are mostly demonstrating the join here? The report is not really a crosstab, more like a list as region is repeated in the report and should show one line for each region. I am probably doing something wrong, but I expect to see a crosstab (2 way) something like:

Region

North East South West TOTAL

Male 22 34 11 10 77

SEX

Female 20 24 19 20 83

Total 42 58 30 30 160

And, when you add in other variables, like the average money they spent or the

high and low values, that really won't fit as it's continuous data anyway. You can

calculate it, but you can't normally produce a table like that in SPSS or SAS or any

other real statistical software. But you could in FMP, of course, by just showing

that stat for that cell. To me though, you are mixing crosstabs with means or

breakdowns (where you would normally show summary statistics for continuous

variables by discreet groups of cases. e.g. Average income by race sex.

It would be nice to do 3 way crosstabs with another discreet variable like race, etc...

and generate layered tables, one for each race, or sex or region (depending on what

order they are specified in.)

Thanks,

--Tripod

[The Shadow]

Yes, the intent was to show how the join could be used to simplify the script needed to create the report. It was quickly pointed out to me there were some bugs that I didn't notice in my rigorous testing...

Sorry.

[Tripod]

Don't be sorry, I find this very interesting. I could not find info on a multi predicate join though. Is that the same as a multi criteria relationship (join)? IOW, the relationship is based on more than one field matching. Sometimes a fairly simple concept, like crosstabs, can be very hard to implement.

Basically though, it's a tally between two or more categories, showing the number or percent (usually that is all) in each cell (or percent of row or column) of a matrix.

Now you have got me wanting to get this figured out...

--Tripod

[The Shadow]

Yes, its the same, I'm slipping into SQL terminology, they say "where predicate", "join predicate", etc. A predicate is just a test expression that returns True/False (or Null!

[The Shadow]

I've got another attempt now - just a 2-way cross join for starters.

This one has a decent reporting layout like Tripod describes above.

The example can be downloaded from:

http://www.spf-15.com/fmExamples/

At the moment, the report layout ("Grid") is rather a lot of work to construct - I'm hoping that could be made more automatic with some clever scripting or subsummary parts.

[The Shadow]

I added a further improvement.

With a single 9x9 "Grid" layout for display, this example shows how to setup two crosstab reports on different data tables.

Each new crosstab would require a new (easy) script, and a new table occurence in the graph to relate the data table to.

[Tripod]

I downloaded this. It is pretty cool, but I am not sure I understand it. Particularly the

relationships graph! Are you using all those relationships?

I'll try and take it apart and see if I can make sense of it. When I get to that point, I'll

post again. I think this is a great example, but I'm not sure how it all works yet.

If you wanted to post a summary of how it works, and why, I would be interested...

--Tripod

[The Shadow]

Tripod,

The relationships aren't really as bad as it looks - there's just a lot of repetition there.

The scripts work by combining (totaling, in this example) from the data tables of interest (CrossTab or Data in my example) into the CrossReport table.

The Grid table, with a single record, is used for the display. Each of the Column1, Column2, Column3, etc. tables are another version of the CrossReport, and they exist to allow the column and row sums to be computed for display with a single summary field in CrossReport.

Each of the portals on the Grid layout is based on one of the ColumnN versions, and since all "buckets" were created by the script "Reset Report", the empty records cause everything to line up properly with the headers.

Another reduction I'm taking advantage of is that for a case like this, the headers for the Grid layout are actually the values I need to join back to the CrossReport table with to find the correct set for each column/row.

The break-through for me was that the Grid does not need to be recreated for each table you want a report on - the same one can work for any case, only a new script to push the data into the CrossReport table is required.

[The Shadow]

I have created a new variant that uses the same technique but creates a 3-way cross tab report.

In my new example, the "Grid" now creates 4 records, one for each region, each record is a cross between race and gender.

I also spent a little more time making the relationship graph pretty, so hopefully its a bit more clear as a result.

You can download it from my website:

http://www.spf-15.com/fmExamples/

[Boo]

Can I get a similar cross tab report with FM5?

Boo.

[The Shadow]

I don't really see why not, its mostly just relationships. You will have to build concatentated keys to do the multi-criteria joins.

I suspect FM5 examples already exist if you would search around a little, this topic was more about exploring how FM7 could make them easier.

[000levy]

I am a real newbie and I see from your example that I have a lot to learn about Going back to the documentation and see if I can figure this out.