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: