Entity & Field Design for charts in Microsoft Dynamics 2015

CRM entity or table design has evolved substantially overt the last few years, so I wanted to take a moment to illustrate how the CRM 2013/2015 releases have impacted how I design entities in Dynamics CRM 2015.

When I am designing a CRM relational database using entities & fields I focus heavily on ‘normalization’ of the data elements to minimize data redundancy.  There are a lot of reasons for this, so check this out here if you are not familiar with database normalization.

As with all development platforms, Dynamics CRM has it’s limitations, but the new features of Calculated and Roll-up fields helps user self-service requirements for Charts, Advanced find and forms.

Consider the following, users want a chart that is based upon data in an entity but want to group the chart data by a field in a parent entity. For example, to generate a chart of opportunity revenue grouped by Customer country, you need a field from the account entity which can’t be accessed via the charting tool. The chart tool can only reference fields in the entity it is associated with.

To build the required chart, you need to add the Customer country field from the Account entity to the Opportunity entity. Prior to CRM 2015, you needed code of some kind to make this happen (usually with a plug-in) and you needed to make modification to the forms to ensure this echoed field was read only to prevent confusion.  Not a quick change for the most part.

Fast forward to CRM 2015, and here is the quick customization no-code solution.

1.Add a calculated field ‘Account country to the opportunity entity to match the Single line of text field in Account, and select calculated as the Field type.    image

2.Use the following formula to retrieve the account valueimage

3. Build your chart.

Simple, no-code, and done. I really like the fact that I no longer have to worry about the data getting out of sync with this solution.

I hope this give you some ideas to think about.

Stephen V Noe
Application Architect

Steve 2015

Advertisements

About stephenvnoe

Dynamics CRM solution architect, consultant, trainer & project manager. I provide CRM design, services, mentoring & support to all manner of Dynamics CRM projects.
This entry was posted in Database Design, Dynamics CRM. Bookmark the permalink.

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