The Shadow Posted April 18, 2004 Share Posted April 18, 2004 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 Link to comment Share on other sites More sharing options...
Tripod Posted May 14, 2004 Share Posted May 14, 2004 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 Link to comment Share on other sites More sharing options...
The Shadow Posted May 15, 2004 Author Share Posted May 15, 2004 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. Link to comment Share on other sites More sharing options...
Tripod Posted May 18, 2004 Share Posted May 18, 2004 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 Link to comment Share on other sites More sharing options...
The Shadow Posted May 18, 2004 Author Share Posted May 18, 2004 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! Link to comment Share on other sites More sharing options...
The Shadow Posted June 22, 2004 Author Share Posted June 22, 2004 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. Link to comment Share on other sites More sharing options...
The Shadow Posted June 23, 2004 Author Share Posted June 23, 2004 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. Link to comment Share on other sites More sharing options...
Tripod Posted June 25, 2004 Share Posted June 25, 2004 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 Link to comment Share on other sites More sharing options...
The Shadow Posted June 25, 2004 Author Share Posted June 25, 2004 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. Link to comment Share on other sites More sharing options...
The Shadow Posted July 1, 2004 Author Share Posted July 1, 2004 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/ Link to comment Share on other sites More sharing options...
Newbies Boo Posted July 13, 2004 Newbies Share Posted July 13, 2004 Can I get a similar cross tab report with FM5? Boo. Link to comment Share on other sites More sharing options...
The Shadow Posted July 14, 2004 Author Share Posted July 14, 2004 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. Link to comment Share on other sites More sharing options...
Newbies 000levy Posted July 15, 2004 Newbies Share Posted July 15, 2004 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. Link to comment Share on other sites More sharing options...
Recommended Posts