0

I've been on this journey to get a more complex report done, and after trying out various methods (joined reports, custom report types, various rollup apps) I still haven't gotten anything to work.

Main goal: create a monthly (or quarterly) report for each Sales Representative, where we can see how the Sales Rep performed. There's a custom object (called Monthly Target), where we have a TARGET field, and right next to this, we would like to see the Net Amounts (from the Sales Rep' Opportunities), and the Actual Amounts (from another custom object).

I've been looking at Rollup Helper and Declarative Lookup Rollup Summaries, because these apps look like they can gather the necessary information to one object, and then I can create a simple report on that object only.

We have the Monthly Target custom object, where we already have a Target field, but I would like to collect the other data here as well.

The Target object already has a Sales Rep (User Lookup) field, so naturally, I thought that this could lead to success, because I could simply just query the sales reps opportunities and get the sum of the net amounts.

This didn't work however, since in either apps that I tried, I could not select the opportunities, since there isn't a direct relationship between the 2 objects (Monthly Target and Opportunities)

So can the following be done somehow?

  • On each Monthly Target object we would create a Net Amount field
  • Using the Monthly Target's Sales Rep field, we would SUM the Opportunities of the selected sales rep, so we could get a Net Amount, and store that on the Monthly Target custom object

Here's the object model, and a small example of how the report should look at the end: enter image description here

  • Could you please provide the links between the objects in the graphic as well ? Functionnaly speaking, why wouldn't opportunities be linked with the monthly target ? – Answers_Seeker Nov 29 '18 at 13:36
  • Those 3 objects in the graphic are not linked to each other. They're linked to the Sales Rep. Currently the Monthly Target is an object where we set the Target amount, we set the Sales Rep, and we set the date period. I can't imagine how the opportunities could be linked to this object. – Laureant Nov 29 '18 at 13:56
  • In other words, the 'Sales Rep' is the common object between those 3. – Laureant Nov 29 '18 at 13:58
1

There's no way you're going to configure a report like this. It might have been possible if you'd started off with orders, opportunities, and forecasting, but definitely not with custom objects involved.

Instead, you need a summary object. This object would have the fields for Sales Rep (lookup user), External Id (text, 128 characters), Actual Amount, Estimated Net Amount, Target Amount, and any various formulas you might want (actual vs net estimate, etc).

You'll need a before insert/before update trigger that each calls a common algorithm that would look something like this:

// Before insert/before update
public static void matchSummary(SObject[] records,
                                SObjectField summaryField,
                                SobjectField dateField,
                                sobjectField userField) {
  Map<Id, String> hashes = new Map<Id, String>();
  Map<String, Summary__c> summaries = new Map<String, Summary__c>();
  for(SObject record: records) {
    Id userId = (Id)record.get(userField);
    Date month = (Date)record.get(dateField);
    if(userId == null || month == null) {
      continue;
    }
    month = month.toStartOfMonth();
    String key = EncodingUtil.convertToHex(
                   Crypto.generateDigest(
                     'sha-256', Blob.valueOf(userId+'/'+String.valueOf(month))
                   )
                 );
    summaries.put(key, new Summary__c(External_Id__c=key, Sales_Rep__c=userId, Month__c=month));
    hashes.put(record.Id, key);
}
upsert summaries.values() Summary__c.External_Id__c;
for(SObject record: records) {
  record.put(summaryField, summaries.get(hashes.get(record.Id)));
}

From here, you're mostly done. You now have an object that has the Sales Rep and the month. All that's left from here is to use DLRS to sum the amounts from the three objects on to this summary field, and finally build your reports.

  • This object should have to be reusable in similar use-cases as auditors may go really tough if he created the object only for only one report. Just pointing this so OP doesn't create a new custom objects each time he has a similar need. – Answers_Seeker Nov 29 '18 at 14:22
  • Hi sfdcfox! Thank you for the response and the code example. I didn't completely understand the example, however, I understood that I must create a new summary object which would have the following fields: Sales Rep, Month, External ID, Actual Amount, Estimated Net Amount, Target. Let's say that this is done, and I called the object "Quota". Now, after analysing the code example, I can see that it's hashing data, and putting it in a summary field. That's the part which I didn't understand. Could you please explain why is this necessary? – Laureant Nov 30 '18 at 10:41
  • Also, the code example is not mentioning the "Actual Amount", "Estimated Net Amount" or "Target" fields at all. I'll have to use DLRS to put the data in those fields, and take them from the summary field somehow? Another question: is that summary field visible in Salesforce? – Laureant Nov 30 '18 at 10:42
  • @Laureant (a) The External ID is to match existing/create new "Quota" records, as you called them. A hash is one simple way to do this, but not strictly necessary. Technically a simple string concatenation would suffice. It's just my preferred method to avoid accidental matches (you would need to make the field unique+case sensitive if you don't hash). (b) Yes, you would still use DLRS for the summary fields. (c) The External ID does not need to be on the page layout or even visible; only the code needs to use it. – sfdcfox Nov 30 '18 at 14:31
  • @sfdcfox Hi again! I find it a little bit interesting that after we've ventured into Apex trigger development, there's still need to use DLRS. At this point, couldn't we simply just write the whole rollup in an Apex class, and write an Apex trigger for it? – Laureant Dec 3 '18 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.