This article is part 2 of a 2-part series.
In the first part of this series on Field Service Reporting with Power BI, we started creating the same report functionality inside of Power BI using Dynamics GP Field Service’s service call information by first getting the information from the following Field Service tables:
- SVC00100 – Technician Master
- SVC00200 – Service Call Master
- SVC00201 – Service Call Analysis Codes
- SVC00203 – Service Call Line Detail
- SVC00901 – Cause Codes Validation
- SVC00906 – Warranty Validation Codes
- SVC00907 – Problem Codes Validation
- SVC00908 – Repair Codes Validation
- SY03900 – Record Notes Master
Now it’s time to start creating our reports.
Analyzing Open Service Calls
The first report we are going to create is for our Service Calls or Open Work Orders. For this report, we will use the following charts:
- Active Work Order – Using a Card visual and Open Service Calls measure.
- Work Orders by Type – Using a Donut Chart and Open Service Calls measure and Service Type.
- Work Orders by Status – Using a Donut Chart and Open Service Calls measure and Service Status.
- Open Work Orders by Resource – Using a Stacked Bar Chart and Open Service Calls measure in the Values section and Service Tech and Customer Name in the Axis section of the chart.
- The final visual on the report is a matrix to see our open work order details associated with each chart above when clickingon them to cross filter.
Here is what the Open Work Orders report looks like:
Analyzing Completed Work Orders
Time to analyze our completed service call/work orders. For this report, we will use the following charts:
- Completed Work Order – Using a Card visual and Completed Service Calls measure.
- Avg Completion Time (in Mins) – Using a Card visual and Completed Service Calls measure.
- Avg Completion Time per Order by Service Type – Using a Stacked Column Chart and Completed Service Calls measure and Service Type.
- Avg Completion Time per Order by Service Tech – Using a Stacked Column Chart and Completed Service Calls measure and Service Tech ID.
- The final visual on the report is a matrix to see our open work order details associated with each chart above when click on them to cross filter.
Here is what the Completed Work Orders report looks like:
Analyzing Equipment Repairs
Time to analyze our completed service call/work orders. For this report, we will use the following charts:
- Equipment Service Call count by Problem Code – Using a Stacked Bar Chart and Equip Service Call Count measure in the values section and Problem Code and Customer Name Axis section.
- Equipment Service Call count by Repair Code – Using a Stacked Bar Chart and Equip Service Call Count measure in the values section and Repair Code and Customer Name Axis section.
- The final visual on the report is a matrix to see our open work order details associated with each chart above when click on them to cross filter.
Here is what the Completed Work Orders report looks like:
Conclusion
Stay tuned for the future Power BI developments and an overview of their functionality. And, for help with building reports like this, please contact KTL Solutions to kick starting your organization into becoming data driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
