Statistic count on found opportunities by employee


Trying to find the right report to transfer to a spread sheet or PDF file the number of opportunities found by employees.

Meaning, I would like Sugar to give me the list of employees that found an opportunity, say first column would be the name, then the other columns would be by month and the count of found opportunities with a total for the 12 last month at the end.

Would look something like this in my mind : 

Name            January   Feb.   March   ...... Dec. Total

Employee A         1          4         0                 2        14

Employee B         1          3         0                 4        19

Employee are not necessarily Sugar users, so at our company our employees are created as contacts in Sugar under our Company account and we have a check box to say that this contact is a "Dénicheur" (sorry for the french term) or someone that can find opportunities.

So when creating an opportunity, if the opportunity was found by one of our employees, under lead source, when go and put "Dénicheur" which in turn brings an hidden search box (which can only give us names of contact with the "Dénicheur" box checked) where we can select the name of the of the employee that found the opportunity.

So I am trying to find a report that can list the name of our "Denicheur" and list the the count of opportunity found by that person.  We do not need the name of the opportunity, but just the number of opportunities found in that month bu that person.

I have tried many things, but so far have come up with nothing that came close to what I would like.

Anyone ever needed this done or knows how to do it or which type of report ?

Any help would be much appreciated

Thank you in advance. 

  • Hi Pascal,

    Based on the information that you provided, I was able to create a Matrix Report that I believe might be similar to what you are trying to do.  In a very basic form, here is what the results of my report look like:

    I started by opening the Opportunities module in Studio and creating two fields, based on your original post description.  The first field I called "Employee" and this is a checkbox field.  The second field is called "EmployeeName" and is a relate field to the Contacts module.  I set the relate field dependent upon the Employee field being checked.

    I opened a few Opportunity records and ticked the Employee checkbox and chose random contact names for the EmployeeName field.  I subsequently took the following steps to create the report:

    1. Open the Reports module and then go to Create Report.

    2. Choose "Matrix Report" as the report type.

    3. Select Module > Opportunities

    4. Define Filters > Choose the Employee field, and set it so that you only see results when Employee Equals Yes.  You may also want to set other filters to meet your needs, such as only opportunities created this year.

    5. Define Group By > EmployeeName

    Define Group By > Month: Expected Close Date (you can also use Month: Date Created if that works better for you)

    6. Choose Display Summaries > Count

    There will already be two other items listed, EmployeeName and Month: Expected Close Date.  Leave these, and add Count from the list of fields in the bottom left corner of the screen.

    7. Chart Options > No Chart, unless you want one.

    8. Report Details > Name the report and then save and run.

    Please let me know if you have any questions!

    Patrick Sullivan
    SugarCRM Support

  • Thank you Patrick, 

    Will try that and will let you know.

    Have a nice day

  • Hello Patrick, 

    Followed your steps and that is what we were looking for, thank you so much.  The only problem I have is with the employees names.  Our database or contacts are mounted as first name and last name as 2 seperate fields.  So in define group I can only put either First or last name, then in display it will give me only what I choose.  Problem we have is we have people with the same first name and 2 people with the same last name.  Like we have 2 Stephane, So instead of giving me the numbers individually of Stephane #1 and Stephane #2 it will give me combined number for all the Stephane's.

    Is there a way to have both First Name and Last name or should I create a new field in contacts that would be filled if only that person is a "Denicheur" ?

    Thank you once again for your help

  • Hi Pascal,

    Is there an option to group by just "Name"?

    If not, one option you may have would be to create a custom field in Contacts that would use a calculated value formula to concatenate the First Name and Last Name fields together.  Then, you could group by this field, so that the contacts who have the same first name (or same last name) remain distinct in the report.  If you pursue this route, just keep in mind that when you create a new calculated value formula using Sugar Logic, you will need to use either "Mass Update" or "Recalculate Values" functionality to ensure that the formula runs for each record and populates the field accordingly.… 

    Patrick Sullivan
    SugarCRM Support

  • Thank you Patrick, 

    In our Studio, under contact, cannot find the "Name" Field, so I created a custom field that we manually enter the name (we do not have many employees, so it was fast to do).  Then put that custom field in the edit contact and that is where I found a field named "Name" that does what you say, but that field is nowhere to be found in our Field lists other than I see it in Label.  I have checked in my report and that "Name" field is not accessed by the report.  Anyhow, I selected my custom field and everything is PERFECT  thank you so much.

    One thing though, is when I export it to PDF, it does not export the table we see on the picture above, instead it gives me a list that is like this : 

    Is there a way to have the table in PDF ?

    If not it's no biggie (I can print screen it and save it as jpg), we are much better off anyways thanks to you

  • Another thing I notice, is that the months column are not ascending in time, meaning there are shown randomly  i.e : March 2016, April 2016, September 2016.  Is there a way to fox this?

    Thanks again

  • Hi Pascal,

    I believe that if you set the "Group By" to have the Date/Month first (before grouping by Employee Name) then it may move the columns around so that they are in order.

    Patrick Sullivan
    SugarCRM Support

  • Hi Pascal,

    It looks like this is not possible right now and is covered by the following feature/enhancement request:

    Patrick Sullivan
    SugarCRM Support