Statistic count on found opportunities by employee

Hello, 

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. 

Parents
  • 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!

    Regards,
    Patrick Sullivan
    SugarCRM Support

Reply
  • 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!

    Regards,
    Patrick Sullivan
    SugarCRM Support

Children