Posts Tagged CRM 2011 Chart

All things Pie Chart in Dynamics CRM

Somehow I have managed not to write about the Pie Chart in Microsoft Dynamics CRM. What you can do with the pie chart is fairly limited since only one series is supported.

However, there are a number of custom properties we can use to tweak the appearance of the pie chart.

Custom properties can be found at the end of the series properties. Note that unlike standard properties, custom properties are all added within the quotations of the CustomProperties.

CustomProperties in Pie Chart XML for Dynamics CRM

The custom properties we can use on a pie chart in Dynamics CRM are:

  • PieDrawingStyle
  • PieStartAngle
  • PieLabelStyle
  • LabelsHorizontalLineSize
  • LabelsRadialLineSize
  • PieLineColor
  • MinimumRelativePieSize
  • CollectedThreshold
  • CollectedThresholdUserPercent
  • CollectedColor
  • CollectedSliceExploded
  • CollectedLabel
  • CollectedLegendText
  • CollectedToolTip

I’ll go through each of these properties and add some notes along the way. There’s an XML sample of a series at the bottom of the post with all the custom properties added.

PieDrawingStyle

  • Default (Default)
  • SoftEdge
  • Concave

Changes the appearance of the pie chart. Yes, the default value is called Default.

Dynamics CRM chart. Chart type Pie. PieDrawingStyle Default

PieDrawingStyle=Default

Dynamics CRM chart. Chart type Pie. PieDrawingStyle SoftEdge

PieDrawingStyle=SoftEdge

Dynamics CRM chart. Chart type Pie. PieDrawingStyle Concave

PieDrawingStyle=Concave

For simply changing the appearance, another option is to change the Area3DStyle to true. Although this is not a custom property.

Dynamics CRM chart. Chart type Pie. 3D enabled

3D Enabled

PieStartAngle

The PieStartAngle is a value between 0 and 360 degrees depending on where you want the first value to start. Microsoft Dynamics CRM pie charts by default start in the 3 o’clock position. That means the 3 o’clock position is 0 degrees and any changes have to take that into account.

Dynamics CRM chart. Chart type Pie. PieStartAngle 270 degrees Custom Property

PieStartAngle=270 start the first slice in the 12 o’clock position

PieLabelStyle

  • Inside (Default)
  • Outside
  • Disabled

PieLabelStyle moves the label inside, outside or disables them completely.

Dynamics CRM chart. Chart type Pie. PieLabelStyle Inside Default

PieLabelStyle=Inside

Dynamics CRM chart. Chart type Pie. PieLabelStyle Outside

PieLabelStyle=Outside

The following four custom properties only apply to pie charts where the PieLabelStyle is set to outside.

LabelsHorizontalLineSize
Specifies the size of the horizontal segment of the callout line, as a percentage of the chart area.
Value range 0-100. Default is 1. Set to 0 to hide it.

LabelRadialLineSize
Specifies the size of the radial segment of the callout line, as a percentage of the chart area.
Value range 0-100. Default is 1. Set to 0 to hide it.

PieLineColor
Any named color, RGB, ARGB or HEX value can be used. See more about colors in MS Dynamics CRM charts here. Default is Black.

MinimumRelativePieSize
Specifies the minimum pie size. Value range 10 – 70. Default is 30. This property has no effect unless the outside label style is used.

Labels and Keywords

When creating a pie chart, the IsValueShownAsLabel=”True” is already added to the chart XML by default. This is what shows the values inside the chart. These values can be formatted by adding the LabelFormat property to the series. Go here for formatting of values in CRM charts.

If you want to customize the content of the label, IsValueShownAsLabel must be set to “False”.

The two properties on a standard pie charts that can hold custom text and keywords are Label and LegendToolTip. Both of these properties must be added in the series. This is included in the sample XML series at the bottom of post.

Here are the keywords that can be used in pie charts. They should be rather self-explanatory. While the last three work, they are not very useful on a pie chart.

  • #LEGENDTEXT
  • #VAL
  • #PERCENT
  • #TOTAL
  • #MIN
  • #MAX
  • #FIRST
  • #LAST
  • #INDEX

Here’s a sample of what the Label and LegendToolTip could look like with keywords added. You can combine both text and keywords as needed.

Label="#LEGENDTEXT #PERCENT  -  #VAL out of #TOTAL total" 
LegendToolTip="#LEGENDTEXT #PERCENT  -  #VAL out of #TOTAL total"

And here is how the pie chart looks with the Label.

Label with Keywords. Dynamics CRM chart. Chart type Pie.

Label=”#LEGENDTEXT #PERCENT – #VAL out of #TOTAL total”

And with the LegendToolTip.

LegendToolTip with Keywords. Dynamics CRM chart. Chart type Pie.

LegendToolTip=”#LEGENDTEXT #PERCENT – #VAL out of #TOTAL total

The LegendToolTip only appears when the mouse hovers over the legend item. Unless changed, the tool tip will show the percentage by default. It’s redundant to show the legend text here, but the option is there.

Label, LegendText, and keywords can be used in the series of other chart types too.

CollectedThreshold

A lot of the pie chart’s custom properties are associated with the CollectedThreshold. The feature combines smaller values into one larger pie slice. This can be useful especially when you have a lot of smaller values  that you can’t tell apart anyway.

When using the CollectedThreshold, you must set IsValueShownAsLabel to False. Otherwise an incorrect value will be shown for the collected slice. Only the value of the first collected item will then be shown.

CollectedThreshold
Any value lower than the CollectedThreshold will be be in the collected slice.

CollectedThresholdUsePercent
If set to true, CollectedThreshold must be between 0-100 and is then defined as a percentage.

CollectedColor
Can be any named color, RGB, ARGB or HEX value. More on colors in CRM charts here.

CollectedSliceExploded
If true, then the collected slice is exploded.

CollectedLabel
Your label for the collected slice. Supports keywords.

CollectedLegendText
The text in the legend. Supports keywords. Customized legend text is only supported for the collected item.

CollectedToolTip
Text in the tool tip for the collected slice. Supports keywords.

Dynamics CRM chart. Chart type Pie. Collected Options used in custom properties

Using collected properties.

Notes on collected

Keyword #MIN is now the collected value, so it cannot be used to show what the minimum value inside the CollectedThreshold is.

Add keyword #TOTAL only to the CollectedLegendText and the total value will appear only once on the chart.

Include a description of how the CollectedThreshold is defined in the either the CollectedLabel or the CollectedLegendText. That way, the user can better decode the information..

Stuff we unfortunately cannot do with Pie Charts in Dynamics CRM

  • LegendText – can’t be changed. Except for collected
  • ToolTip – have no effect
  • LabelToolTip – have no effect
  • Exploded – custom property, have no effect.
  • Keyword values cannot be formatted

Yes, you cannot add formatting to keywords, even though the documentation says you can. You’ll get a chart rendering error in Microsoft Dynamics CRM if you try to add it. This makes keywords pretty useless when dealing with large numbers. I have reported the issue to Microsoft, so if you have a minute, please go vote for the case here, and hopefully we can soon unlock even more chart features.

Note that with IsValueShownAsLabel=”True” label formatting in Dynamics CRM charts can still be used.

The documentation for all the pie chart properties can be found here. http://msdn.microsoft.com/en-us/library/dd456674(v=vs.110).aspx

Sample XML of a Pie Chart series with all custom properties.

<Series	ChartType="pie" ShadowOffset="0" IsValueShownAsLabel="False" 
	Font="{0}, 9.5px" LabelForeColor="59, 59, 59" 
	Label="#LEGENDTEXT #PERCENT  -  #VAL out of #TOTAL total" 
	LegendToolTip="#PERCENT  -  #VAL out of #TOTAL total"
	CustomProperties="PieDrawingStyle=Concave,
			  PieStartAngle=270,
			  PieLabelStyle=Inside, 
			  PieLineColor=Black,
			  LabelsHorizontalLineSize=1,
			  LabelsRadialLineSize=1,
			  MinimumRelativePieSize=10,
			  CollectedThresholdUsePercent=True,
			  CollectedThreshold=10,
			  CollectedColor=Tan,
			  CollectedSliceExploded=True,
			  CollectedLabel=Collected #VAL - #PERCENT,
			  CollectedLegendText=Collected - #VAL out of #TOTAL total,
			  CollectedToolTip=#VAL out of #TOTAL total">
  <SmartLabelStyle Enabled="True" />
</Series>

The sample series can be used to replace the standard series generated by the chart editor.

Thanks for reading. If you have a tip or trick for pie charts that I haven’t found, please include them in the comments. And of course, please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , , ,

19 Comments

Design options for bar charts and Top X bar charts

Here’s a couple of design options to improve bar charts, fix some pet peeves of mine, or simply change things up a little.

Those pet peeves are truncated labels and always putting the top record on the bottom. Fortunately, both of those can be fixed as seen below.

Top 5 Chart - Top item on on top - Full Labels, non-truncated. CRM Chart, CRM 2011

MS CRM top 5 bar chart with the top item on top and non-truncated labels.

I’ve also positioned the labels on the inside of the chart, which is a better utilization of space.

Top X Charts

If you make a Top 5 Bar Chart, you get this.

Top 5 Bar Chart - Standard, made in CRM Chart editor

Top 5 bar chart made in the CRM Chart editor.

It has always puzzled me that the “top” item is on the bottom.  Nobody want’s to see their name on the bottom of a list. My first idea was to change the order in the xml from descending=”true” to descending=”false”. While that will reverse the order of the Bar chart, it will also only return the bottom 5 records rather than the top 5. Good to know if you ever need to make a bottom x chart of something.

Turns out the xml modification is even easier. Add IsReversed=”True” to your AxisX properties and you now get a Top 5 Bar chart with the top record on top.

crm chart xml axis x isreversed="true", CRM 2011

CRM Top 5 Chart barchart with X axis reversed and top item on top.

The same top 5 chart with the X axis reversed and top item on top.

Truncation of Axis Labels

Label truncation is particularly annoying when users name all their records something like “Campaign for xyz” or “Opportunity for xyz”. All you see on the labels are the “Campaign for…”. Somehow the “…” always contains the most important part.

To fix that, plus a few other modifications, I’m going to:

  • Add the axis label to the bar series, to get the label on the inside of the chart
  • Left align the axis label via Custom Properties
  • Make the bar color semi-transparent so it’s easier to read the labels
  • Increase the text size a little
  • Remove current truncated labels by disabling the X axis
  • Use a StackedBar chart type

xml axis labels inside

Top 5 BarChart CRM 2011 reversed - labels on inside across the bars

There are 4 spaces in front of the #AXISLABEL. That is to control the positioning and ensure the label doesn’t start before the bar does.

Font is increased from 9.5px to 11px.

ARGB colors was used  to add transparency. In this case a little over 50% transparency.

Custom property BarLabelStyle=Left is added to make sure the labels are aligned along the X axis.

StackedBar is used instead of a Bar chart, because that, in conjunction with BarLabelStyle=”Left”, forces all the labels to start along the axis. On a Bar chart the label may move right, till the end of a short bar. Even when it’s left positioned.

On AxisX; IsReversed=”True” to get the top item on top, and Enabled=”false” to remove the original truncated labels.

Hope you liked it. Please sign up for new posts or follow me on Twitter 

, , , , ,

11 Comments

Groupby Business Unit – even when not present (i.e. Activities/Opportunities)

In setups where both Users and Teams own records, it can sometimes be useful to group data by the owning Business Unit, rather than by the Users or Teams individually.

Most entities allow you to add an “Owning Business Unit” column in Advanced Find to take care of that, but some do not. Most notably the Activity entities, incl. Phone Calls, Appointments, etc. do not have this option. We can only get access to the “Owner”, “Owning User” and “Owning Team” from the UI. Regardless of which one we use, either we will get Teams separately from Users or we will only see the Business Unit related to either the Team or the User. This is also the case for the Opportunities entity.

Of course, the records still have an Owning Business Unit, and luckily, we can still call it in the chart xml directly.

Just copy the <link-entity> tags and the groupby attribute from a CRM chart xml that does support “Owning Business Unit” in the UI. Then use it to replace the attribute groupby line in your Activity chart xml.

Or just grab the code from here.

<link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" link-type="outer">
<attribute groupby="true" alias="REPLACE ALIAS" name="name" />
</link-entity>

Just insert the alias from the original groupby attribute where it says “REPLACE ALIAS”.

Grouping directly by Business Unit allow you to see both User owned and Team owned records as one. That way you don’t have to create 2 charts or create multiple series that go through the “Owning User” and “Owning Team” fields to find the proper Business Unit.

Don’t forget to follow me on Twitter

, , , , , ,

Leave a comment

Groupby on a related entity’s related entity

Are we focusing on the right Accounts and Opportunities?

A common question in sales departments. Accounts are often classified in some kind of A, B, C categorization based on their potential value etc. Ideally, that means you sales activity should primarily be related to A accounts and some B accounts. However, a common setup in CRM, is to set your sales activities regarding an Opportunity.

So if I want to see if sales activities are focused on the right type of Accounts, I need a chart that show me all the Activities related to an Opportunity, but group the data by the A, B, C categorization on the Account. Something that is not possible in the MS CRM Chart Designer.

The tricky part here is that we now have two steps.

Activity -> Opportunity -> Account

Had we only had one step, it would have been fairly easy to do everything in the MS Dynamics CRM Chart Designer. For example, if you wanted an overview of your Est. Revenue in Opportunities based on the type of Account, all you need to do is add the field from the Account entity, to your Opportunities View in “Advanced Find”, save the View, and then use it when you open the chart editor. Thank you to Adam Vero for having brought that very helpful shortcut to my attention.

Since our scenario has two steps, we can’t use this trick. However, we can get Advanced Find to do the most of the dirty work.

In Advanced Find, from the Activities entity, create a filter that goes to the Regarding (Opportunity), to the Potential Customer (Account) and check if the desired field, Classification, contains data.

Advanced Find - Activity to Opportunity to Account

Save the View and download the FetchXML for later as it contains the needed link information in xml format.

Next I’ll create a chart in the chart editor that I can use as a base for my new chart xml. I want my final chart to be a pie chart grouped by the Account classification. Since I need to add that link in the chart xml, I’ll create a similar chart and use another field as a placeholder.

Create base chart xml to modify later. crm chart xml

In this case the field “Priority” is the placeholder for the A, B, C classification.

I’ll export this chart and now I’ve got the pieces I need.

First I’ll take what I need from the FetchXML I downloaded from Advanced Find..

FetchXML - pieces need to modify crm chart xml

Highlighted are the needed lines with the <link-entity> information from Activity -> Opportunity -> Account, as well as the schema name for the Classification field, which we want to group by. We do not want to keep the filter information in this case.

Now that I have the link information, I’ll open the Chart XML and locate the groupby attribute line in the fetchcollection part.

Original fetchcollection for chart xml - locate groupby attribute

I want the <link-entity> tags from the FetchXML to surround this groupby attribute line. Then the placeholder, the prioritycode, should be replaced with the “accountclassificationcode” from the Account entity.

Final version should look like this.

fetchcollection in the chart xml after modifications

Import it back into CRM and use it on a View that only show Activities related to an Opportunity.

CRM Chart showing Sales Activities related to an Opportunity grouped by the Type of hte Potential Customer

Number of Opportunity Sales Activities grouped by Account Type

Hopefully that’ll get you a little closer to knowing if your Sales/Opportunity time is spent on Accounts that are worth it. In this case, more time is spent on B-Accounts than A-Accounts.

So, how many nested <link-entities> can CRM handle? Well, you can go at least 4 entities out. Beyond that, and probably sooner, you’ll hit a point there the chart just doesn’t make sense any longer.

Depending on the volume of records, this is an area where you can tell that these charts load a little slower. Only a few extra seconds, so enough to be noticeable, but it shouldn’t impact usability.

Thanks for reading and please follow me on Twitter

, , , , ,

9 Comments