Answered by:
MS dynamics crm 2011 edit xml of chart to get count of Won vs ALL opportunities

Question
-
Dear Support Team,
We are currently working on a dashboard for our on-premise CRM dynamics 2011 instance. What we need is to display Won VS ALL opportunities of a certain account manager. I have tried exporting one of the default chart in CRM which is the Won Deals VS Lost Deals. I have modified the XML to get the COUNT of opportunity number for WON and LOST deals instead of the SUM of revenue. Now, i have imported the modified xml content and was able to import it successfully. Upon checking the chart data, it shows the same count for WON and LOST records which is incorrect. How do i able to correct this. Please refer below to the xml codes i have imported. Looking forward to any help. Thank you.
<visualization>
<visualizationid>{7842C8AF-0FD2-E411-9758-F5ACB647B220}</visualizationid>
<name>Test Chart For Comparison</name>
<description>Shows the amount of revenue for won deals versus lost deals.</description>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="sum_lost" name="opportunityid" aggregate="count" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="sum_won" name="opportunityid" aggregate="count" />
<filter>
<condition attribute="statecode" operator="eq" value="1" />
</filter>
</link-entity>
<attribute groupby="true" alias="groupby_column" name="nexus_accountmanager_lookup" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category>
<measurecollection>
<measure alias="sum_won" />
</measurecollection>
<measurecollection>
<measure alias="sum_lost" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
<presentationdescription>
<Chart Palette="None" PaletteCustomColors="97,142,206; 168,203,104; 209,98,96; 142,116,178; 93,186,215; 255,155,83; 148,172,215; 217,148,147; 189,213,151; 173,158,196; 145,201,221; 255,180,138">
<Series>
<Series Name="opportunity_statecode,1" Color="149, 189, 66" IsValueShownAsLabel="False" BackGradientStyle="TopBottom" BackSecondaryColor="112, 142, 50" Font="{0}, 9.5px" LabelForeColor="59, 59, 59">
<SmartLabelStyle Enabled="True" />
</Series>
<Series Name="opportunity_statecode,2" Color="255,124,31" IsValueShownAsLabel="False" BackGradientStyle="TopBottom" BackSecondaryColor="235,98,0" Font="{0}, 9.5px" LabelForeColor="59, 59, 59">
<SmartLabelStyle Enabled="True" />
</Series>
</Series>
<ChartAreas>
<ChartArea BorderColor="White" BorderDashStyle="Solid">
<AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181">
<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">
<MajorGrid Enabled="False" />
<MajorTickMark Enabled="False" />
<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
</AxisX>
</ChartArea>
</ChartAreas>
<Titles>
<Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="0, 0, 0" />
</Titles>
<Legends>
<Legend Alignment="Center" LegendStyle="Table" Docking="Bottom" Font="{0}, 11px" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>Best regards,
Honey Dulatre
Wednesday, March 25, 2015 9:04 AM
Answers
-
I see, it is because your aggregates are "count". They need to be "countcolumn" for the filter to work.
Ulrik,
Blog: http://crmchartguy.wordpress.com - Exclusively for MS CRM Charts
Twitter: crmchartguy- Marked as answer by Honey Dulatre Monday, April 20, 2015 5:11 AM
Wednesday, April 8, 2015 1:10 AM
All replies
-
You'll need to create a filter for each of your two series in the xml.
This blog post describes the process for filtering invididual series within the chart xml.
Ulrik,
Blog: http://crmchartguy.wordpress.com - Exclusively for MS CRM Charts
Twitter: crmchartguyTuesday, March 31, 2015 9:52 PM -
Hi Ulrik,
I will try to use this as my reference. By the way, my main objective is to count all WON leads VS LOST leads.. I have searched so many related links but the samples are all SUM.. Thank you for the support. I'll get back to you for the progress.
Best regards,
Honey
Tuesday, April 7, 2015 12:31 AM -
I see, it is because your aggregates are "count". They need to be "countcolumn" for the filter to work.
Ulrik,
Blog: http://crmchartguy.wordpress.com - Exclusively for MS CRM Charts
Twitter: crmchartguy- Marked as answer by Honey Dulatre Monday, April 20, 2015 5:11 AM
Wednesday, April 8, 2015 1:10 AM -
Hi Ulrik,
Thanks for the info :-) the "countcolumn" worked. I very much appreciated the help.
Best regards,
Honey Dulatre
Monday, April 20, 2015 5:13 AM