We have a custom object that we use to replace Activities and in this object, a user can track which other employees attended a meeting via a lookup(User). (There are 4 fields). The owner of the record is the generally the first attendee and there could be 4 others... When creating a report, I want to see how many meetings a user attended, either as the owner or an attendee, how do I do this? Right now, if I group it by Owner, it doesn't show me the other meetings. I thought maybe I need a formula field on the object but don't know how to go about writing it. (I'm not a formula rockstar). I tried a User/Object report type and that didn't work... I tried using filters and report formulas on in the report, and bucketing doesn't work because its essentially 5 fields to bucket. Help?!
There is probably a better way, but this way should work. If you have a lot of users, though, it will not be efficient.
On your Activity__C object, create a formula field for each user who could be listed as an attendee. I would probably label the fields with the user's name.
You can use a formula like this to get the count. You will need to modify it to match the number of fields and your naming conventions. This also assumes that you are using a lookup for your Activity fields.
if(owner.username = [email] || attendee_1__c.username = [email] || attendee_2__c.username = [email], 1, 0)
With this, if the set user was listed in any of the fields, the formula for their name becomes a 1. Otherwise, it is a 0. Then, you can build a report that shows those fields as columns. The grand total of each person will be listed at the bottom of the report (or in the sub counts if using a summary style report.
Because of your database design using a SFDC report isn't going to be practical as one would normally make Activity__c have a child record called
Attendee__c and each attendee listed there. Then reporting would be easy.
I'm going to assume that you aren't an Apex developer and are looking for a point-and-click solution. I'm also going to assume you don't have Conga Composer, an app exchange tool that could do this report via a clever Excel template.
You could do the following (some ugliness involved):
- Create a formula field (text 255):
Attendee_List__c(assumes average name len < 50 chars)
- The formula field is the concatenation of the
attendee_1__r.name, semi-colon, attendee_2__r.name, ...
- Run a report on
- In the browser, type in a user's name in the search box, the browser will tell you the number of occurrences for that name
If you want to see a report of # of meetings by user, then take the output of #4 into Excel and then split
Attendee_List__c on semi-colon and put results into one column. See this Excel tip using TRANSPOSE function (or Google for others).
Then you can use Excel pivot table to count meetings by Attendee.