The key fields in the table were the StudentID and a Course Name. Our suggested solution was to put the table in the report twice and link them by StudentID. So if you take the first instance of the table, you have each student and every course they are enrolled in.

The join then takes that student and links them to a second table with every course the student is enrolled in again.

The crosstab is then a simple and easy solution. The row field is the course name from the first table and the column is the same field from the second table. The summarized field is a count of the StudentIDs.

A couple of simple crosstab formatting options such as
suppress row and column totals, and format each cell with
no decimals and blank when zero.

Click image to enlarge

.

A couple of interesting things about analyzing the crosstab above

When you look at a report like the one above, there are some important things to look for. There were 8 records in our original table, but there are 18 records in our crosstab. So on average each student is enrolled in 2 to 3 courses. We kept the volume down to make the testing easier.

We get a class count on the diagonal in the crosstab. The “2” in the French/French cell suggests that two students are doing French, likewise, 2 in Geography, 1 in History, etc. You could use the new crosstab functions in Crystal Reports 2008 to highlight these cells where the column name and row name were the same.

We also get the data duplicated. There is one student doing a Geography/Physics combination. Likewise, the same student is also doing a Physics/Geography combination.

For that reason, the total of any column or row is going to be twice the number of students in the class so could be misleading. For that reason we suppress the total row and column. Those options are available on the “Customize Style” tab of the Crosstab expert.