Insights from Dynamics 365 Sales data

Getting Insights from Dynamics 365 Sales data

Customer relationship management (CRM) systems play a crucial role in managing customer interactions, streamlining business processes, and improving profitability. Nowadays, businesses ranging from startups to large enterprises are tailoring prebuilt CRM systems to fit their unique needs.

Microsoft Dynamics 365 is a package that provides CRM and enterprise resource planning (ERP) applications that accelerate business growth by helping businesses manage customer databases, generate new leads, and engage with customers.

In this blog post, we are going to see how to connect to and visualize Dynamics 365 data in a sales manager dashboard created using Bold BI. The dashboard is designed to help sales managers gain insight into the sales pipeline, identify potential leads, and reach revenue targets.

What is Dynamics 365 Sales?

Dynamics 365 Sales is Microsoft’s CRM application dedicated to sales. It helps salespeople and sales managers track leads, contacts, and opportunities from a team’s sales pipeline. It comes with a free, 30-day trial. If you’re a Microsoft partner, you can access the applications through PartnerSource.

First, create your account with appropriate details.

Microsoft Dynamic CRM Sign Up Page
Microsoft Dynamic CRM Sign Up Page

After setting up your account, the following page opens.

Microsoft Dynamics CRM Account Setup Page
Microsoft Dynamics CRM Account Setup Page

Your account details will be emailed to you once the setup completes. Click the link in the email that takes you to the CRM online trial, which will have sample data already installed. You can access all the data of the CRM, including sales, marketing, and service.

Now let’s see how to visualize sales data using Bold BI through the Microsoft Dynamics CRM Sales data connection.

Connecting to Dynamics 365 Sales Data

The Dynamics 365 web API allows you to retrieve data in Dynamics 365 entity instances through API requests. The base URL format for the Microsoft Dynamic CRM Web API is: https://<:your_organization_url>/api/data/v9.0/<:your_end_point>

Reference the following table to connect to Microsoft Dynamics and create a data source with appropriate APIs.

End PointsUsed For
GET [Organization URI]/api/data/v9.0/accountsGetting data for an account entity instance.
GET [Organization URI]/api/data/v9.0/opportunitiesGetting data for an opportunity entity instance.
GET [Organization URI]/api/data/v9.0/leadsGetting data for a lead entity instance.

Refer to Microsoft Dynamics’ entity documentation for more entities.

Creating a sales manager summary dashboard

Let’s create a sales manager summary dashboard with Microsoft Dynamic CRM sales data. Refer to the video “How to Create a Dashboard – Bold BI Tutorial for Beginners” if you’ve never set up a Bold BI dashboard before.

Sales Manager Summary Dashboard
Sales Manager Summary Dashboard

This dashboard showcases sales data through the following metrics, giving sales managers important insight into how the sales team is performing.

  • Revenue won
  • Win count
  • Lost revenue
  • Sales pipeline for opportunity
  • Top six accounts based on revenue
  • Industry-wise revenue
  • Country-wise revenue
  • Revenue trend
  • Won revenue by sales leaders
  • Opportunities details

Now let’s create a data source from the designer with Microsoft Dynamics data.

Creating a Microsoft Dynamics CRM data source

To create a Microsoft Dynamics Sales data source from the dashboard designer, follow the steps described in “Choose Microsoft Dynamics CRM data source” from Bold BI’s documentation. Also, use the steps in “Authentication with Microsoft Dynamics CRM” to authenticate with Microsoft Dynamics.

After successful authentication, the NEW DATA SOURCE configuration panel opens. Specify a unique name for the Microsoft Dynamics data source. Provide a valid Microsoft Dynamics REST API endpoint in the URL textbox. Leave the default value GET for the REST API under the Method section. Set the time interval for Refresh Settings so that the dashboard can refresh itself with the latest data updates.

Let the Data Format be JSON since the Accounts, Opportunity, and Lead API endpoints return results in JSON format. Microsoft Dynamics CRM Sales will be set automatically in the Authentication Type section since OAuth is used for authenticating with the Microsoft Dynamics CRM account.

Microsoft Dynamics CRM Sales Connection Window
Microsoft Dynamics CRM Sales Connection Window

Click Preview & Connect. As a result, a window opens listing the data results as seen in the following image.

Raw Data Window
Raw Data Window

Choose the desired columns and click Connect. Drag and drop the table and save the data source. Finally, the created data sources will be added in the DATA SOURCES panel.

Data Source Listing Panel
Data Source Listing Panel

Create an expression column in the CRM Opportunity data source to calculate fields as shown in the following table.

Expression NameExpression
Opportunity StatusIF([statuscode]=1,’In Progress’,IF([statuscode]=2,’On Hold’,IF([statuscode]=3,’Won’,IF([statuscode]=4,’Canceled’,’Out-Sold’))))
Opportunity StateIF([statecode]=0,’Open’,IF([statecode]=1,’Won’,’Lost’))
Win CountIF([Opportunity Status]=’Won’,1,0)
Lost RevenueIF([Opportunity State]=’Lost’,[estimatedvalue],0)

Now, the data source is ready to be configured with the widgets.

Configuring data to the widgets in a dashboard

Add the required widgets to your dashboard and configure them with corresponding data sources, as described in the following table.

Widget TitleConfiguration
Revenue Won


Type: KPI Card

Data source: CRM Opportunity

Actual Value: Actual Value

Target Value: Estimated Value

Win CountType: Number Card

Data source: CRM Opportunity

Measure: Win Count

Lost RevenueType: Number Card

Data source: CRM Opportunity

Measure: Lost Revenue

Sales Pipeline for OpportunityType: Funnel Chart

Data source: CRM Opportunity

Value(s): Estimated Value

Column(s): Step Name

Top Account by Revenue


Type: Bar Chart

Data source: CRM Account

Value(s): Revenue

Column(s): Name

Revenue by Industry


Type: Tree Map

Data source: CRM Lead

Value(s): Revenue

Column(s): Company Name

Revenue Won by Sales Leaders


Type: Column Chart

Data source: CRM Lead

Value(s): Revenue

Column(s): Full Name

Revenue by Country


Type: Map

Data source: CRM Account

Location Value: Revenue

Location Name: Address1_Country

Revenue Trend


Type: Spline Chart

Data source: CRM Opportunity

Value(s): Actual Value

Column(s): Actual Close Date

Opportunities Details


Type: Grid

Data source: CRM Opportunity

Column: Name,

Estimated Close Date,

Estimated Value,

Opportunity Status

Finally, the sales manager summary dashboard is ready. You can share it with your sales team by enabling access to it. Team members can view business information while working in Dynamics 365 Sales. For more details on sharing dashboards, refer to “Share Dashboards” in Bold BI’s documentation.


So, now you know how to connect to Dynamics 365 Sales data and visualize important information from your sales pipeline using Bold BI. If you have any questions, please post them in the comments section. You can also contact us by submitting your questions through the Bold BI contact webpage, or, if you already have an account, you can log in to submit your support question.

Bold BI has a 15-day free trial with no credit card information required. We welcome you to try it out to experience Bold BI for yourself.

Access live 30-minute demo with our experts

Leave a Reply


Access live 30-minute demo with our experts

Related Blogs

Up arrow icon
Live Chat Icon For mobile
Hugo Morris

We are here. Let’s chat!

Live Chat Icon