web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Report for win/loss of...
Microsoft Dynamics CRM (Archived)

Report for win/loss of opportunities/leads

(0) ShareShare
ReportReport
Posted on by Bobh 560

I am trying to add a report to show the win/loss percentage for leads.  I have a Lead field called "MRH owner" which is the person who is helping me with that lead.  I would like to display the win/loss percentage for each "MRH owner" as well as an overall win/loss for me.  Thanks in advance for your help!

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: Report for win/loss of opportunities/leads

    Hi Bobh,

    If you're talking about win/loss percentages, I presume you mean leads that have been qualified and turned into opportunities?

    The fastest way to achieve this is by using the charts/dashboard feature in CRM.

    1. Create a view on Opportunities with the following filter:
      1. Status Equals Won;Lost
      2. From the Related tables, select Originating lead

    2. Based on this view, make a new "Stacked Chart" with 2 series and 1 category:
      1. Both series will be "Status, Count:All"
      2. The Category will be the "MRH owner" field
    3. Save the chart
    4. Export the chart. This will create a download with the chart xml.
    5. Basicly, what you need to do now is adjust the series so the first series points to the lost opportunities and the other one will point to the wins: there is a really good description found here with plenty of screenshots to help you: http://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/
    6. Save the xml and re-import the chart into CRM.

    Now you can use this chart on the opportunity view or in a dashboard. Remember that a personal chart can be shared with other users but you might want to opt to make a system view and a system chart so you everyone can see the data.

    Feel free to as more questions if any of the above steps aren't clear. Good luck!

    PS: If editing xml charts is a step to far for you, you could just export the results of your view into Excel and build a pivot table or a chart there if you're more comfortable with that.

  • Bobh Profile Picture
    Bobh 560 on at
    RE: Report for win/loss of opportunities/leads

    I was trying to do a simple table such as in the "Lead Source Effectiveness" report showing a % for each MRH Owner.  Is that possible?

    I did try to build out the other report as you suggested but I can't seem to get the filters to work in the XML Which I assume I will also need for the new report.  Can you spot my error?  Thanks!  Here is the report code:

    <visualization>

     <visualizationid>{EE99C7D8-98DA-E211-8CF1-78E3B511B601}</visualizationid>

     <name>MRH Closing Percentages3</name>

     <primaryentitytypecode>opportunity</primaryentitytypecode>

     <datadescription>

       <datadefinition>

         <fetchcollection>

           <fetch mapping="logical" aggregate="true">

             <entity name="opportunity">

               <link-entity name="lead" from="leadid" to="originatingleadid" link-type="outer">

                 <attribute groupby="true" alias="MRH_Owner" name="new_mrhowner" />

               </link-entity>

               <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">

                  <attribute alias="Won" name="statecode" aggregate="count" />

                    <filter>

                      <condition attribute="stepname" operator="eq" value="Won" />

                    </filter>

               </link-entity>

               <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">

                  <attribute alias="Lost" name="statecode" aggregate="count" />

                    <filter>

                      <condition attribute="stepname" operator="eq" value="Lost" />

                    </filter>

               </link-entity>

             </entity>

           </fetch>

         </fetchcollection>

         <categorycollection>

           <category alias="MRH_Owner">

             <measurecollection>

               <measure alias="Won" />

             </measurecollection>

             <measurecollection>

               <measure alias="Lost" />

             </measurecollection>

           </category>

         </categorycollection>

       </datadefinition>

     </datadescription>

     <presentationdescription>

       <Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">

         <Series>

           <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

           <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>

         </Series>

         <ChartAreas>

           <ChartArea BorderColor="White" BorderDashStyle="Solid">

             <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

               <MajorGrid LineColor="239, 242, 246" />

               <MajorTickMark LineColor="165, 172, 181" />

               <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

             </AxisY>

             <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

               <MajorTickMark LineColor="165, 172, 181" />

               <MajorGrid LineColor="Transparent" />

               <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

             </AxisX>

             <AxisY2 LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

               <MajorGrid LineColor="239, 242, 246" />

               <MajorTickMark LineColor="165, 172, 181" />

               <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

             </AxisY2>

           </ChartArea>

         </ChartAreas>

         <Titles>

           <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>

         </Titles>

         <Legends>

           <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />

         </Legends>

       </Chart>

     </presentationdescription>

     <isdefault>false</isdefault>

    </visualization>

  • Community Member Profile Picture
    Community Member on at
    RE: Report for win/loss of opportunities/leads

    A simple table is possible ofcourse, but you would need to build a custom report for it aswell. Are you working with CRM online or on-premise? Custom reports (aka SSRS report) are build with a report design suite in visual studio and depending on your organistion, you'd build a dataset with sql or fetch xml. This reporting method gives you more freedom and a lot more tools to work with, but I didn't want to suggest it since it requires some setup.

    I'm looking into the chart code and I believe your filtering should look something like this:

    <filter>

    <condition attribute="statecode" operator="eq" value="1" />

    </filter>

    <filter>

    <condition attribute="statecode" operator="eq" value="2" />

    </filter>

    1 = Won, 2= Lost

    For percentages as labels, use: IsValueShownAsLabel="False" Label="#PERCENT" LabelFormat="#0.##%"

    in the format of the series.

  • Bobh Profile Picture
    Bobh 560 on at
    RE: Report for win/loss of opportunities/leads

    I am using CRM online.  I modified the filter statement per your instructions, but the stacked bar is created and shows the same count for both parts which is the total for all opportunities for each MRH_owner.  So the filter statements don't seem to have any effect whatsoever!?

  • Verified answer
    Ulrik Profile Picture
    Ulrik 105 on at
    RE: Report for win/loss of opportunities/leads

    You need to change your aggregates from "count" to "countcolumn"

    Ulrik

    www.crmchartguy.wordpress.com

  • Bobh Profile Picture
    Bobh 560 on at
    RE: Report for win/loss of opportunities/leads

    Changing to "countcolumn" results in all stacked bars showing "0" as the count

  • Ulrik Profile Picture
    Ulrik 105 on at
    RE: Report for win/loss of opportunities/leads

    If you get a zero count it is because your filters are coming up with zero records. Just had a second look at the xml, and it looks like you need to change your filters to what Steven suggested earlier.

    Ulrik

    www.crmchartguy.wordpress.com

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
HR-09070029-0 Profile Picture

HR-09070029-0 2

#1
UllrSki Profile Picture

UllrSki 2

#3
ED-30091530-0 Profile Picture

ED-30091530-0 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans