Subtitles of the Movie

A cross tab report is one that looks more like a spreadsheet than your traditional FileMaker Sub-summary Report. For instance, let's take a look at one of the layouts we've already created for our Sub-summary Report. We'll go down here and take a look at our report by category and it's a subsummary by category, so we can go to Preview Mode and then sort records by our category field. It gives us a vertical representation. Here's the subsummary organization and here's all the detail below it. What I want to have is all the detail to go this way, and that is often a very valuable technique, especially when you're doing reports by product and by month, which is what we're going to do. We want to have all the products listed down here, all the months listed here, and everything filled in between. So let's take a look at what we have. We'll go into Layout Mode again and we'll come back over to our Customers form, go to Browse Mode. We already have this program, so let's try it out. Scroll down here, cross tab report, click on it, and you can see it gives us a report. Now, there's no products sold in these months. That's why there's nothing there, but you can see September is filled up. October is a little empty and November is filled up as well, and then you have your totals here. The difference is we've made it go this way, rather than having espresso with January, February, March, and so on, below it, and then Columbian with the same things below it, and so on. It takes up a lot less space, but it's also a lot more work. FileMaker is not set up to work this way, so you have to understand how FileMaker works and program around that. So let's take a look at what we have. The first thing we're going to do is take a look at that layout. We'll go down to our cross tab layout and you can see we have a header, a subsummary, when sorted by KF Product ID, so that sorts and organizes all of your products together, but we do it by the product ID and just make sure all the records are in our lines because if you look at Layout Setup, it's based on showing records from lines, so it organizes them by that way and then we have a trailing grand summary that has our totals in it and then a footer. Now, each one of these fields is important to look at. There's month total 1, month total 2. There are 12 fields here and these are the exact same fields down here as well, so let's take a look at those fields. We'll go into Manage Database, scroll all the way to the bottom. You can see month total 12. That is a total of the month 12 field and this is the total of the month 11, so we really have to take a look at those fields. If we look at this one, it says at date we declare that variable equals invoices date post. Then we say grab the month, just the month number, and see if it equals 12. If it does, then give us the extended price. Each one is a little bit different. This one says only grab month 11 prices and so on and so on. Then these each total it up, so you can see how you're going to require a lot of fields to do this total in here by cross tab. It requires one field for every category and then a summary field for every total you want. So once you have that in there, then you simply have to put the summary fields on the layout. Now, we've gone ahead and formatted them, of course, to have fixed number decimal digits, currency, thousand-separated, and all that great stuff. There's also a script that goes along with this. We'll go to the ScriptMaker, scroll all the way to the bottom, and we'll see our cross tab report. First we go to the Invoices Form layout. We enter Find mode and then we do the set field to the date post field. We say set this date, which will actually come out something like this, based on today's date, so it's going to find all the records in 2008 for you. Once it performs a find, then it's going to do a go to related record. It wants to find all of the related items to all those invoices, so we're going to go from invoices to lines for a relationship, find all the line items because we're choosing match all records in current found set, so all the invoices, it finds all those lines, shows only those related records, so we have all of the line items from 2008. Then we need to sort the records for the subsummary by KF Products ID. Then we can enter Preview Mode and pause, enter Browse Mode, and go to Layout, Original Layout. So when we run that whole thing, let's go back into our Customers form, go back into Browse Mode, and we'll turn on the debugger this time and make sure you understand everything. So we'll go ahead and run the cross tab report. OK, it goes through the menu button, which here is the option here that we want. See, Perform Script, Cross Tab Report, so I'm going to hit Play down to that. It's going to select that one. We're going to go into it. Then we're going to run through this. Go to the Invoices layout, so now we're on Invoices. We have nine of nine found here and probably after we do this Find, all of them are going to be found anyhow. Run through that. You can see how we've set that field to that value. Perform the find. It's still nine records because every record we have is in 2008 anyhow. Then we're going to sort the records after we go to related record first. See, we actually found 16 of 18. For some reason, two of the records don't relate to any of the invoices and we'll find out why. We'll have to clean that up. Then we go through that and we sort it. Now they're sorted. Now we can enter Preview Mode. You can see how that, the difference between Browse Mode reporting and Preview Mode reporting is that you can actually slide the objects here and then we pause so we can take a look at the data. Then we go back to Browse Mode and back to the original layout and then the last step there. So that's how you do a cross tab report. You can actually do it with a web viewer, if you want, but it gets extremely complicated. You have to take all your data and put it into a report format or a table format for HTML viewing and the reason it gets so complicated is that doesn't seem complicated, but what happens if you have an empty month, in our case? You could have a lot of empty months. It gets very complicated as far as putting it together and then you have lots of limitations with the web viewer. It has the same limitations as a web browser. You can't print very well because it doesn't page break gracefully. There's all kinds of issues with it, so I generally avoid doing reports inside of Web viewers, although you can do it and you can get some neat-looking results, but I think if you want a cross tab report, this is probably the best way. Even though you have to add all these fields, it's probably the best way to do it.