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:

enter image description here

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.

  • Can you draw the data model and post it here? it would be easier to see and then decide? – Pranay Jaiswal Nov 27 '18 at 14:38
  • Yes Pranay! Thank you for taking your time, and looking at this issue. I just added a quick sketch that I made in Excel. Hope it helps. – Laureant Nov 27 '18 at 15:02
  • 1
    Can you use the "Sales Rep" as the joined field for reporting? – battery.cord Nov 27 '18 at 15:03
  • I can't use it like that, and even if I did, I wouldn't be able to group the data on dates. The necessary date values are not on the Sales Rep object. – Laureant Nov 28 '18 at 10:06

Your Answer

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

Browse other questions tagged or ask your own question.