How to – Visualizing Hierarchical Data in Dynamics CRM 2015

We had discussed earlier about some of the top features in Dynamics CRM 2015, and one that we missed out on picking up was the new and powerful feature – Visualizing Hierarchical Data. As we have started to use this feature, we have realized the sheer potential this feature can bring in for quicker, efficient and effective data visualizations. Further, combining this with Roll-up fields gives the user the flexibility to see the aggregation of data visually.

Let us look at some of the benefits you will draw from visualizing data in CRM:

  • View and explore complex hierarchical information
  • View key performance indicators (KPIs) in the contextual view of a hierarchy
  • Visually analyze key information across the web and the tablets

Now let us look at where are can we use this feature. All OOTB entities are supported for data visualizations and by default the following system entities have visualizations enabled:

  • Account
  • Position
  • Product
  • User

Going ahead in the post, we shall be defining a visualization, step and step. First, let us look at what we need to bear in mind before defining data visualization

  1. Only one (1: N) self-referential relationship per entity can be set as hierarchical. In this relationship the primary entity and the related entity must be of the same type, such as account_parent_account or new_new_server_new_server
  2. Maximum number of fields that can be displayed in a visualization tile is four

Defining a visualization

  1. Create an Entity called new_affiliate
  2. Create a (1:N) self-referential relationship new_new_affiliate_new_affiliate and marked it as hierarchical, as shown here
  3. Click on the Hierarchy Settings–>New. Enter the mandatory fields and select a quick form. You can create a quick form by clicking create new link
  4. If you have more than one 1:N (Self reference) relationship with hierarchy enabled for the same entity then you can specify which relationship should be used for visualization by clicking “Mark a relationship as enabled for hierarchies” from the above screen which will bring this window. Now, select a relationship and click on “Mark hierarchical”
  5. Create 2 fields to calculate Rollup
    • Field – Commission – Type : Currency
    • Field – Total Commission – Type: Currency, Field Type: Roll-up
    • Click on the Roll-up button to define the rollup. Here I have specified a simple roll-up conditio

 

6. Publish all the customizations and create some records to see the visualizations

7. Create some affiliate records. Add commission also
8. You can see a sample record below. We can see a small calculator Icon next to Total Commission field and it is disabled by default. This is a roll-up field and the calculation will be done asynchronously. If we want to calculate immediately then user can click on the small refresh icon found at the end of the Total Commission field

9. After creating the records you can see an icon on the Active Affiliates List
10. Clicking on the hierarchy icon brings the below screen (I clicked on the icon next to Affiliate 1). The tile displays Name, Parent Affiliate, Commission and aggregated commission (Total Commission). This was configured on the “New Form” quick create form above. User can click on the small icon found on the top right corner of the tile which will open the corresponding affiliate record
11. Using Advanced find to query against a hierarchy. There are 2 new operators introduced in Advanced Find – Under and Not Unde

For Developers

Developers can fetch the hierarchical data using FetchXml or QueryExpression.

At present Odata doesn’t support querying hierarchical data.

Following fetch XML can be used to retrieve the Hierarchical count

<fetch distinct='false' no-lock='false' mapping='logical'>
  <entity name='new_affiliate'>
    <attribute name='name' />
    <attribute name='new_affiliateid' />
    <attribute name='new_affiliateid' rowaggregate='CountChildren' alias='AffiliateChildren'/>
    <filter type='and'>
      <condition attribute='new_parentaffiliate' operator='under' value='{0}' />
    </filter>
  </entity>
</fetch>

Note:

  1. The aggregate value returned represents all the child records, including any that the user may not have access to read.
  1. There is a default limit of 100 recursions allowed conditions for hierarchical queries. This can also be adjusted.
The following two tabs change content below.
Murali Narayanan

Murali Narayanan

Ever changing hobbies ...but an undying passion for Dynamics CRM, Salesforce and all the new age technologies out there!
Murali Narayanan
Murali Narayanan

Ever changing hobbies ...but an undying passion for Dynamics CRM, Salesforce and all the new age technologies out there!

All stories by: Murali Narayanan