Most common reporting tasks require organizing fields on one object, but when you start working with multiple objects in a single report its important to understand the implications of the object model on report calculations.
Object relationships can be one to one or one to many, and in a one to many scenario you may have data from the same object appear on multiple rows of your report. For example, if you use the Campaigns with Lead report type if you have a lead with multiple campaign members associated to it, you will see that lead’s data on each of the campaign member rows. Similarly, if you add fields via a lookup relationship to a report type the data will be have the same way.
This makes a lot of sense from a reporting perspective, but what happens if you try and summarize one of these fields you’ll notice that the value will “de-dupe” and you will only see the result of the real values on the underlying record, which will visually appear different than what you see in the report. Keep in mind that for summary and matrix reports, this behavior happens for each grouping individually. This means that if you are doing a sum, the values at each grouping value won’t necessarily add up to the values on the total line.
A good way to understand this behavior is to experiment. If you create a checkbox on the lead object and have the formula evaluate to “true”, then create a formula on the campaign member that pulls the data from the same formula onto the campaign member directly. Then you can create a Campaign with Lead report that shows both a field behaving directly on the main object in a report as well as one that is on a related object. This will let you experiment with groupings and summary fields and formulas and see how each behaves.
Once you have a good understanding of the behavior you can leverage it to make interesting reports. For example, I recently had someone asked me to create a report that shows the number of unique leads and contacts associated with campaigns during a certain period of time. I created a count field on both the lead and contact object using the same logic as the example formula above. Then I used that in a joined report, Campaigns with Leads and Campaigns with Contacts, with a cross block formula to summarize the total number of unique people who responded to campaigns.
