Filemaker Pro Lesson

Report Writing:

Creating A Crosstab

This exercise requires the file Registrations3.fp7.

Although multiple subsummary parts can be used to create specific subgroups of information, sometimes you will be asked for a crosstabular report format. Filemaker Pro does not automatically create crosstabular reports but they can be designed with the creation of the appropriate calculated and summary fields.

Suppose you were asked to produce a report that showed how many males and how many females participated in each event. You could produce a standard subsummary report by creating a report with a subsummary part sorted on Event followed by a subsummary part sorted on Sex, then place the "Number of Participants" summary field in each subsummary part to produce a total of participants per event followed by a list of sexes with the number of each sex participating in each event.

The resulting report would look like this:

Bicycling Total Participants 8

Male Total Participants 5
Female Total Participants 3

Kayaking Total Participants 10

Male Total Participants 4
Female Total Participants 6

But the person requesting the report wants a crosstab instead that looks like this:

To produce this layout requires some additional calculated and summary fields.

1. Using the file Registrations3.fp7 select File -> Define ->Database and select the Fields tab.

2. Create a calculated field named Count of Males. In the Specify Calculation dialog box type If(sex="M",1,0) set the result to be a number and click OK. The "If" function uses the argument If(condition,result1,result 2). In other words, if the condition you specify is met, in this case sex="M", then return a 1, otherwise return a 0. It is also important to include the quotation marks around the "M" to let Filemaker know that this is a specified text value not a field name.

3. Create another calculated field named Count of Females. In the Specify Calculation dialog box type If(sex="F",1,0), set the result to be a number and click OK.

4. Now create a summary field named "Summary Count of Males". In the "Options for Summary Field" dialog box select the operation "Total of" and the field "Count of Males" and click OK.

5. Create another summary field named "Summary Count of Females". In the "Options for Summary Field" dialog box select the operation "Total of" and the field "Count of Females" and click OK.

6. Now select View -> Layout Mode. Choose Layouts -> New Layout/Report and select Columnar/List Report and click Next.

7. Choose Report with Grouped Data and click Next. Double click the "Event" field then the "Summary Count of Males" field, then the "Summary Count of Females" field and click Next. Double click the "Event" field again and click Next.

8. We will sort by the "Event" field so just click Next again. Choose the Default layout theme and click Next.

9. We won't bother with headers or footers, just click Next. We won't need a script so just click Next again.

10. Select "View in Layout Mode" and click Finish.

11. Drag the field "Summary Count of Males" into the Subsummary part by Event and move it over to the right of the field "Event".

12. Drag the field "Summary Count of Females" into the Subsummary part by Event and move it over to the right of the field "Summary Count of Males". Rename the field labels to just Male and Female and reposition them over their appropriate fields.

13. Click on the tab for the Body part and press the Delete key. Select View -> Preview Mode to view the result.

If you have an RSS feed that would be of interest
to faculty and students at the College of Education
please contact Mary Harrsch.

Last updated: 05/19/2005