We're trying to create what seemingly should be an easy report (and can't get it to work) where we measure the total number of user activities in the Contact object over the past 7 days by user vs the total number of contacts the user owns over 7 days. The activities and contacts measured can only be of three certain status as is indicated in the contact record, Contact.Status__c (we don't want to measure activities for current customers). This last bit is where the complication stems from.
Here's what we've tried:
A simple joined report against the Activities and Contacts. This doesn't work because there is no common User name to group across the report blocks. (We have the user name for the person that created the activity and have the contact owner name, they're not the same though because you sometimes have people creating activities on someone else's contact and we want to count those activities)
Another joined report, except this time we created two custom report types. UserWithActivities (User is the primary, Activities is the sub) and UserWithContacts (User is the primary, Contact is the sub). This at first worked quite well because it gave us the common User we needed to sort the report type. The problem, however, is that we need to sort the activities to only include those activities on Contacts with a certain status (as noted above). The Activity object does not have a referenceable link (from what I can tell) to the Contact parent such that we can pull that status into the report. This is frustrating because the "Activity.Name" field has a direct reference to the Contact object, we just can't get it to go the next level.
This is where we got to when I decided to post here. I think you can solve 2 by creating a lookup field on the Activity object to the Contact parent. Such fields are now possible by request under Winter '15. So we put in the request and now we have it. However, when creating the lookup field, it's blank, so I now need to create a workflow rule that says every time an activity is created, copy the SF id in the Activity.Name to the lookup Field (Activity.LookupToContact__c = Activity.Name) so we can reference that status field in the contact (LookupToContact__r.Status__c).
There's got to be an easier way to do this, right? (other than writing a vf page with a SOQL query in it).
Edit: Anyone have any suggestions on how to get the Activity to refer back to the Contact object to complete #3 without having to use the workflow rule I mentioned?
Edit 2: Well, turns out you can't run a workflow update on a Lookup field on an activity object. So that idea is out.
