Rollup Opportunity amounts on a Custom object using user account as middle man

Question

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:

Answer 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.