I call this kind of report the "without" report. Salesforce has built-in functionality to show you items that are linked; for example, it can show you all the contacts that have upcoming activities. It can show you all the Accounts with open Opportunities. But what if you want to see all the Contacts "without" an open Activity or all the Accounts "without" any Opportunities associated with them - you can't - because you are asking salesforce to show you what doesn't exist rather than what does exist.
A client asked me for just such a report this week. He was worried about losing prospects in a "black hole"; meaning, he wanted to know which Contacts had no open Opportunities or open Activities. He knew he had dozens of Contacts that had no upcoming activities - these clients wouldn't be hearing from him because he had never set a task for some kind of followup in the months to come. These clients were in a black hole and he wanted to pull them out.
Here is how we found those contacts. We created two reports. One called "All Contacts" and the other called "All Open Activities." We exported both reports to MS Excel using "Printable View." We then copied the second report into a second worksheet on the first report and renamed the tabs. So to sum up what we've got so far, we had one MS Excel workbook containing two sheets, the first worksheet is called "All Contacts" and the second worksheet is called "All Open Activities." There isn't much on these worksheets. The first one has one column called Contacts. The second worksheet has two columns: Contacts and Activities.
Essentially, the way we solve this problem now is to copy onto the first worksheet the activity from the second worksheet associated with each contact. So for each contact on the first worksheet, a lookup will be made onto the second worksheet. If that contact exists on the second worksheet, we'll copy the activity over to the first worksheet. Then we'll see all the contacts that have an open activity and all the ones that don't - which is the answer we're looking for. Now by simply sorting the list, you'll have a block of contacts with no upcoming open activity.
The excel function we used to lookup the activity on the second worksheet is called VLOOKUP. This formula will be located in the column next to the contact name on the first worksheet. It has 4 operators. They are:
Lookup_Value: This is the field containing the value that you want to search the second sheet for. So we'll put the cell containing the contact name in there. It will be the cell just to the left of where you're putting the VLOOKUP formula.
Table_Array: This is the table containing all the contacts and their open activities. For us, it is the contacts and activities columns on the second worksheet. Highlight that entire table, both columns. Be sure to fix the value with $ signs so it does not slide as you copy VLOOKUP into the rest of the column. It will look something like this: "All_Open_Activities!$A$1:$B$35"
Col_Index_Num: This is simply the column that contains the answer (the activity) that we're copying from the second sheet into the first. For our purposes, it is column 2 - so just put a 2 there.
Range_Lookup: Just put FALSE. This has to do with how closely excel tries to match the contact names. In our case we want an exact match. If it doesn't find an exact match it will put "NA" for the answer.
The formula will look like this: =VLOOKUP(A2, Sheet1!$A$1:$B$35, 2, FALSE)
Now just copy the formula all the way down the second column and sort the list based on the activities in the second column which will put all the NAs together. The NAs are the contacts that do not have any upcoming activities scheduled.
This same methodology can be used for any "without" report. It can show you the records of any object that do not show up on the related list of another object. Accounts without Opportunities, Leads without Activities, Accounts without Contacts, etc.
