Reporting and Dashboards Blog
Reporting and Dashboards Blog - March 2008
-
My view on Activities
Mar 10, 2008If you've ever looked at facebook, one of the addictive features is the list of what your friends are doing. You can press refresh a couple of times a day to see what Darren's status is now, what application Sharon has installed, or who Kendall is friends with.
That's pretty cool - but why isn't there a feed like that in salesforce?
I could put it in my dashboard, and see what's going on in the world around my team's work, or to the activities of people I can see.
For instance - My sales team's sales activities...
This is also the story of trying to get a report, and having it be a bit more complicated than the usual.
More apex than reports
So, a lot of the times that these posts appear, somebody, or many people, have asked me a question, and I think the answer would be useful to many more people.
As I was trying to get the report, I was stymied. I could not use a formula because the field values I wanted were not present in the activities object. I couldn't use the report formulas because I didn't want a summary formula field in a summary report. So what to do?
Apex
Arg! not a report?
No, you can't do this just with reporting. We're going to use Apex to put the string description of what happened in the event.
The apex below will fire each time there is a new event, or an update to an event. This will update a field that sets a "humand-readable summary". This is something like "Pat met with Bradley about Apex Generator sale" or "Casey did Phone Hayden".
1: trigger eventtrigger on Event (before insert,before update) {
2: String sSummaryText;
3: User uAssignee;4: String sWhoType;
5: String sWhoName;
6: Contact cWho;7: Lead lWho;8: Opportunity oWhat;9: Case cWhat;
10: String sWhatType;
11: String sWhat;
12: String sAbout;
13:14: for (Event eIterator : Trigger.new) {
15: sSummaryText=eIterator.Subject;16: uAssignee=[select name from User where Id=:eIterator.OwnerID];
17: sWhoType=eIterator.WhoId+''; //make sure it can't be null
18: if (sWhoType.length()>5) {
19: sWhoType=sWhoType.subString(0,3);20: if (sWhoType.equals('003')) {
21: // it's a contact
22: cWho=[select name from Contact where Id=:eIterator.WhoId];
23: sWhoName=' with '+cWho.name;
24: }25: else
26: {27: //lead is the only other choice
28: lWho=[select name from Lead where Id=:eIterator.WhoId];
29: sWhoName=' with '+lWho.name;
30: }31: }32: else
33: {34: sWhoName='';
35: }36: sWhatType=eIterator.WhatId+'';//make sure it can't be null
37: if (sWhatType.length()>5) {
38: sAbout=' about ';
39: sWhatType=sWhatType.subString(0,3);40: if (sWhatType.equals('006')) {
41: System.debug('opportunity what');
42: oWhat=[select name from Opportunity where Id=:eIterator.WhatId];
43: sWhat=oWhat.name;44: }45: else if (sWhatType.equals('500')) {
46: // case
47: cWhat=[select Subject from Case where Id=:eIterator.WhatId];
48: sWhat=cWhat.Subject;49: }50: sAbout=sAbout+sWhat;51: }52: else
53: {54: sAbout='';
55: }56: eIterator.summary__c=uAssignee.name+' had a '+sSummaryText+sWhoName+sAbout;
57: }58:59: }So, you can fix the English sentence if you want - to change the fixed words I used to get the pieces of the sentence together.
Won't this only update activities going forward?
Yes, but the ones we care about are new or updated, so that's fine - at least to start with. You aren't going to look at old activities to see new activity are you?
But now each task has a summary field that looks like:
What about tasks?
Well, yes the script above only works for Events. You can put the same kind of script on Tasks:
1: trigger tasktrigger on Task (before insert, before update) {
2: String sSummaryText;
3: User uAssignee;4: String sWhoType;
5: String sWhoName;
6: Contact cWho;7: Lead lWho;8: Opportunity oWhat;9: Case cWhat;
10: String sWhatType;
11: String sWhat;
12: String sAbout;
13:14: for (Task tIterator : Trigger.new) {
15: sSummaryText=tIterator.Subject;16: uAssignee=[select name from User where Id=:tIterator.OwnerID];
17: sWhoType=tIterator.WhoId+''; //make sure it can't be null
18: if (sWhoType.length()>5) {
19: sWhoType=sWhoType.subString(0,3);20: if (sWhoType.equals('003')) {
21: // it's a contact
22: cWho=[select name from Contact where Id=:tIterator.WhoId];
23: sWhoName=' with '+cWho.name;
24: }25: else
26: {27: //lead is the only other choice
28: lWho=[select name from Lead where Id=:tIterator.WhoId];
29: sWhoName=' with '+lWho.name;
30: }31: }32: else
33: {34: sWhoName='';
35: }36: sWhatType=tIterator.WhatId+'';//make sure it can't be null
37: if (sWhatType.length()>5) {
38: sAbout=' about ';
39: sWhatType=sWhatType.subString(0,3);40: if (sWhatType.equals('006')) {
41: System.debug('opportunity what');
42: oWhat=[select name from Opportunity where Id=:tIterator.WhatId];
43: sWhat=oWhat.name;44: }45: else if (sWhatType.equals('500')) {
46: // case
47: cWhat=[select Subject from Case where Id=:tIterator.WhatId];
48: sWhat=cWhat.Subject;49: }50: sAbout=sAbout+sWhat;51: }52: else
53: {54: sAbout='';
55: }56: tIterator.summary__c=uAssignee.name+' did '+sSummaryText+sWhoName+sAbout;
57: }58:59: }Because in API and Apex, Tasks and Events are different...
Report and dashboard component.
We can then do a report on the list of these human-readable activities, and a dashboard.
The report shows the list of the most recent 10 activities - closed tasks or past events - and then displays them (using a top-n report).
The dashboard can then show those 10 items.
Other components
For the package, I also added some more components - for instance, a display of how people are doing on their tasks - showing completed by person for tasks created in this month or last month.
I also added one on the number of activities per closed opportunity status - to show activities driving deals.
This was built using the power of one trick again to calculate the number of activities per opportunity, then the per-stage view averages that out.
Package
The package is available here:
And I'll probably have to update it when Simon Fell or somebody tells me that my method for testing to see whether the WhatId is an opportunity or Case should not rely on substring comparisons.
The package also includes test code that you might find useful if you are trying to test Apex Triggers (especially on Activities). I have 100% test coverage! W00t!
Bookmark