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.
After setting up your account, the following page opens.
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 Points | Used For |
| GET [Organization URI]/api/data/v9.0/accounts | Getting data for an account entity instance. |
| GET [Organization URI]/api/data/v9.0/opportunities | Getting data for an opportunity entity instance. |
| GET [Organization URI]/api/data/v9.0/leads | Getting 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.
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.
Click Preview & Connect. As a result, a window opens listing the data results as seen in the following image.
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.
Create an expression column in the CRM Opportunity data source to calculate fields as shown in the following table.
| Expression Name | Expression |
| Opportunity Status | IF([statuscode]=1,’In Progress’,IF([statuscode]=2,’On Hold’,IF([statuscode]=3,’Won’,IF([statuscode]=4,’Canceled’,’Out-Sold’)))) |
| Opportunity State | IF([statecode]=0,’Open’,IF([statecode]=1,’Won’,’Lost’)) |
| Win Count | IF([Opportunity Status]=’Won’,1,0) |
| Lost Revenue | IF([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 Title | Configuration |
| Revenue Won
| Type: KPI Card Data source: CRM Opportunity Actual Value: Actual Value Target Value: Estimated Value |
| Win Count | Type: Number Card Data source: CRM Opportunity Measure: Win Count |
| Lost Revenue | Type: Number Card Data source: CRM Opportunity Measure: Lost Revenue |
| Sales Pipeline for Opportunity | Type: 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.
Conclusion
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.
