Posts Tagged CRM Chart XML

Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM

When setting up a new Dynamics 365/CRM trial a few standard dashboards are included. However, I feel they lack something, and those who follow me on Twitter know I occasionally like to make fun of particularly the funnel chart. So here’s a hopefully improved version, that you can use, either in your organization or simply to spice up a demo environment.

Well, why not do something about it then. Plus, I’ve toying with this idea for a while anyway.

Crowdsourcing?

I did have the audacity to use the term “ultimate” in the title and the dashboard I provided is clearly not. However, with lots of input, we can make this much better.

Therefore; I would love to hear from you if you have suggestions how to make these dashboards better.

Suggestions could include, but are certainly not limited to:

  • Ideas for new KPIs
  • Ideas for new Charts
  • Ideas to improve formatting and make the charts easier to decode
  • Using charts contextually on record forms
  • Better use of colors between the different charts

My only request is that you elaborate on your suggestion, and I will try to incorporate as much as possible and hopefully have this grow into something very useful.

At the bottom of this post, I’ve included a zip file containing a solution with all the components for the dashboards in this post. The zip file contains both a managed and unmanaged solution that can be imported to Dynamics 365/CRM, and all the chart xml files individually.

Assumptions

It should be possible to import the charts on Dynamics 365/CRM organizations, so I will try to adhere to the following rules and assumptions:

  • Only use the charts within Dynamics 365/CRM*
  • Only use system fields
  • Only use system relationships
  • Avoid using calculated fields for KPIs – unless it is a system field
  • Take as much advantage of the existing setup as possible

*Why am I limiting myself to only using the charts within Dynamics 365/CRM and not including Power BI and Customer Insights. While Power BI and Customer Insights are powerful features, they are both tools external to Dynamics 365/CRM. You cannot as easily, share the charts and KPIs within CRM itself. Charts also have some significant advantages too. They can be contextual to the user logged in, respects the security roles, and they are real-time. “I just won an opportunity. I’ll happily wait an hour and then look at the sales leaderboard to see my position.” said no sales person ever!

 

The Sales Dashboard

Main sales dashboard with the most important KPIs for Dynamics 365/CRM

The Sales Dashboard consists of the following charts:

1. Sales Funnel

Somehow it is not a sales dashboard if there isn’t a funnel on it. It is the universal indicator of a sales process regardless of how wrong it may be. I have done my best to overcome some of those areas.

2. Sales Leaderboard

The Sales Leaderboard is also a standard component on a sales dashboard. This one includes both won and in progress revenue. Users are ranked per the amount won. Current user is emphasized in a stronger color so it is easier to see your own position.

3. Sales KPIs about the open opportunities

KPIs include est. revenue, average value, number of opportunties in the pipeline, next est. close date and date the last opportunity was created. On the dashboard, the user can flip views between “Open Opportunities” vs. “My Open Opportunities” to get both group and personal KPIs.

4. Phase Loss Rate on Est. Revenue

This chart gives you the percentage of revenue lost per phase in the sales pipeline. For example, in the qualify stage, if opportunities est. 100 million entered the qualify stage, but only 45 million of est. revenue made it to the develop phase, then the loss rate for qualify is 65%. The same calculation for the develop phase, but of course excluding all the opportunities that never made it that far. In an ideal world, you would lose more opportunities in the initial stages and have a much higher win percentage towards the end.

Additional charts with variations on how the calculations are made are included in the zip file, including using the number of opportunities rather than the est. revenue.

5. Sales KPIs for Won Opportunities

Like the other KPI chart, but this one focusing on KPIs for the opportunities that were won.

Competitor Strength

Scroll down on the dashboard and you get to the Competitor Strength chart.

Competitor Strength for Open Opportunities in Dynamics 365/CRM

The competitor strength chart lists all your competitors, ranked by how many opportunities you have lost to them.

On top of the lost bar, you can see how many opportunities are currently in progress.

Finally, in another bar, you have the est. revenue of the open opportunities where this competitor is a threat.

Sales Pipeline Analysis

Sales Pipeline Analysis in six charts in Dynamics 365/CRM

As mentioned earlier, I have included all the pipeline analysis charts in one dashboard for review. They all work in the same manner, although the calculations have a different focus as described by their names.

Download

As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.

Sales Dashboard by CRM Chart Guy Version 0.0.0.1

Update Notes November 20, 2016.

Pipeline Charts now only uses the beginning number of the step name, so as long as your phases are 1 through 4, then you would not need to make changes. Won opportunities will also be filtered correctly and do not necessarily need to be moved to the close stage. Regardless of the stage on a won opportunity, it is assumed that it has made it through all the stages.

Added some of the pipeline charts to a separate User form to analyze an individual’s performance against how the company KPIs.

Zip file includes all the chart xml files individually and a managed and unmanaged version of the solution file for Dynamics 365.

Solution files for pre-Dynamics 365 environments are no longer included. However, all the chart xml files can be imported separately into previous versions. Presumably, all the way back to CRM2011 although I have not been able to test that.

Added a chart for the Opportunity Products to see what products are in the pipeline. Products less than 10% of the total are grouped into “Other”.

Original Solutions Notes

All the charts that use stages or the pipeline rely on the Opportunity field called Pipeline Phase. Most trial instances have data in this already. Otherwise, it is populated either manually or by a workflow. The chart also assumes that the naming of the phases is kept from the trial so they are called 1-Qualify, 2-Develop etc.

The assumption I made in the calculations is that won opportunities are in the 4-Close phase. Therefore, make sure that the process that updates the pipeline phase puts won opportunities 4-Close. They really should be in that stage anyway if won.

The solution files with Dyn365 in the name are specifically for new trials or environments that have already been upgraded. This solution also includes the Competitor Chart.

Solution files with CRM_8.0 in the name are for CRM2016 and earlier. This version does not include the competitor strength chart as there’s a bug in CRM that does not allow the distinct=true property to be imported in a solution file. This was fixed with Dynamics 365.

All the chart xml files are in the zip file as well, so you can import them separately, make modifications etc.

Thanks for reading. Hope you enjoy and please share and let me know of any good suggestions.

And don’t forget to sign up for my newsletter on this page and follow me on Twitter

, , , , , , , ,

12 Comments

Charting on Activity Parties in MS Dynamics CRM

The Activity Party entity surfaced a little in the MS CRM blogosphere late last year.

First Jukkan wrote this excellent piece “Your Interaction Network in Dynamics CRM”

And then Joel Lindstrom and George Doubinski talked a little about the challenges of reporting on the activity parties in episode 14 of the CRM Audio podcast called “SSRS is Dead to George”.

While you can chart on activities with the chart editor and Excel, there are two questions you cannot answer without some xml customizations. I will use appointments as an example throughout this blog post although you can apply this to any of the activity types.

The two questions are:

1. How many people are included in each appointment?
Count of Attendees per Appointment - MS Dynamics CRM

2. How many appointments are CRM users included in?
Count of Appointments per User - MS Dynamics CRM Chart

This information is stored within the activity party table, which keeps track of all the contacts, users etc. that you add to the required and optional fields on the appointment form. It also keeps track of the owner and organizer.

A user can create their own appointments, but they can also be required attendees in other users’ appointments. That means you can have two or more users involved in a sales meeting, but in a normal chart, only the owner of the appointment would get “points” for the sales activity. If you are having regular sales meetings with multiple participants or if you use appointments to keep track of who attended what event, it can be useful to know how many people were actually involved in each of the appointments.

101 on Activity Parties

While I do urge you to read Jukkan’s blog on activity parties, here’s an ultra-short 101 in case you didn’t.

The Activity Party works as a manual N:N relationship between any activity and the people participating and what their participation role is.

If user John Smith is the owner of an appointment, and a required attendee, he will have two activity party records between his one user record and the one appointment record.

The official documentation on activity parties can be found here. ActivityParty entity details on MSDN

How many people per appointment chart

We need to create and download two pieces of xml before we open up Notepad ++ and start our chart xml editing.

1. A base chart we can build on.

Attendees Base Chart in MS CRM

2. A FetchXML from Advanced Find which includes details on the attendees we want to include.

Advanced Find link to activity party in MS Dynamics CRM for chart customization

In Advanced Find, select the participation types you want to include.

Participation Types selection in Advanced Find

Download the FetchXML.

Download Fetchxml button in Advanced Find

Open the FetchXML and find the <link-entity> section. Highlighted below.

Fetch downloaded from Advanced Find in MS Dynamics CRM for chart customization

Export and open the chart xml from the base chart and get ready for some editing.

In the xml for the base chart, locate the line with the aggregate in the fetchcollection. As usual I have renamed the aliases to something sensible.

Original fetch from the base chart with aggregate line highlighted

Now we need to edit the fetch, so that the <link-entity> section downloaded from Advanced Find goes inside the fetchcollection and that the aggregate ends up inside that section.

MS CRM Chart fetchcollection modified with link to Activity Party entity.

Note I did some other changes to the aggregate too.

  • Replaced name=”statecode” with name=”partyid” which refers to the attendee record. The statecode came from the field selected in the chart editor.
  • Added distinct=”true”. We only want to count people once.
  • Aggregate was changed to countcolumn so we only include records with a value.

Import the fetch and have a look.

Count of Attendees by Appointment - Final chart after customizing the chart xml in MS Dynamics CRM

 

How many appointments per user chart

Not all sales people are lone wolves. Some are pack hunters and go to meetings together. However, in standard CRM charts, only the owner of the appointment will get “points” for attending. We need to make sure all users are accounted for, and of course make sure that each user is only counted once. No double dipping by putting yourself in both the owner and the attendees’ fields.

The process is very similar as above, but we will need to create an extra <link-entity> from the activity party to the user entity so we can group by it.

Again, build this in Advanced Find and download the FetchXML. Include a lookup to the users to ensure only users are counted.

Advanced Find - by User - additional link to User entity

Modify the fetchcollection in your chart xml to include the added <link-entity> to the users.

Appointments per User fetchxml modified. Dynamics CRM chart customization

Other changes:

  • Aggregate is activityid rather than partyid since we are now counting appointments, not participants
  • Groupby was moved to the new link-entity section and is now on the user
  • Aliases updated to fit this scenario better

Import the chart and you’ll see a count of appointments per user.

Count of Appointments per User chart in Dynamics CRM - Chart customization XML

 

Bonus Chart – Count by any Party

Since any party is worth counting, we can skip the additional link-entity and group directly on the partyid. By doing this you get a mix of record types involved in the appointments so you can compare participation between Accounts, Leads, Contacts and Users.

Bonus chart for coounting the number of appointments for all party types. Fetchxml modified for import to a Dynamics CRM Chart.

Here is the chart after import.

Bonus Chart - Count of Appointments by all party types. MS Dynamics CRM Chart customization.

This is clearly candidate for a Top X chart. Read more about those here.

Thanks for reading. Please sign up for my newsletter and follow me on Twitter

, , ,

6 Comments

Compare This Year to Last Year with a Dynamics CRM chart

Comparing sales performance this period vs. the same period last year is a fairly standard metric. However, to create a chart that compares the period this year, with last years in the same date range can be tricky. For example, today is June 21st. I want to see my actual sales for this year to date, compared to my actual sales last year, until June 21st. Or Year to Date vs. Last Year to Date (YTD vs. LYTD).

YTD vs LYTD MS Dynamics CRM Chart Year to Data vs Last Yeard to Date

Or maybe I want to compare my sales this year and last year by month.

Chart compare to last year by month overlapped MS Dynamics CRM chart after customizing xml

In this post I will detail how I created both of these charts.

Compare YTD to LYTD in MS Dynamics CRM

Before I start to create the chart, I need to get some components ready first.

These are:

  • View of Won Opportunities – This and Last Year
  • Fetchxml filter for YTD
  • Fetchxml filter for LYTD

Create the View for the chart

Open Advanced Find on opportunities and create the following view:

Advanced Find - View Won This AND Last Year

Name it “Won Opportunities: This And Last Year.” Always use this view with the charts in this post.

After saving it, keep Advanced Find open and use it to create the fetchxml filters we need to modify the chart xml with.

Create the following filters and download the fetchxml. You can download the fetchxml without saving the View first.

Filter for YTD

Advanced Find - This Year

 Filter for LYTD

Advanced Find - Last Year and Older Than 1 Year

Using the Older Than X Years will make sure we only get the LYTD date range for the won opportunities.

Note: Are you on a version earlier than CRM2015, you can use “Older Than X Months”, with X being 12, and get the same result.

Chart Editor

Time to open up the chart editor.

Save a chart similar to the settings here.

Chart Editor to create YTD vs LYTD in MS Dynamics CRM

The two series for actual revenue, is what will be modified to YTD and LYTD using the filters we just downloaded from Advanced Find.

Now the chart xml is ready to export and we can have some fun!

Fetchxml edits

The two attributes for the actual value series will need to be edited so they use the filters we just downloaded.

xml original attrbiutes

Here is the fetchcollection with the additions made and the filters downloaded from Advanced Find highlighted in yellow.

      <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" name="statecode" /> 			<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" /> 				  <filter> 					  <condition attribute="actualclosedate" operator="last-year" /> 					  <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> 				  </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" /> 				  <filter> 					   <condition attribute="actualclosedate" operator="this-year" /> 				  </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>

Fetchcollection edits:

  • Created link-entity outer joins so the series only includes the filtered data.
  • Added the YTD and LYTD filters downloaded from Advanced Find.
  • Renamed the aliases from _CRMAutoGen_aggregate_column_Num_0 or similar to more sensible names.
    • If renaming the aliases, remember to update them in the categorycollection too.

Other changes:

     </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </measurecollection>         </category>       </categorycollection>     </datadefinition>   </datadescription>   <presentationdescription>     <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">       <Series>         <Series XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </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 Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>

  • Make sure the series are in the same order in both the categorycollection and in the series section
  • Custom LegendText
  • Custom LabelFormat
  • Format the Y axis numbers

The categorycollection is where you can adjust if YTD or LYTD should come first. In this example YTD comes first.

The new formatting is designed to show amounts in the thousands with negative and zero values suppressed. See this post for details on chart labels and formatting.

Import the XML and this should be your result.

YTD vs LYTD chart in MS Dynamics CRM. Custom chart xml.

Here is the chart and we can see how our performance is compared to the same period last year.

If you would like to see the data grouped by something else, such as customer or owner, you can open the editor and change the category.

Change GroupBy in editor for YTS vs LYTD chart, custom chart xml in MS Dynamics CRM

Just keep in mind that if you make any changes to the legend entries you will lose your xml customizations.

Compare to Last Year by Month

For better insights on our sales development, we can group the sales by month instead.

Start with the chart we just made and use the editor to change the groupby to Actual Close Date and Month.

Category Month

Click Save As and save the chart under a different name.

The result should look like this.

Compare to Last Year Month after changing groupby in chart editor.

Now we see the same data spread out per month instead. What I would like to do is include all of last years data and then have the month columns next to each other, so March of this year is next March of last year, etc.

In short, what I will do is remove the Older-Than-1-Year filter so we get all of last year. Next I will split the data onto two separate X axes. One for each year and set the chart to display them in sync with each other so the months line up.

Note: We are working with data by month. If data points are missing for a certain month, that month will not be included. For this chart, we are relying on having 12 data points for each year. If you do not have a data point for each  of the past months, then use dummy records to ensure a data point exists for the missing months. These can be of zero value and will not interfere with the calculations. You do not need records for future months.

Update filter and add secondary X axis

Export the chart xml.

First remove the “older than” clause in the fetchcollection.

              <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>

Now scroll down and add the secondary X axis. Both in the Series and Axis section.

<visualization>   <visualizationid></visualizationid>   <name>Compare to Last Year by Month - overlap axis</name>   <primaryentitytypecode>opportunity</primaryentitytypecode>   <datadescription>     <datadefinition>       <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" dategrouping="month" name="actualclosedate" />             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="this-year" />               </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </measurecollection>         </category>       </categorycollection>     </datadefinition>   </datadescription>   <presentationdescription>     <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">       <Series>         <Series XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </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 Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>           <AxisX Minimum="0" Maximum="12.5" IntervalOffset="1" Interval="1" 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>           <AxisX2 Minimum="12" Maximum="24.5" IntervalOffset="1" Interval="1" 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" />           </AxisX2>		           </ChartArea>       </ChartAreas>       <Titles>         <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />       </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>

I added XAxisType=”Secondary” to the series for this year to indicate which series should go on the secondary X axis.

The AxisX2 section is identical to AxisX except for the “2” so you can copy it and add the number.

Import the chart xml back into MS Dynamics CRM.

Month over Month 2nd X axis added in chart xml

Notice that the secondary X axis has been added to the top of the chart. The blue columns are actually bound to the top (secondary axis) and the orange are on the bottom axis.

Overlap axes

Next step is to make the two axes overlap so it is easy to compare a month this year, to the same month last year.

We have 24 data points. One for each month of the two years. The series for last year is displayed on Axis 1 and should show points 0 – 12 (Jan-Dec of 2014). Axis 2 has the data for this year and should show points 12-24 (Jan-Dec of 2015).

To achieve that, I add Minimum and Maximum values to each axis so they are aligned.

Here are the chart xml axis edits.

<visualization>   <visualizationid></visualizationid>   <name>Compare to Last Year by Month - overlap axis</name>   <primaryentitytypecode>opportunity</primaryentitytypecode>   <datadescription>     <datadefinition>       <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" dategrouping="month" name="actualclosedate" />             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="this-year" />               </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </measurecollection>         </category>       </categorycollection>     </datadefinition>   </datadescription>   <presentationdescription>     <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">       <Series>         <Series XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </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 Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>           <AxisX Minimum="0" Maximum="12.5" IntervalOffset="1" Interval="1" 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>           <AxisX2 Minimum="12" Maximum="24.5" IntervalOffset="1" Interval="1" 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" />           </AxisX2>		           </ChartArea>       </ChartAreas>       <Titles>         <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />       </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>

IntervalOffset=”1″ is added so the axis does not show the zero value (i.e. Dec 2014 on the 2015 axis).
Interval=”1″ is added to make the intervals consistent between the two axes and show every month.

Notice the maximums are set to 12.5 and 24.5. That is to make sure the data for the last month is displayed in full. If set to 12 and 24 only half the column would display.

Import the xml and this should be the final result.

Chart compare to last year by month overlapped. Chart xml custom MS Dynamics CRM

The chart type on Last Year can be changed to Line, ChartType=”Line”, to get this result.

Compare to Last Year Line chart. chart xml MS CRM

As always, hope you enjoyed the post and found something useful. Please follow me on Twitter or sign up for the newsletter to be alerted on new posts.

 

 

 

, , , , ,

40 Comments

Add Key Figures to Dashboards in MS Dynamics CRM

One of the most common questions for sales dashboards in MS CRM is “can I have a total on that Sales Pipeline funnel chart?”

Seems like a fair question, but somehow this not possible. At least not in a manner where the number can be formatted in a way that can be read properly.

My preferred method, is to create a chart that shows nothing but the key figures. Total sum of opportunities, the average amount of the estimated revenue, how many opportunities there are, next estimated close date, etc.

The chart itself can look like this.

MS Dynamics CRM key figures total, sum, average, next est. close date. CRM chart custom xml.

And here, put in context on a sales dashboard on top of the pipeline funnel.

Key Figures in Context of Sales Dashboard MS CRM. Chart component with custom chart xml.

How to create the Key Figures Chart in MS Dynamics CRM

As usual, start the chart editor so it can do the majority of the work.

1 CRM Chart editor to set up key figures. MS Dynamics CRM chart editor. Create base chart for furhter xml customization.

I have added a series for each of the key figures I want the chart to show.

These are all of the chart type bar. For the dates, Est. Close Date and Created On, only the count aggregates are available. We will need to replace that later in the chart xml for minimum and maximum date values.

For the category I selected Status. My view for this only includes active opportunities, and for this type of chart I need all the opportunities to fall in to the same category. In this case it is the active statuses.

Edit the chart XML

Export the xml and open it in your editor. Notepad++ is still my go to xml editor.

In the fetchxml section, the date values will need to be replaced from the countcolumn aggregates.

2 Fetchxml edit the aggregates for the date to min and max. MS Dynamics CRM charts xml.

In the example I have also renamed all of the aliases, so it is easier to see which attribute is what. If you do this in your xml too, remember to update the aliases in the categorycollection as well.

The series that displays the next Est. Close Date is set to “min”, giving us the lowest value of the dates which will be the next date an opportunity is supposed to close.
The series for the date of the latest opportunity that was created is set to “max”, which will give us the highest value in the set.

If you import the xml now, it should look like this.

3 first import of chart xml - Have all key figures, prior to editing layout in the MS CRM chart xml.

Good. All the values we want are now in the chart including dates on the bottom two bars.

Now for some major clean up as we do not need the axes, titles or legends for this. They do more harm than good in this case.

4 Remove unwanted sections from the chart xml in MS Dynamics CRM.

Click to enlarge. Yellow sections removed. Green section added.

 

Remove

  • AxisY2 section
    • And YAxisType=”Secondary” from one of the series
  • Titles section
  • Legends section

Add

  • Enabled=”False” to AxisY
  • Maximum=”1″ to AxisY – this is to help with alignment of the text
  • Enabled=”False” to AxisX

Edit the presentationdescription xml

We are now ready to work on the presentationdescription of the xml. This is where we set the labels, formatting and colors.

We will need to edit both the normal properties and the CustomProperties for this chart.

First we will edit the colors and labels.

Standard properties in the presentation description modified in hte chart xml in MS CRM.

Click to enlarge. Yellow sections modified.

 

  • Added Color=”Transparent” to each series to make the bar invisible. We only want the label.
  • IsValueShownAsLabel=”True” for sum, average and count as these are numbers.
  • IsValueShownAsLabel=”False” for the date values as we need to use keywords to display these better.
  • Font size has been increased for each series.
  • LabelFormat for sum, average and count values to add description.
  • Label for each of the date values with the #VALY keyword to insert the date without time.
  • Notice there are some extra spaces in front of the date values. It takes a little tinkering with these to get the alignment right on the final chart.

You can read more on label formatting in this post LabelFormat Cheat Sheet

The CustomProperties

Edit custom properties in the chart xml for MS Dynamics CRM

Click to enlarge. Yellow sections modified.

 

  • Added BarLabelStyle=Left to each series to help with alignment.
  • Adjusted PixelPointWidth for each series to accommodate the larger font sizes set earlier.
  • Adjusted MaxPixelPointWidth for each series due to the larger font sizes.

Import and here’s the final chart again.

Microsoft Dynamics CRM chart with important key figures for Opportunities.

One chart with all four key figures inside one chart component to put on a dashboard.

If you need more, the MS CRM chart xml will support up to nine key figures added in this manner.

Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter

Here’s the xml file. As usual, provided for fun and games only.

<visualization>
  <visualizationid></visualizationid>
  <name>Key Figures - by CRM Chart Guy</name>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="statecode" name="statecode" />
            <attribute alias="SumTotal" name="estimatedvalue" aggregate="sum" />
            <attribute alias="Average" name="estimatedvalue" aggregate="avg" />
            <attribute alias="CountOfOpp" name="estimatedvalue" aggregate="count" />
            <attribute alias="NextCloseDate" name="estimatedclosedate" aggregate="min" />
            <attribute alias="LastCreated" name="createdon" aggregate="max" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="statecode">
          <measurecollection>
            <measure alias="SumTotal" />
          </measurecollection>
          <measurecollection>
            <measure alias="Average" />
          </measurecollection>
          <measurecollection>
            <measure alias="CountOfOpp" />
          </measurecollection>
          <measurecollection>
            <measure alias="NextCloseDate" />
          </measurecollection>
          <measurecollection>
            <measure alias="LastCreated" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">
      <Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 30px" LabelFormat="Sum of Opportunities #,#,k" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=150, MaxPixelPointWidth=5000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 28px" LabelFormat="Average amount #,#,k" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=150, MaxPixelPointWidth=5000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 26px" LabelFormat="Number of Opportunities #,#" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="False" Font="{0}, 20px" Label=" Next Est. Close Date #VALY" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="False" Font="{0}, 15px" Label="  Latest Opportunity Created #VALY" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY Enabled="False" Maximum="1" 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 Enabled="False" 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>
        </ChartArea>
      </ChartAreas>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

Thanks for reading!

, , , , ,

27 Comments

Display only complete weeks or months in charts

Here’s an issue that can be rather annoying. You want to display activities by week. You set up your chart and have a view that includes data from the last 5 weeks.

MS CRM chart with incomplete week highlighted

Incomplete week highlighted in chart

The only problem is, that a view showing the last 5 weeks doesn’t show the last 5 weeks. It is showing you the last 5 x 7 days, or 35 days. If those 35 days end on a Thursday, your chart, showing you information by week, will only have data from two days of that week. In other words, the chart will (inaccurately) present you as a slacker that week. We all know that is not the case, so we will need to make sure that those charts have an accurate representation of weekly data.

To correct this, I will create a view with 6 weeks of data, but limit the chart to show only the last 5 weeks. That will ensure that all columns in the chart represent a complete week’s data.

Note: This approach also works for months. However, if your fiscal periods are aligned with months, a View with the last 5 fiscal periods, will actually be the last 5 full months. A view with the last 5 months, will be the last 5 x 30 days = 150 days.

First, I’ll create the weekly chart in the editor and then export the chart xml.

I’ll start by adding an order clause, and reverse the standard order. The descending=”true”, means the most recent week will now be the first item, rather than the last.

Add order clause to MS CRM chart xml. order alias="start" descending="true"

To remove the sixth straggler week, I’ll add Maximum=”5.75” to the Axis X. This will ensure that I am only displaying the first 5 items and not the 6th. This is why I had to change the order of the weeks.

Maximum added to X axis in the MS CRM Chart xml

Why set the maximum at 5.75 and not just 5. If set to 5, the maximum property would cut the chart exactly at 5, which would be the middle of the 5th column at the interval mark, highlighted below. If maximum was 5, only half a column would be displayed.

MS CRM Chart activities by week, displaying only complete weeks by modifying the chart xml.

Now we have a chart where all the weeks have a complete set of data. The exception of course, is the current week, which depending on the day might not be complete. That however, makes much more sense to the user.

If you want the oldest week to the left, add IsReversed=”True” to the X axis properties.

X Axis is reverased by adding IsReversed="true" to the xml for the MS Dynamics CRM chart.

Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , , ,

4 Comments

Is your “Regarding” field on activities set correctly?

MS Dynamics CRM gives you a plethora of options for setting your activities regarding a specific record. Most companies have guidelines on which entity certain activities should be set to. For example, an often recommended approach is to set activities regarding the opportunity it is about. However, if a sales person simply clicks on the track button in Outlook, rather than “Set Regarding,” and go through the process of selecting the right record, the email will be associated with the contact, not the opportunity. In the defense of sales people, clicking the track button does seem like the obvious thing to do.

In order to follow up on whether or not activities are set regarding to the correct entity, we can create a chart that show the count of activities per type, and which entity they have associated them with.

MS Dynamics CRM chart - Activities by regarding entity.

The chart tells us which type of entities the users are focusing on. There should be a healthy balance between activities on leads and opportunities depending on how you are using MS CRM.

Create an “Activities View” with the needed entities

First, open up Advanced Find and select the Activities entity and open Edit Columns.

Open the Record Type option set and scroll down to the section that begins with Regarding.

Add Columns with Regarding entity to MS Dynamics CRM for chart customization.

This is a list of all the entities that activities can refer to.

Add a column for each of the entities that you would like to include in your chart.

After you select a regarding entity, you have to select a field. Choose a field that you know always is populated. I like to use the field “Created On”.

View with Regarding: Created on field for each if the chosen entities in MS Dynamics CRM. CRM Chart.

To this view I have added the “Created On” field for Leads, Accounts, Contacts and Opportunities. Each column will be populated with the “Created On” date. The view in itself, is not very helpful, but the important part is that there is only data in the Account column, if the Activity is related to an Account. I cannot see which is which, but that is OK for now.

View with data Regarding Created On in MS Dynamics CRM.

Save your view and navigate back to the activities section in MS Dynamics CRM and open your new view.

Use chart editor to create the base

Open the chart editor and start adding the “Regarding” fields to your legend entries. If we had not added the fields to the view first, they would not have been accessible in the chart editor.

Chart editor with Regarding fields added to prepare for xml chart customizations in MS Dynamics CRM. CRM Charts.

Make sure that the aggregate is set to Count:Non-empty, so we only count the records that have a value in that field. This means the chart only counts for the field when the activity is regarding an entity of that type.

Set the category to either Activity Type or Owner depending on your needs.

Chart from editor before xml edits. Activity by entity and type in MS Dynamics CRM. CRM chart customizations.

Now we have a chart that shows us the amount of activities per type and the regarding entity.

Clean up chart xml

Unfortunately, not even the person who made it can decipher which series belongs to which entity, so we’ll export the CRM chart xml and do some clean up.

First, we now have the almost mandatory step of removing the secondary Y axis.

Find this sucker Y Axis Type Secondary MS Dynamics CRM chart customizations.in one or more of the series and delete it.

For good measure, although not required, remove the AxisY2 section.

AxisY2 section to be removed from MS Dynamics CRM chart. Customization in the chart XML.

Luckily we do not have to remember the order of how we added the different entities. That information is present in the <fetchcollection>.

fetchcollection for MS Dynamics CRM chart. Adjusted alias for each entity for CRM chart customization.

I’ve renamed the aliases in the example above so it is easier to read. If you do that, remember to update them in the <measurecollection> as well.

measurecollection for MS CRM chart xml. Alias renamed according to entity names.

Finally, in the series, add the LegendText so it will make sense to the user. The series follow the same order as the <measurecollection>.

Series with LegendText. Added to MS CRM Chart xml.

Import the chart back in to MS Dynamics CRM.

Final Chart after chart xml modifications. MS Dynamics CRM activities per entity and per type chart.

 

While the example used Activity Type as the category, this can easily be replaced with Owner, to see which entities users focus their activities on. It is also a very good indicator if users set the Regarding field according to the company guidelines.

Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , ,

3 Comments

Multiple entities in one MS CRM chart

For a complete overview of records owned by a user, it can be beneficial to see the count of leads, opportunities, accounts and contacts next to each other. For example, you can then ask the question; are the amount of opportunities owned appropriate compared to the amount of leads and accounts owned?

Out-of the-box, MS CRM does not provide an option to compare different entities in one chart. The solution is often to make one chart for leads, one chart for opportunities etc. and put those charts next to each other in a dashboard. However, since the Y-axis is updated dynamically, eyeballing the relative difference can be challenging.

With some XML modifications to the MS CRM chart, we can fortunately add multiple entities to one chart and get that instant overview of records owned per user.

Multiple entities in one MS CRM chart. Accounts, Leads, Opportunities, Contacts count for each User via chart xml modification.

But wait a minute. MS CRM does not support charting on multiple entities! That’s right, it doesn’t. The chart above is actually based on the user entity, checking the amount of records owned by each user.

This chart modification is possible in both CRM2011 and CRM2013.

User overview of Leads, Opportunities, Accounts and Contacts

Let’s get started. First create an xml file we can work with. Bring up a user view and open the chart editor. Add one series as a placeholder for each of the entities you want to chart for the users.

Chart Designer for creating User chart to count Accounts, Leads, Contact, Opportunities in one chart with multiple entities.

I’ve added four series (leads, opportunities, accounts and contacts), and selected Count:Non-empty as the aggregate. I have selected the user field in this example, but it’s just a placeholder for now.

Save the chart, export the xml and open it in your favorite editor. I still use Notepad++.

Locate the attributes in the fetchcollection that has the countcolumn aggregate. These are the lines that must be replaced.

fetchcollection from base xml - MS CRM chart for multiple entities in one chart.

I will start with the adding a count of leads owned by the user. Replace the first of countcolumn aggregates, the whole line, with the following code.

<link-entity name="lead" from="owninguser" to="systemuserid" link-type="outer">
	<link-entity name="lead" from="leadid" to="leadid" link-type="outer">
		<attribute alias="OpenLeads" name="leadid" aggregate="countcolumn" distinct="true" />
		<filter type="and">
		  <condition attribute="statecode" operator="eq" value="0" />
		</filter>
	</link-entity>
</link-entity>

This is the part that goes from the user record, to the lead entity and counts all the leads the user own.

Notes on the XML

  • First <link-entity> finds the lead entity via the “owninguser” field.
  • Second <link-entity> is self-referential which enables us to add a filter and only count records meeting a specific criteria.
  • The aggregate must be “countcolumn”. Using the “count” aggregate will return an inaccurate amount.
  • The distinct=”true” must be added.  Leaving out distinct=”true” will return an inaccurate amount.
  • Filter and condition can be taken directly from an exported fetchXML, so use advanced find to create them for you.
  • The use of link-type=”outer” is required. If link-type=”outer” is removed from the first line, the chart will only include users that have a lead record. A user with no leads would then not appear in the chart at all. This can be an alternate method to ensure that users with no records still appear in charts.

Repeat this step for each of the entities you want included, but modify the chart xml to point to the correct entity and field. Also check for any YAxisType=”Secondary” in the series and remove these.

Note that if you rename the alias, the same alias must be used in the measurecollection. In the example above the alias has been changed to “OpenLeads”.

And that’s it. I have included a full sample chart xml at the bottom of the post for reference.

A reader has previously pointed out that during a solution import, MS CRM will throw an error when using distinct=”true”. However, there are no issues importing the chart directly. Thanks for the heads up Martin.

Campaigns

The same method can be applied to campaigns, so we can have leads and opportunities generated by the campaign on the same chart.

We are not limited to the “countcolumn” aggregate. We can also use “sum”, so we can include actual and estimated revenue.

A chart of campaigns could include

  • Count of active leads
  • Count of open opportunities
  • Sum of estimated revenue on open opportunities
  • Sum of actual revenue on won opportunities

Here’s how the <link-entity> and aggregate looks when starting from the campaign. The sum aggregate refers to the estimated value field on the opportunity. The filter ensures only open opportunities are summed.

<link-entity name="opportunity" from="campaignid" to="campaignid" link-type="outer">
	<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
		  <attribute alias="EstValue" name="estimatedvalue" aggregate="sum" distinct="true" />
		  <filter type="and">
			<condition attribute="statecode" operator="eq" value="0" />
		  </filter>
	</link-entity>
</link-entity>

Since we are mixing countcolumn and sum on this chart, we must manually make sure that the currency fields are all on the secondary axis, by adding YAxisType=”Secondary” to each of those series. The chart editor has probably already added a secondary Y-axis when you added the multiple series, so just make sure the correct series go on the correct axis.

Note: While I am showing the use of SUM in this example, this is generally bad idea in combination with the distinct=”true” property. Since only unique values are included, two opportunities with an est. revenue of 10,000, would be summed as 10,000 instead of 20,000. Make sure your values are unique if you use this approach.

The chart type StackedBar can also be used instead of Bar for all of the series. That way we can see the full potential of the campaign.

Campaign Chart with both Lead and Opportunity information in one chart.

Campaign chart showing the combined amount of leads and opportunities generated. Actual and estimated revenue are stacked to indicate full potential of campaign. Notice that campaigns with no leads or opportunities generated still appear in the chart.

Some considerations

There are no drill-down charts modified in this manner. On a user chart, any attempt at drilling down, will only show the user records. Not the leads or opportunities, etc.
It is fairly easy to hit the max record limit of 50,000 since all related records count. A user with 49,999 inactive leads, and 2 active, will go over the limit even if the chart is filtered to only show active leads. This is because the inactive leads are still processed, so only include the users you really need or divide them into smaller groups if you hit the max limit.

Hope you liked the post. Please sign up for the email newsletter and follow me on Twitter  

Sample chart

Sample is provided for test purposes only. Do not use in a production environment without proper testing.

<visualization>
  <visualizationid></visualizationid>
  <name>CRMChartGuy's Leads, Opportunities, Contacts and Accounts by User</name>
  <!--Sample chart provided for demonstration purposes only. For more details see www.crmchartguy.wordpress.com -->
  <primaryentitytypecode>systemuser</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="systemuser">
            <attribute name="systemuserid" groupby="true" alias="User" />
		<link-entity name="lead" from="owninguser" to="systemuserid" link-type="outer">
		  <link-entity name="lead" from="leadid" to="leadid" link-type="outer">
		  <attribute alias="OpenLeads" name="leadid" aggregate="countcolumn" distinct="true" />
			<filter type="and">
			  <condition attribute="statecode" operator="eq" value="0" />
			</filter>
		  </link-entity>
		</link-entity>          
		<link-entity name="opportunity" from="owninguser" to="systemuserid" link-type="outer">
			<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
				  <attribute alias="OpenOpportunities" name="opportunityid" aggregate="countcolumn" distinct="true" />
				  <filter type="and">
					<condition attribute="statecode" operator="eq" value="0" />
				  </filter>
			</link-entity>
		</link-entity>
		<link-entity name="account" from="owninguser" to="systemuserid" link-type="outer">
			<link-entity name="account" from="accountid" to="accountid" link-type="outer">
				<attribute alias="Accounts" name="accountid" aggregate="countcolumn" distinct="true" />
				<filter type="and">
				  <condition attribute="statecode" operator="eq" value="0" />
				</filter>
			</link-entity>
		</link-entity>		
		<link-entity name="contact" from="owninguser" to="systemuserid" link-type="outer">
			<link-entity name="contact" from="contactid" to="contactid" link-type="outer">
				  <attribute alias="Contacts" name="contactid" aggregate="countcolumn" distinct="true" />
				  <filter type="and">
					<condition attribute="statecode" operator="eq" value="0" />
				  </filter>
			</link-entity>
		</link-entity>				
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="User">
          <measurecollection>
            <measure alias="OpenLeads" />
          </measurecollection>
          <measurecollection>
            <measure alias="OpenOpportunities" />
          </measurecollection>
          <measurecollection>
            <measure alias="Accounts" />
          </measurecollection>		
          <measurecollection>
            <measure alias="Contacts" />
          </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="Bar" IsValueShownAsLabel="true" LegendText="Open Leads" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Open Opportunities" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Accounts" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />	
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Contacts" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />			
      </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>
        </ChartArea>
      </ChartAreas>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />
      </Titles>
      <Legends>
        <Legend Alignment="Center" LegendItemOrder="ReversedSeriesOrder" 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>

, , ,

27 Comments