VIEW MORE SCREENCASTS   |   MISSINGMANUALS.COM HOME PAGE

	Click here to preview and order the book.

TRANSCRIPT

Hi, I'm Geoff Coffey, co-author of FileMaker Pro 9: The Missing Manual and in this screen cast we're going to talk about subsummary reporting in FileMaker Pro. We're going to cover subsummary parts and summary fields and how they work together.

This is the people database. We're going to use this database to demonstrate the power of subsummary reporting. In order to use subsummaries we need to have a new layout, a layout with subsummary parts on it.

So the first thing I'm gonna do is create that layout. I'll go to the View menu and switch to layout mode and then I'll go to the layout menu and choose New Layout/Report. You can name this layout anything you want and down here from the Select a Layout Type list, choose Blank Layout because we're gonna build this layout from scratch. Then click Finish and now we have a new and completely empty layout.

Now of course you can do anything you want with this layout like add headers, titles, page numbers and things like that. We're going to ignore all of that for now and focus just on the subsummary side of things.

Most subsummary reports are a list of data at their heart. So the first thing I'm going to do is add some fields for my list. I'll just drag the field object into the body part and I'll add the first name, the last name and the email address fields. I'm gonna put these right up in the corner of my body part as close to the edge as I can and then shrink my body part way up so that it just holds those fields.

If I save this layout and switch to browse mode I see just one record. I want to see a list of records so I'll say View As List. Now that's not strictly necessary because subsummary reports are always used in preview mode or it always uses a list. But just for the sake of demonstration let's make sure we do that.

Now I can see a list of all of the records in my database. Now I'm gonna switch back to layout mode and now I'm ready to add a subsummary part. I just go to the Insert menu and choose Part and turn on the "subsummary when sorted by" radio button.

Now every subsummary part is tied to a particular field and it only works when you sort your records by that field. In this case what I want to do is I want to group the people in my database by city and by state. I'm gonna start with state so I'll choose the state field. As you can see I've selected State in this list.

Then I'll push "Okay." FileMaker asks me if I want this subsummary part to go above the records or below the records. You can have a subsummary part in either place or you can have one in both places. In this case I want them above so I'm going to say, "Print above." Now it's adding this subsummary part to my layout.

Since this subsummary part is based on the State field I'm going to add the State field to it. That's pretty typically what you do. So I'll drag the field object up and choose the state field and push "Okay." Now I'll just move this into position way over here on the side of my part.

Now you can see that the part label is getting in the way of my data which is a common problem, subsummary parts have long labels. So I'm gonna use the part label title way down here in the bottom of the window right by the mode popup. When I click that my part labels all flip the other direction. I can't see their full names but at least they're not in the way of my layout.

The last thing I'm gonna do, and this just makes things look a little nicer, is I'm gonna take all the fields in my body part and move them over to the right a few notches so that they're sort of indented with respect to the State field above them. Now I save my layout and switch to browse mode and nothing. And that's because subsummary parts don't work in browse mode. Instead we need to use preview mode.

So, I'll switch to preview mode and now we see Arizona and then all the people in Arizona. We see California and then all the people in California. So what's happening is all my records are sorted by state and FileMaker is inserting the subsummary part one time for each grouping. Before all the people from that state it adds that subsummary part. Anything I put there will show just once before every state group.

Now this is only working because I had already sorted my records by state. If I were to go choose the Unsort command you would see that my subsummary part completely disappears. I have to sort my records by state for this to work.

Now you could actually have more than one subsummary part on your layout at any given moment if you want. I'll switch back to layout mode, choose Insert Part again, choose Subsummary again and this time select the City field and push "Okay." Again, I want to print above and FileMaker adds another subsummary part below the first one but above my body.

I'm gonna hold down the Option key, well, that's the Control key on Windows, and drag straight down to create a copy of that State field and this time I'm gonna choose the city field. I'll indent it from the State field and then again, I'll indent my body fields even more. Now I'll save this layout and view in preview mode.

Now at first it looks like it didn't work and that's because my records aren't sorted by the City field. So I'll go to the Records menu, choose Sort Records and add the City field to my sort order.

I still want to sort by state because that's one of my subsummary parts. I want to sort by state and then city because I want to group my records by state and then city. I'll push, "Sort" and now you see a state name and then various city names and under each city is a list of the people that live in that city.

Now this alone is interesting because I've created these useful groupings that look nice on my report but you can do a lot more than this with subsummary parts when you start using summary fields.

Let me show you an example. I'm gonna go to the File menu, choose Manage and then Database. Make sure I'm on the Fields tab and I'm gonna add a new field. I'm gonna call this field Count of People. For the type over here I'm going to choose summary because this is a summary field and then push create. When I do that FileMaker brings up this box that asks me what kind of summary field I want to make. In this case I want to click the Count Of radio button.

And then FileMaker says, "What do you want to count?" This is a little confusing but I'm going to choose the first name field. What the Count Of subsummary field does is it counts all the records in that particular grouping or in that entire database and it counts a text field like this. If the field is empty it doesn't count the record at all and if the field has anything in it then it does count the record.

Since our database has something in every first name field on every record choosing the first name field will tell it to count every record. You can use any field you want as long as you're positive that field won't be empty. I'll push "Okay" and push "Okay" again.

Now FileMaker adds that count field to my layout and I can see that it says 126 in every case which is just how many records are in my table. I don't want it in this part though. I'll show you what happens when I move that field around. I'll switch to layout mode, I'll select a label and the summary field and I'll move them into my State subsummary part.

I'm also gonna make the body part smaller again. FileMaker grew it in size when it added that field to the body. Now I'll switch to preview mode, save my layout, and now you can see that FileMaker's telling me I have 29 records, not in my entire table but just in the state of Arizona.

If I scroll all the way to California I can see that this now says 77 people are in California. So the summary field shows the values of summarizing just the records within that subsummary group.

Let me switch back to layout mode. To make this a little more clear I'm gonna duplicate those fields using Control D or Command D and drag it down into the City subsummary part. Now this is the same exact field. Here's it's in the state subsummary part. Here's an exact copy of it in the City subsummary part.

I'll save that and switch to preview mode and you can see now that I have 29 people in Arizona including one in Flagstaff and eighteen in Phoenix and so forth. So the subsummary parts group my records by city and state and the summary fields count my records by city and by state.

Now you can have other types of summary fields as well. Let me go back and add another one. I'll say Manage Database again and I'll add another field this one called Average Goodness.

Again I want a summary field, click Create and I'll choose the Average Of radio button. There are several different summary types you can experiment with here or read about them in the book. This time because I picked Average it insists that I choose one of my number or time or time stamp fields.

I'm gonna choose the Goodness rating field and push "Okay," push "Okay" again. Again, I need to go fix my layout. I don't want that in the body. I want to put that up here in the summary part and I also want to put it in the other summary part. I'll make my body small again, save and switch to preview mode.

And now you can see that FileMaker's calculating the Average Goodness rating of everyone in the state of Arizona and here it's everyone in the city of Phoenix.