Basic Reporting Part V FAQ: How to Create a Mailing List Report / How to Create a Report With No Duplicated Names

Basic Reporting Part V FAQ: How to Create a Mailing List Report/A Report With No Duplicated Names

Part I - Mailing Lists

One of the most frequent questions we get is about creating reports that can be used in a mail merge for letters, labels, and/or envelopes ("mailing list reports"), and while we do have a tutorial below that explains how to do that, creating your letters this way is actually harder and takes longer than just using CRM's built in "Letters" function. Letters (which is separate/different from Acknowledgement Letters in the system) lets you create mail merged letters (that can be emailed directly from inside the system to the recipients or downloaded to MS Word and/or PDF to be printed out and sent via postal mail). The Letters functionality uses the advanced search interface to build your recipient list (which, by definition, only generates one record/letter per contact), building the list and merging the letters happens in one step (rather than in many steps when you write a report, export it to Excel, and then do a mail merge inside MS Word), and the system automatically records that a communication was sent in each contact's record (in Engagement History). This is MUCH more efficient than creating mail merges the old fashioned way, especially since it takes a bit of manipulation to get a mailing list report set up in the reporting area.

You can use the Letters function to create any kind of letter you want - want to remind your members that it's time to renew? Use Letters. Want to send a donation appeal to donor prospects or lapsed donors or last year's donors? Use Letters. Want to make a flyer or newsletter you can print out and mail to your contacts? Use Letters. There's no limit to the type of letters you can send.

See this tutorial in the Communications section of the support library on using the Letters functionality.

If you wish to continue with a mailing list report, the tutorial is below. 

Part II - Why Names Appear Multiple Times On a Report

1. A "mailing list report" is a report in which each contact name appears only once
2. Databases (such as CRM and Connect) are made up of tables, such as a Contacts table that contains name, address, phone number, etc. and a Donations table, which might contain donation amount and date.
3. Reports pull data from one or more tables in the database AND COMBINE THAT DATA INTO EVERY POSSIBLE COMBINATION. For example, if we write a report showing contacts who made a donation during 2016, and Bob gave two donations, on the report we would see:

1. Contact Name (Bob) – First Donation Amount ($25)
2. Contact Name (Bob) – Second Donation Amount ($50)

In this case, Bob would appear on our report twice. If we then also added Event Registrations to our report, and Bob registered for two events during 2016, our report would show:

1. Contact Name (Bob) – First Donation Amount ($25) – First Event Registration (Annual Gala)
2. Contact Name (Bob) – First Donation Amount ($25) – Second Event Registration (July Jubilee)
3. Contact Name (Bob) – Second Donation Amount ($50) – First Event Registration (Annual Gala)
4. Contact Name (Bob) – Second Donation Amount ($50) – Second Event Registration (July Jubilee)

As you can see, Bob would then appear on our report 4 times, because the report is combing all the information we are asking for in every possible combination (the report doesn’t know which event registration goes with which donation amount, because the two tables are unrelated/unconnected, so it does the best it can by presenting every possible combination).

This is normal; this is how reports work in all platforms.

In Advanced Reports, there is a per report setting that can minimize the instances of "one row per combination of data" called "Special Cartesian Processing." When you set this to "True," it can remove the extra/"non-sense" rows (if you want to learn more about what this means, check out this Exago Tutorial).

You will find the Special Cartesian Processing setting on the reports Settings: Advanced: Joins

Part III: How To Get Around This In Reporting

In order to create a report where each contact only shows up once on the report, users have 3 options:

1. Do not include non-unique data on the report.
   1. Unique data is data for which there would only be one record in the table – such as contact name or address. Non-unique data is data for which there may be more than one record in the table – such as donations or event registrations. Donations are not unique data because contacts could have made more than one donation. Event Registrations are not unique data because contacts can register for more than one event (and have more than registration per event, even). Volunteerism is not unique data because contacts can volunteer on more than one day or for more than one opportunity. Etc. Therefore, to avoid having the contact appear more than once on a report, do not include non-unique data. If you are writing a mailing list report, then you should only include those fields you need for mailing labels/envelopes, such as name and address (all of which is unique data).
2. Use an Advanced Search
   1. By definition, the search/advanced search function provides users with a non-duplicated list of contacts who meet the search parameters/filters. Users can export search results to CSV (Excel) or use a Saved Search as a filter in a report (if the user wants to customize the fields in the export), so using the Advanced Search function to create the report is one of the fastest and easiest ways to create a non-duplicated list of contacts. See our tutorial on using a Saved Search in a report for more information.
3. Use Grouping/Group Headers to Roll Up Duplicated Data
   1. Sometimes users wish to create a report that has non-unique data (such as donation details) that will also be used to create a mailing list. For instance, the user may need the donation details to provide to their board or Executive Director but then also want to send a letter to these same individuals, or the user may want the details on the report to help trouble shoot the report or verify its accuracy.
   2. In this case, adding a group header to the report (on Contact Name or Contact ID or Household ID) and adding the unique data (name, address) to the group header and then suppressing the details section (where the non-unique data is displayed) can allow the user to create a report that contains non-unique data but which can also be used as a mailing list report.
   3. For more information, see our tutorial on Adding Grouping to a report.