I would like to create a report, where I can monitor my sales representatives targets and actuals.
- We have some custom objects defined where we put the Target for each month. So for a year, each sales rep has 12 of these records created.
- We also have a custom objects where the actual sales are defined. Each sales rep can have multiple of this, in every month.
- Then, we would like to also use the Net Amount on the built-in Opportunity object for each Sales Rep.
The report would display the SUM of each of these amounts right next to each other, for each month.
I've been trying to create a custom report type, but I haven't been able to create the necessary relationships between the objects. They're simply not in the list where you can select a relationship.
I also tried creating joined reports, but I haven't been able to group by month, because I can only group by the fields of the object that is common (in this case, that would be the Sales Rep).
What is the best way to approach this situation?
On paper, this sounds like an extremely easy thing to do, the only "problem" is that data is stored in multiple separate objects, but that should be solvable.
Here's the object model, and an example in Excel:
You can see the 2 custom objects that are used. The bolded red fields are the ones that are summed for each month for each sales representative. Then I added a small table where I put 3 months worth of dummy data in.