Archive for category Multiple Series

N:N Relationship Charts in MS Dynamics CRM

N:N Relationships, or Many-to-Many Relationships, can be a little tricky to get information out of.

As an example competitors out-of-the-box have an N:N relationship with opportunities. Let’s say we wanted to look at a list of competitors and see how many active opportunities they are currently engaged in. With the chart editor and views, the best bet is to open each competitor record and count how many active opportunities it is related to. We can make that much easier with a chart like this.

Count and sum of estimated revenue for opportunities, per competitor chart in MS Dynamics CRM.

Count and sum of estimated revenue for opportunities, per competitor.

Before I get started on how to build the chart, let’s first have a look at how N:N relationships are structured in MS Dynamics CRM.

I will use the competitor opportunity relationship as an example, but this applies to all system and custom N:N relationships.

While it is not apparent in the solution designer, the relationship is controlled by an intersect table, in this case called “opportunitycompetitors”.

Table structure for competitor to opportunity N:N relationship in MS Dynamics CRM.

The intersect table is a system entity.

We will need this exact name of the intersect table for the fetchcollection in our chart xml.

To find the name of the intersect table, open up competitors for customization in the solution editor.

3 Solution Editor See NN relationships

Click N:N Relationships and open the relationship to opportunity.

4 Relationship Entity Name

Here, find the “Relationship Entity Name” towards the bottom of the form and make a note of the exact name for later.

Now that we have the name of the intersect table, on to building the chart. A full sample of the chart xml can be downloaded at the bottom of this post.

Build The Base Chart

As always, I start by creating a base chart I can use for editing the chart xml. I am starting on the competitor entity.

2 Create Base Chart

A simple bar chart counting competitors, grouped by competitor. Not very useful yet.

Simple – Count Opportunities Per Competitor

Export the xml and open it in your xml editor (Notepad++ perhaps).

Here’s the fecthcollection. I have renamed the obscure aliases to something more sensible. This is where we will do most of the work. If you change the aliases too, remember to also change them in the categorycollection.

      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
            <attribute groupby="true" alias="GroupBy" name="name" />
            <attribute alias="CountOfOpportunities" name="name" aggregate="count" />
          </entity>
        </fetch>
      </fetchcollection>

Now I want to change the chart xml, so I get a link to the intersect table and can count the number of relationships the competitor has to opportunities.

      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
			<link-entity name="opportunitycompetitors" from="competitorid" to="competitorid">
				<attribute alias="CountOfOpportunities" name="competitorid" aggregate="count" />
			</link-entity>
            <attribute groupby="true" alias="GroupBy" name="competitorid" />
          </entity>
        </fetch>
      </fetchcollection>

I have now created a link to the intersect table and placed the count aggregate inside of it. The groupby is still directly on the competitor entity. I also changed the name of the count attribute to “competitorid”. This is because the intersect table only holds the id fields.

Import to MS Dynamics CRM and look at the result.

Count of opportunities per competitor via the intersect table in MS Dynamics CRM.

Count of opportunities per competitor.

Now we have a list of competitors including a count of how many opportunities they are competing on.

On import you may run into this error message.

Error

If that happens, just change the width of the chart or refresh the page and it will work.

Advanced – Count and Sum of Estimated Revenue from Opportunities

That was charting on N:N relationships in its simplest form. However, as always we would like to do more. How about including the sum of the estimated revenue of those opportunities, and also filter it to only include active opportunities.

In order to achieve that, we need to add an extra link-entity to get all the way to the opportunity entity, so we can access fields there directly. In the first example we stopped on the intersect table.

Sample of fetchcollection which links from competitor, via the intersect table, to the opportunity entity.

      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
            <link-entity name="opportunitycompetitors" from="competitorid" to="competitorid">
				  <link-entity name="opportunity" from="opportunityid" to="opportunityid">
						<filter type="and">
						  <condition attribute="statecode" operator="eq" value="0" />
						</filter>
						<attribute alias="CountOfOpportunities" name="opportunityid" aggregate="count" />
						<attribute alias="EstimatedRevenue" name="estimatedvalue" aggregate="sum" />
				  </link-entity>
            </link-entity>
            <attribute groupby="true" alias="GroupBy" name="competitorid" />
          </entity>
        </fetch>
      </fetchcollection>

We now have two sets of <link-entity>, linking through the intersect table to the opportunity entity.

Notice the pattern in the links are name=”intersectable” from=”field with id from intersect table” to=”field with id from starting entity” and in the next line it is name=”the other entity” from=”field with id on other entity” to=”field with id on intersect table”. Since the id fields are mostly the same, once you have the name of the intersect table, these are fairly straight forward. Marketing lists are an exception to this.

Inside the <link-entity> to the opportunity entity, I have added a filter so we only include active opportunities. As usual, I just created a view in Advanced Find, downloaded the fetchxml, and copied the part with the filter.

I also added an extra attribute to include the sum of the estimated revenue for the opportunities.

Here’s what the final chart looks like.

N:N relationship chart in MS Dynamics CRM using only standard xml customizations in the chart xml.

Opportunities and sum of estimated revenue per competitor.

I also made these optimizations to the chart, just to touch it up a little.

  • In the Series
    • LabelFormat=”C0″  – so values in the chart have no decimals – that’s the letter “C” followed by a zero
    • DrawingStyle=Cylinder – in the custom properties just add some shading
  • In the Axes
    • Format=”C0″ – so dollar values on axis have no decimals
    • YAxisType=”Secondary” – on the series containing the sum to get count and sum on different axes
    • IsReversed=”True” – on the X axis so it is alphabetical from the top, not the bottom

A full sample can be found below with these changes included.

As a note, charting on N:N relationships also works on Marketing Lists (static only). That means you can create a list of users with a chart of how many marketing lists they are associated with. However, you will need to use a metadata browser to get the names of the intersect table (listmember) and its id field (entityid), which is different in this case.

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

Sample chart xml for count and sum of opportunities per competitor

Here’s a sample chart xml ready for import to the competitor entity. It is made in an environment with no customizations so it should be easy to import. As always, samples are provided for fun only. Do not use in a production environment without proper testing.

<visualization>
  <visualizationid></visualizationid>
  <name>Opportunities per Competitor - by CRM Chart Guy</name>
  <primaryentitytypecode>competitor</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
            <link-entity name="opportunitycompetitors" from="competitorid" to="competitorid">
              <link-entity name="opportunity" from="opportunityid" to="opportunityid">
                <filter type="and">
                  <condition attribute="statecode" operator="eq" value="0" />
                </filter>
                <attribute alias="CountOfOpportunities" name="opportunityid" aggregate="count" />
                <attribute alias="EstimatedRevenue" name="estimatedvalue" aggregate="sum" />
              </link-entity>
            </link-entity>
            <attribute groupby="true" alias="GroupBy" name="competitorid" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="GroupBy">
          <measurecollection>
            <measure alias="CountOfOpportunities" />
          </measurecollection>
          <measurecollection>
            <measure alias="EstimatedRevenue" />
          </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" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="DrawingStyle=Cylinder, PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>
        <Series ChartType="Bar" IsValueShownAsLabel="True" LabelFormat="C0" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="DrawingStyle=Cylinder, PointWidth=0.75, MaxPixelPointWidth=40" YAxisType="Secondary">
          <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" 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" IsReversed="True" 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" Format="C0" ForeColor="59, 59, 59" />
          </AxisY2>
        </ChartArea>
      </ChartAreas>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
      </Titles>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

If you made it this far. You’re awesome! Seriously. Tweet about it!

9 Comments

Add Percentage Labels to a 100% Stacked Bar chart in MS Dynamics CRM

The 100% stacked bar chart is great to display the relative amounts within a series.

However, for some reason the 100% stacked bar chart comes without labels to indicate what the percentage is for each group. You can eyeball it, but that’s not very accurate.

Original StackedBar100 chart

A 100% stacked bar chart made in the MS Dynamics CRM chart editor. No labels.

It is a little tricky to get to the accurate result, so I will go through the process of getting there.

The obvious thing to try first, is to export the chart xml and add IsValueShownAsLabel=”True” to the series.

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

 

IsvalueShownAsLabel True on a StackedBar100 chart

IsValueShownAsLabel=”True” added. Labels are the sum of the group.

This gives us the sum of each individual phase. This could be useful in its own right, but I really want the percentage on the label.

The next obvious thing to try, is to put in Label=”#PERCENT” and set IsValueShownAsLabel=”False”. Why not? It works great for pie charts.

        <Series ChartType="StackedBar100" IsValueShownAsLabel="False" Label="#PERCENT" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>

 

Keyword PERCENT on StackedBar100 chart

Keyword Label=”#PERCENT” added. The percentages run across the bars rather than within a single bar.

Now we get some great looking percentages, but the values add up to 100 on the second series. Not across like we would expect.

The magic combo to add to the series xml for percentage labels on a 100% stacked bar chart in MS Dynamics CRM is:

IsValueShownAsLabel=”True” LabelFormat=”#.00′ %'”

        <Series ChartType="StackedBar100" IsValueShownAsLabel="True" LabelFormat="#.00' %'" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>

 

StackedBar100 chart with correct percentages as labels in Microsoft Dynamics CRM charts.

With IsValueShownAsLabel=”True” and LabelFormat added, the percentages on the labels now display as intended.

Finally we have proper percentages across on the bar chart as we would expect.

 

A couple of other optimizations for a percentage bar chart.

LabelFormat=”#.00 ‘%'” in the series to show the percentage sign and always have 2 decimals.

Format=”0’%'” on the Y-axis to remove decimals and add percentage sign.

More on where and how to add Format and LabelFormat in MS Dynamics CRM charts here.

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

, ,

9 Comments

Aggregate Total on top of Stacked Column Charts or Bar Charts in MS CRM 2011

Adding an aggregated total to the top of a stacked column chart sounds simple enough. It’s a couple of clicks in Excel. In MS CRM 2011 however, it is a different story. You would have to add an additional series to the chart, which then calculates and displays the total. But Stacked Column charts in MS CRM 2011 do not support multiple series.

CRM 2011 Stacked Column Chart with aggregate total on top

But that does not mean it is not possible. This CRM chart show us the aggregated total value of each of our pipelines, stacked by the pipeline phases, for each of the types of purchase processes.

So how did I create this chart? First, I need a chart with a series for each of the 4 pipeline stages, and one for the total. Then I display the series in the chart so they look like a standard stacked column chart. The total on top is a point chart type. The drawback here, is of course that all the possible series needs to be known so we can include them in the chart. In this case, all the series are each phase in the pipeline.

Since the user interface in MS CRM 2011 for creating charts does not support multiple series for stacked column charts, we need to create something else as our chart XML base and work off that.

I will use the UI to create a normal column chart and add all the series I need.

In this case a column chart with 5 series.

Chart Designer with all the series and category - CRM 2011 CRM Chart

1 for each of the 4 stages in the pipeline (Develop, Qualify, Propose, Close) plus 1 extra series for the total.

I want to see how my pipelines looks between the different types of Purchases Processes, so I’ll pick “Purchase Process” as the category so I get that on the X axis.

All the series I have added as a standard column chart.

Now I’ll export the chart xml and get to work. (Full chart XML samples are available at the bottom of this post)

CRM 2011 might add some references to a secondary Y axis in your XML. If that is the case, remove these first.

Rename the series in the Chart XML

I’ll start out by renaming all the aliases to something more sensible and easier to work with.

Original XML

Original Chart XML datadescription with aliases highlighted

Chart XML with renamed aliases

Chart XML with new aliases

Now it’s much easier for me to see which alias represents what data. I have “Purchase Process” as the category, the name for each pipeline phase, and the aggregated total at the end.

Change the Series Chart Types

Next I’ll change the first 4 series for the pipeline phases from ChartType=”Column” to ChartType=”StackedColumn”.

The last series for the aggregated total will need the following changes;

  • ChartType=”Point”
  • IsVisibleInLegend=”False” – don’t need to see “Total” in the legend
  • IsValueShownAsLabel=”True” – YES, this is the whole point of this chart
  • LabelFormat=”#,#,#” – no need to see decimals on the total
  • MarkerColor & MarkerBorderColor=”Transparent” – we just want the total amount – no need to see a marker also
  • The font I increased to 14px so it stands out more

Changing the series charttypes in the xml

Let’s do a quick import of the chart xml just to check what it now looks like in CRM.

Chart after intial modifications - no filters, all totals

We can see the total amount in the larger font, but the different pipeline phases are also showing the aggregate amount.

Let’s add a filter to each of the series in the fetchcollection, so they only show the correct amount for each phase.

Filter the series for the Pipeline Phases

Here’s the original fetchcollection.

Chart XML Fetchcollection with new aliases

I’ll add a filter to each of the series by creating a self-referential <link-entity>. This will allow me to make sure each series only sum up the value of the Opportunities, specific to its phase.  The easiest way to get the filter properties is by creating them in Advanced Find and export the FetchXML.

Each attribute will get a <link-entity> and <filter> added as follows.


<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
  <attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
    <filter>
      <condition attribute="stepname" operator="eq" value="1-Qualify" />
    </filter>
</link-entity>

Here’s a part of the fetchcollection with filters added for each attribute for the pipeline phases. The last attribute I’ll leave without a filter as we do want the total on that one.

Chart XML Fetchcollection with Filters for each phase matcing the alias

Time to import the XML and have a look.

Chart with Total on Top - Filtered, but with Orignial colors and settings

Chart is now filtered and stacked properly for each series and there’s an aggregated total on top.

Adjust Chart Axis, Legend and Colors

It works, but we can make the chart look a lot easier to read and understand.

I’ll make the following changes:

  • IsValueShownAsLabel=”false” on all series except the total.
  • Add LegendText to all the series except the total
  • Add Color to each series – I’m going with increasingly darker shades of blue in this case
  • Add Format=”$#,#,k” to the LabelStyle for a shorter label on the Y axis

Last, I’ll reverse the series in the legend, because MS CRM 2011 automatically reverses it on stackedcolumn charts. In other words, I reverse the reversed order so it reads 1-2-3-4 instead of 4-3-2-1.

Cleaning up the Chart by adding colors ot the series etc.

One final import of the CRM chart XML and here’s the result.

CRM 2011 Chart - Stacked Column with Aggregate Total on Top

A couple of notes:

  • Bonus: Even though no Opportunities are in phase “4-Close”  we still see it in the legend
  • Filtering in the XML can be tricky – it’s your job to make sure everything relevant get’s accounted for
  • This approach allow you to add colors directly in the Series, which can be a lot easier than using the PaletteCustomColors
  • The Series have to be listed in the exact same order in the presentationdescription as they do in the measurecollection
  • The User can still hover the mouse over the chart to get the exact value of each pipeline phase in the tooltip

How about an Aggregate Total on top of a Stacked Bar Chart?

The same approach can be used for bar charts. All the chart types need to be a Stacked Bar for the pipeline phases and then a regular Bar type for the total, which then have to be made transparent. (XML sample included at the bottom of post)

CRM Chart 2011 Stacked Bar with Aggregate Total on top

Hope you enjoyed this post.

If you did, please follow me on Twitter for CRM Chart updates

Chart XML Samples

These chart xml samples are for reference only and not intended for use in a live environment without thorough testing.

They were created on a Microsoft Dynamics CRM 2011 trial with only the sample data added. The only addition I made to the sample data, was adding values to the Purchase Process field on the Opportunities.

Stacked Column Chart with aggregated total

<visualization>
<visualizationid>{C618533F-3784-E211-8B16-78E3B5114607}</visualizationid>
<name>Aggregated Total on top of StackColumn chart</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<attribute groupby="true" alias="purchase_process" name="purchaseprocess" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="1-Qualify" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="develop" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="2-Develop" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="propose" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="3-Propose" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="close" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="4-Close" />
</filter>
</link-entity>
<attribute alias="aggregated_total" name="estimatedvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="purchase_process">
<measurecollection>
<measure alias="qualify" />
</measurecollection>
<measurecollection>
<measure alias="develop" />
</measurecollection>
<measurecollection>
<measure alias="propose" />
</measurecollection>
<measurecollection>
<measure alias="close" />
</measurecollection>
<measurecollection>
<measure alias="aggregated_total" />
</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" LegendText="1-Qualify" Color="LightSteelBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="2-Develop" Color="CornflowerBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="3-Propose" Color="RoyalBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="4-Close" Color="DarkBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Point" IsVisibleInLegend="False" IsValueShownAsLabel="True" LabelFormat="$#,#,#" MarkerBorderColor="Transparent" MarkerColor="Transparent" Font="{0}, 14px" LabelForeColor="59, 59, 59"></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" Format="$#,#,k" 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"></Title>
</Titles>
<Legends>
<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

Stacked Bar Chart with aggregated total

 <visualization>
<visualizationid>{C618533F-3784-E211-8B16-78E3B5114607}</visualizationid>
<name>Aggregated Total on top of Stackedbar Chart</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<attribute groupby="true" alias="purchase_process" name="purchaseprocess" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="1-Qualify" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="develop" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="2-Develop" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="propose" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="3-Propose" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="close" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="4-Close" />
</filter>
</link-entity>
<attribute alias="aggregated_total" name="estimatedvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="purchase_process">
<measurecollection>
<measure alias="qualify" />
</measurecollection>
<measurecollection>
<measure alias="develop" />
</measurecollection>
<measurecollection>
<measure alias="propose" />
</measurecollection>
<measurecollection>
<measure alias="close" />
</measurecollection>
<measurecollection>
<measure alias="aggregated_total" />
</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="Stackedbar" LegendText="1-Qualify" Color="LightSteelBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="2-Develop" Color="CornflowerBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="3-Propose" Color="RoyalBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="4-Close" Color="DarkBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Bar" IsVisibleInLegend="False" IsValueShownAsLabel="True" LabelFormat="$#,#,#" Color="Transparent" Font="{0}, 14px" LabelForeColor="59, 59, 59"></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" Format="$#,#,k" 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"></Title>
</Titles>
<Legends>
<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

, , , , , , ,

61 Comments