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 thoughts on “N:N Relationship Charts in MS Dynamics CRM

  1. Another shortcut using the Advance Find.
    Start with Competitor table, link to the Related table Opportunity, Download Fetch XML, cut-and-paste the Link-Entity code.
    (I got really excited reading this latest post, then realised I was already using these N:N tables without thinking about it)

    PS Love this site; some of the charts I’m producing are invaluable to my managers.
    (and some that I build make no sense at all, depending on the Advance Find used to populate them) 🙂

    • Great tip – that is much easier. Particularly for Marketing Lists too. Thanks for sharing. Advanced Find does add an alias to one of the link-entity codes which you may want to remove or rename if you’re combining multiple pieces.

  2. Thanks for sharing this information !

    I was able to use this samples. However I wanted to use the same chart on both entities.
    It now works only when I am on the Competitors’ entity. I tried to import this chart(xml) into the Opportunity entity to filter the view and show in the information in the chart. I tried several combinations on changing the field names (id) but always get the error that the field does not exist in CRM.

    Many thanks.

      • Just had the same issue today. I was getting the issue because when I tried to re-engineer the chart to work from the Opportunity side, although I had moved the attributes to sit in the right section of the fetch, my group by remained at the bottom. I was as such trying to group the opportunity by the competitor field which doesnt exist. Something like the below.

  3. Is it possible to get two N:N relationships between the same entities in one chart? I have a custom entity with two relationships to another entity (Successes vs. Failures, effectively) and have code that works — but it’s treating the link-entity relationships as And statements instead of separate columns (but still showing two columns).

  4. I’m trying to do what seems like should be a very simple thing based on your (excellent) examples, but the chart will not even import if I set aggregate=”true”. I get an error on import “The specified XML file “Goal Progress (New).xml” is either not valid XML or does not conform to the chart schema”. Any thoughts on what is wrong with this xml? I’m starting to think it treats “goal” charts in a special way with limitations.

  5. Hi!.
    Thank you for this guide, but I have one question:
    I need insert chart on the account-form. I have n2n relationship with custom entity Goods. Each good have a one of option set (f.e. vegetable, meat, vitamine). I want to create chart in account form for this account /not for all accounts/ (f.e. vegetable 30%, meat 50%, vitamine 20%)- how I can do this?
    Sorry for my bad english.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s