Subreports 2.0

Now that we have the phone fields we need in our data file, all we have to do is place the fields on our report, right? Well, let’s give it a try and see what happens.

With the report open on the Design tab, click and drag the phone number field (GfCnPh_1_Phone_number) and drop it in the Group Header #1 section, to the right of the Name (GfCnBio_Name) field. Refresh the report and see the results.

Hmm, not so good. It shows a phone number or email, but it’s only showing one per constituent. That’s not what we want so let’s move the phone number field from the Group Header #1 section to the Details section. Any better?

Nope. This time it is showing all of the phone numbers and emails, but it is also creating duplicate gift rows. In the example above, Joseph has three phone numbers and one email address. Placing these in the details section causes the report to show all of the possible combinations of phone/email and gift information. (Do a Google search for “Cartesian product SQL” if you want to learn the technical details for why this happens.)

You have probably figured out by now (thanks to the article’s title) that we will need to use a subreport to add the phone numbers and email addresses to the main report. But first a quick note to keep subreports in mind whenever you add a field and it causes duplicates. It usually means you should use a subreport to add the information to the report.

Continued in the next article.