Interactive Dashboard Fun in Dynamics 365

Dashboard Results

Why configure Dynamics 365 Interactive Dashboards?

I look forward to the opportunity to create an Interactive Dashboard in Dynamics 365. Although they are quickly and easily configured, they look and perform like they took hours of our time! Admittedly, you won’t be able to provide the flexibility and functionality of a Power BI dashboard, but those features aren’t always required. The out of the box capabilities of interactive dashboards are often enough to provide great value. Even if your users don’t request an interactive dashboard, throw one together to demonstrate the power of Dynamics 365. Now that I have created a variety of interactive dashboards for a variety of industries, I have a few of my own personal best practices that I’d like to share.

Templates to get you started with Interactive Dashboards

To create a new interactive dashboard, from a solution, select Dashboards on the left. Go to the New button dropdown list and select “Interactive experience dashboard”.

Create New Dashboard

There are multiple templates available in to get you started with your interactive dashboards. The multi-stream dashboards display real-time data over multiple data streams, which are simply views. Each stream or view can be based on a different entity. The single-stream dashboards display real-time data over one stream or view. Both multi-stream dashboards and single-stream dashboards display charts that also function as data filters. For the purpose of this blog, I’m going to concentrate on single-stream dashboards.

Multi Stream DashboardsSingle Stream Dashboards

Interactive Dashboards for Opportunities

Most companies obviously pay close attention to the status of their sales opportunities. Interactive dashboards are a perfect tool for them to accomplish that. I recommend creating an “overview” interactive dashboard for all opportunities. Another interactive dashboard can be configured to do analysis on just the open opportunities. Because the data categories of interest will most likely be different for each group of opportunities, you can develop a unique and optimum design for each dashboard. In this blog, we will dig into the details behind the “overview” type of dashboard.

Opportunity Overview Dashboard

For the “overview” type of dashboards, I always choose one of the 5-Column Overview Dashboard templates. (The dashboard being featured in this blog is using the 5-Column Overview Dashboard II template.) After the dashboard form opens, give your dashboard a meaningful name and select the Filter Entity. Next, you need to select the Filter By date. You have the option to choose any of the Filter Entity date fields. In most cases, the Created On date works well. The Filter By date is the primary date filter and the date used by the selected default Time Frame. There are lots of choices for the default Time Frame (e.g. Last week, This month, Month To Date), but I like to use “This quarter”.

Configure Dashboard

Streams and Charts

I recommend using the entity view that lists “All” entity records as the one stream or view that will be displayed in this dashboard – in this case, All Opportunities. Now you need to think about the charts you want to use as filters. There may be one or more out of the box charts you can use. In the example below, Top Customers by Estimated Revenue and Top Opportunities by Estimated Revenue are both out of the box charts. The charts that I consider to be standard on an “overview” dashboard are the “by Status” and “by Owner” charts. You can configure them to be one of many different chart types. If you want to add a fifth chart, select the 5-Column Overview Dashboard template.

I think a pie chart works well for Opportunities by Status. Or, you may find more value in Opportunities by Status Reason. Our users had 12 Status Reasons defined, which I thought would be too many for a filter chart. So instead, I made Status Reason a Global Filter. More about Global Filters later in the blog. Tag charts are relatively new in Dynamics 365. I like to use a tag chart for Opportunities by Owner.

Option Set Color Codes


If you create some custom charts based on an option set field, remember to configure your option set values with different colors. I think an interactive dashboard that has some color is more appealing and makes it easier to use. If you google this, you will find that in data visualizations, the importance of color is not just aesthetics – it also conveys meaning. You could make your color selections a project in itself! But, I just google “HTML color codes” to find the color codes that I think are appropriate for my chart. You simply type, or copy and paste, the color code into the option set value Color field.


The tiles, which are also based on views, are the dark blue boxes positioned on the right side of the dashboard. Each tile offers a count of the opportunities within that view. This is great for a quick overview of how many opportunities are in each category. For Opportunities, there are many out of the box views to choose from. I will show you how tiles work later in the blog.

Save and Publish

When you are satisfied with your configuration and are ready to test it out, click on SAVE at the top and close the dashboard form. If you are adding it to a model-driven app, you may need to add the dashboard to your app and publish it. Next, publish your solution and test out your new interactive dashboard.

Check Out Your Interactive Dashboard

Navigate to Dashboards and open your new interactive dashboard to check it out. This particular dashboard is using test data, but you could imagine how it would look with a lot more data.

Dashboard Results


Dashboard Time Frame Filter

Time Frame

It is important for users to understand that the interactive dashboard opens using the selected default time frame. If the user clicks on the down arrow next to the default Time Frame, they will see many other out of the box options for the date filter. “Created On” is displayed at the top of the drop down list as a reminder that this is the Filter By date for this interactive dashboard. There is an option for a Custom Time Frame. If the user selects this option, they will be able to enter a Start Date and an End Date. This provides a way to limit your data scope to accommodate your custom time frame requirements. Unfortunately, you can’t set the Custom Time Frame to be your default. The Custom Time Frame needs to be reset each time you open the interactive dashboard.



Global Filter

As previously mentioned, there are additional filter capabilities in the Global Filter. There is a Show Global Filter button at the top of the interactive dashboard. Here, you will find additional field filters (e.g. date fields, option sets, numeric fields) that the user can modify to limit the amount of data being displayed in the dashboard. Some fields in your Filter Entity will most likely already be enabled as Global Filter fields. (If you want a field to be included as a Global Filter, check the “Appears in global filter in interactive experience” checkbox on the field definition.) The one really nice thing about Global Filters is that you can give them a meaningful name and save them. If there is a filter that you find yourself using often, this can be a big time saver.

Global Filter

Selected Filters

You don’t need to worry about forgetting which filters are currently active. All filters, whether selected by clicking on a segment in a chart or selecting values from a global filter option, will be displayed at the top of the interactive dashboard in the blue areas. In the example below, Estimated Close Date was selected from a Global Filter and Status was selected by clicking into the Opportunities by Status pie chart. It’s important to keep in mind that the default time frame (in this interactive dashboard, “Created On This Quarter”) is also active. You can remove filters individually by clicking on the “X” in the blue area. Or, all filters can be removed by clicking on “Clear All”.

Selected Filters2


Opptys Closing Next Month

The selected filters will not only impact the records you see in the data stream on the left side, but they will also reduce the data being presented in the charts and in the tiles on the right. Because I selected the “Open” pie slice, it would make sense that tiles for Closed Opportunities, Won Opportunities and Lost Opportunities would all display zero (see above).

The tile in the lower left corner displays Opportunities Closing Next Month. If you wanted to see which Opportunities fit into that category, you can click into the tile and the records will be displayed. These are also links, so click on a record and the Opportunity record form opens to display all the details about this Opportunity.


You could imagine that with more data behind these visualizations, these interactive dashboards would be quite impressive. Here is a good link from Microsoft that will provide more details and more available options – Configure interactive experience dashboards. Like I said before, it’s fun to try to anticipate your users’ needs by creating a Dynamics 365 interactive dashboard for them before they even ask for one. Give them a try and have fun!



Dynamics 365 Service Level Agreements Demystified

Dynamics 365 Service Level Agreements Setup

Dynamics 365 Service Level Agreements allow case managers to setup criteria upon which to monitor and analyze the case response time across their organizations. These criteria can include things like first response and case resolution time.

A first response Service Level Agreement was setup at a client’s request to ensure that all cases are initially responded to within one business day. This Service Level Agreement monitors a Customer Service Rep’s initial response to a case.

Microsoft provides helpful documentation on setting up a Dynamics 365 Service Level Agreement. You can find that documentation here.

Issue with Email Warning NOT being delivered

Next, I proceeded to configure the Service Level Agreement.  Triggering a warning can be set for when a threshold is met.   In this instance, an email reminder is sent if the Customer Service Rep (CSR) assigned to the Case does not respond within four hours.  Note: The elapsed hours calculation automatically takes into consideration the Business Hours configured.

The other warning actions that can be configured are: Create record, Update record, Change status and Assign record.

Dynamics 365 Service Level Agreement Warning Setting

I discovered that the email created in the Warning Actions part of the configuration was not being sent! After reviewing the configuration, I determined it was being done correctly and was not the source of the issue.

Dynamics 365 Service Level Agreements Workflow Discovered

The warning email was configured to be sent out if  four hours had elapsed and the first response had not been initiated. However, the email message was not being created.

While troubleshooting, I discovered an automatically generated background workflow running on the Case record that originated at the time of Case creation. This workflow was managing the Dynamics 365 Service Level Agreement processing that had been configured. Hence, the “First Response By” name of the workflow. I reviewed the workflow in order to determine what was preventing the warning email from being sent.

Please note: The workflow displayed below now includes an update to the case record. It initially was creating and sending an email to the Customer Service Rep. In order to facilitate creating charts and dashboards regarding the  SLA results,  I decided subsequently to include a custom field on the Case to house the SLA Status. This information is tracked on the SLA KPI Instance records that are attached to the case and NOT ON THE CASE ITSELF.  This is the reason for adding the custom status field.

When an SLA is applied to a case, one SLA KPI Instance record is created (for each SLA Item attached to the applied SLA) and attached to the case. The lookup fields configured in the SLA KPI related to the SLA will be populated with the newly created SLA KPI Instance records. The SLA KPI Instance records track information for each related SLA item: status, failure time, warning time and succeeded on time. 

Service Level Agreement

First Response Workflow Dissected

The workflow is monitoring the state of the Case to determine if it has passed the service level agreement criteria that has been setup and updating the associated SLA KPI instance accordingly.

The first Wait Step is highlighted in the workflow shown below. This step is waiting until the First Response sent equals Yes, the Case Status is Cancelled or Resolved. In these instances, it sets the SLA KPI Status to Succeeded.

It will timeout when the computed warning time ( 4 hours past the created on date of the case)  is reached. At that time, the SLA KPI Instance will be updated with an “approaching non-compliance” status and update the case accordingly. The original configuration I had setup had an email being sent from the CSR manager. The issue was that the CSR manager user was not configured to allow users  to send emails in their behalf. As a result, the processing in the workflow stopped with an error and the email was never sent. It also never got to the second wait statement.

SLA Background Workflow - Wait #1

The second part of the workflow shown highlighted below is waiting again until the Case Status is Cancelled or Resolved. The SLA KPI Status is then set to “Succeeded”.

SLA Background Work - Wait #2

When the computed failure time (which in this case is one Business day )  is reached, it will timeout. The SLA KPI Instance will be updated with a “Non-compliant” status.

I hope this blog was informative and aids in any SLA troubleshooting you might need to do.

Boost the Timeline with Dynamics 365 Custom Activities

Why use Dynamics 365 Custom Activities?

The Unified Interface Timeline was a nice addition to Dynamics 365. It was a big improvement over the old Activities and Notes control. Out of the box, the system captures interactions and communications around the usual activities like Phone Calls, Tasks, Appointments, Emails and Notes. But suppose a user would like the Timeline to reflect an activity that falls outside of these common interactions? We had two clients make a request like this. One client wanted to see the ClickDimensions Sent Emails that were sent to their Contacts. And, another client wanted a general “Made Contact” activity. The “Made Contact” could be a conversation at a trade show or a discussion at an industry golf outing. Dynamics 365 custom activities to the rescue!

Creating Dynamics 365 Custom Activities

We create a custom activity entity just like any other custom entity. However, you need to be sure to click on the “Define as an activity entity” checkbox. After your new Dynamics 365 custom activity is saved, you can go to the Entity Definition and open the “Update Icons” tab displayed at the top. There are loads of icons available on the web – some are free. Dynamics 365 recommends using a *.svg  file type icon.

Our Cyber Charter School Client

Our cyber charter school client uses ClickDimensions Campaign Automations to send two different types of emails to their prospective parent/guardian leads. Some are personalized emails containing required enrollment document links and others are marketing brochure type emails. But, those of us that work with ClickDimensions know that a Sent Email does not result in the creation of a Dynamics Email activity. Therefore, it is not reflected in the Timeline. As a workaround, we created a new Dynamics 365 custom activity record called “Follow Up Email.”

Custom Entity Definition

We found a free *.svg “envelope” icon on the web and uploaded it. However, we were sure to choose one that looks different from the out of the box Email activity icon.

Dynamics 365 creates a custom activity entity record with multiple out of the box fields. For this client, we added one new custom field to capture the ClickDimensions Email Template name. The next step was to configure a workflow that is triggered by the creation of a ClickDimensions Sent Email record. This workflow generates the custom activity record by mapping the following field values from the Click Dimensions Sent Email record:

      • Related contact is mapped to the Follow Up Email activity Regarding field
      • Subject is mapped to the Follow Up Email activity Subject field
      • Email Template is mapped to the Follow Up Email activity Email Template field (Email Templates should be given meaningful names)

The last step of the workflow is to update the Activity Status to “Completed”.  The final result is a Follow Up Email activity looks like this in the Timeline:

Charter School Timeline

Our Chemical Company Client

The sales team from our chemical company client spend a lot of time meeting people at trade shows and other industry events. Therefore, they needed a generic “Made Contact” custom activity. This would be something they could use when the contact made does not fit into a Phone Call, Appointment or Email. We configured the Subject on the Made Contact form to be prepopulated with “Made Contact.” This allows the user to append someone’s name or an event name. In addition, they can enter a description of the interaction in the out of the box Description field. This client requested a “handshake” icon for their custom activity. We were able to satisfy this business requirement in literally less than an hour by utilizing the power of a custom Activity. Here is an example of how the Made Contact activity looks in the Timeline:

Made Contact Timeline

Next time you are contemplating a solution for tracking an activity that doesn’t quite fit into the out the box activity offerings, don’t forget about Dynamics 365 custom activities. They are very simple to configure and could potentially fill in some of those gaps in your Timeline.

Beware Dynamics 365 Storage Cost Increase

Dynamics 365 Capacity Summary

Latest Customer Engagement Licensing Change Could Lead to Dynamics 365 Storage Cost Increase

With the latest Dynamics 365 licensing changes (see Dynamics 365 Licensing Guide – April, 2020), there’s one change that small business owners and IT administrators should be aware of. If you’re running Microsoft Dynamics 365 Customer Engagement (CRM), then you need to be aware that the data storage model is changing, and could potentially lead to a Dynamics 365 Storage Cost Increase.

Where to Look to Identify Biggest Data Storage Offenders

The first place to look to see if you may be impacted is the Power Platform Admin Center. Just navigate to and look at the summarized storage capacity usage. Note that the storage capacity summary shows how much data storage is used by all the environments in the tenant. The storage capacity is broken down by Database, Log, and File type. Database is your actual data, Log is your audit logs, and File is file attachments.

Dynamics 365 Capacity Summary

Until this change, a lot of third-party add-ins were geared towards reducing the amount of space used by file attachments in your CRM system. It is hardly necessary to worry about files saved in your CRM system now, because you’ll have 20 GB for the tenant to start, and an additional 2GB for each full-licensed user. So, if you have 15 users, you’ll have 50GB of file storage — more than enough for most user environments.

However the Database Storage metric is more likely to be over the available capacity. You get 10GB for the tenant, and an incremental 0.25GB for each full-licensed user. As shown on the capacity summary image above, you can see that 10GB + 15 users x 0.25GB = 13.75GB. And you can see that we’re using 11.07 out of 13.75GB. Again note, this is the summary page and measures across all your environments (e.g., your Production plus your Test environments). The screenshot below shows the breakdown by environment (or organization).

Dynamics 365 Storage Capacity Breakdown by Environment

Impact of Dynamics 365 Storage Costs Increase

Here we can start to get an idea of the impact of the Dynamics 365 Storage Costs Increase, if you have a few years of data, and a full-copy test environment, you may be over your allotted capacity for the CDS Database Capacity metric. Under the previous licensing, you could buy additional capacity for $5 / GB per month. Now it’s $40 / GB per month.

type of storage capacity Included Capacity Incremental Capacity for Each Full-Licensed User Additional Storage Cost
CDS Database Capacity (e.g., relational database data) 10GB 250MB / user $40 / GB per month
CDS File Capacity (e.g., file attachments) 20GB 2GB / user $2 / GB per month
CDS Service Log Capacity (e.g., audit logs) 2GB none $10 / GB per month

Dynamics 365 Storage Costs Increase Mitigation

There are two places to look to see what could be major factors impacting your CDS database usage. First, look to see if you have multiple environments with equivalent database sizes. If you have a test or demo environment of equal size to production, chances are you can pare that down. Rarely does the test environment need to have all the historical data that the production environment has.

In our example, above, you can see that we have 4.66GB in prod, 3.1 GB in the demo environment, and 1.13GB in our Test environment. If you don’t need a test, demo, or training environment, then these environments can be deleted to free up capacity. If you do need them, you still have options.

What Tables are Using the Most Storage Capacity?

From the screenshot, below, you can see the top ten tables that are using up database capacity. As indicated in the graphic, there are a few tables that you may not be able to do anything about. If you have managed solutions that you’ve imported, and are still using, you won’t be able to delete data from, or reduce the size of those tables. (Though, if you don’t need those solutions in your test environment, you can delete them from there.)

Database Storage Usage by Table

A good place to start is to download all the tables data, showing which tables are using the most data – from biggest to smallest. The table names listed are what are called schema names or logical names, as opposed to the friendly display name of the table. You can get more information about the specific tables via either the Metadata Browser or the Metadata Documentation Generator using the XrmToolBox.

Backup Essential Data to Long-Term Storage, then Delete Older Data

Once you identify the tables taking up the most space, you can get to work reducing the data size. If the data is non-essential, you can run bulk delete jobs now, and schedule recurring bulk delete jobs to periodically delete the data to keep it in check. Another strategy would be to delete just the older data. For example, customer support cases could be deleted if they’re older than one year old. Maybe there are certain types of customer support calls that can be deleted, but you definitely want an archive in case you ever need to refer back to them. In this case, we can “save-off” a copy of your CRM data to an Azure SQL Database. Then you can configure the database with a long-term retention backup policy (LTR) to automatically retain the database backups in separate Azure Blob storage containers for up to 10 years for the Azure SQL Database.