The Right Way and Wrong Way to Create a Crosstab

Crosstab software can be amazing for summarizing information, provided you carefully choose the information to include in your crosstab. Depending on the data you’re seeking, it can be easy to get bogged down by a variety of extraneous information that is not well suited for crosstabs. An example can illustrate what we mean.Information You’re SeekingLet’s say you had a movie rental business and you wanted to reward customers who currently had more than one movie rented at that time. Out of curiosity, and perhaps to offer an additional customer bonus, suppose you also wanted to know what movie titles each customer had along with the date the movies were due. One more tidbit of information you hoped to check out was the area of town where the customers lived.Crosstab software could certainly help you find the information you’re seeking – or you could end up with one big mess.How to End up with a MessThose unfamiliar with the way crosstab software works may eagerly dive right in, attempting to include every bit of information in a single crosstab. This would leave you with entries that included:

• Customer name
• Customer location
• Number of movies currently renting
• Movie titles
• Movie return dates

A misguided crosstab may look something like this:Customer nameLocationNo. of moviesMovie titlesReturn datesJohn DoeEast3Jaws, Aliens, Ben-HurAug. 9 for all threeBob DenverWest1Rescue from Gilligan’s IslandAug. 5Allison PeytonWest2Little Women, Stolen KissesAug. 3, Aug. 7Kiki FoundryCentral2Trainspotting, Valley of the DollsAug. 9Totals8Although the above graph contains the information you’re seeking, it doesn’t give you a concise summary or let you easily observe any patterns.How to Do It RightA crosstab done right may instead look something like this: Number of movies rentedCustomer1 23Grand totalJohn Doe11Bob Denver11Allison Peyton11Kiki Foundry11Grand total1214Here you can see at a glance that four customers have movies rented at the moment. Two of them have two movies rented, one has one movie and one has three movies. Your first question is easily answered, letting you know three customers are due rewards for having more than one movie rented at the current time.Notice the grand total is giving you the total number of customers with movies out, not the total number of movies rented at the moment.Where’s the Rest of the Info? Also notice the crosstab doesn’t contain customer location, movie titles and due dates. Instead of clogging up your streamlined crosstab, you can include extraneous information in the form of sub-reports.Attach a sub-report to each row and you can drill down for the additional information as needed. Any information that is not part of your main query, or would otherwise confuse your results, is generally best left to a sub-report instead of on the main crosstab.Crosstab software was designed to help you quickly review information at a glance, and choosing the right data to include ensures the software can effectively do its job.