This article is part 1 of a 2-part series.
On August 23, 2017, Microsoft announced the release of the Power BI solution template for Microsoft Dynamics 365 for Field Service. The template includes a set of reports designed for field service professionals. The solution template offers a very fast, guided experience to create compelling reports on an extensible, scalable, and secure architecture, and can be customized as needed. This means that instead of spending your time on plumbing, you can instead spend it on extending and customizing the solution template to meet your organization’s needs.
In this two-part series of posts, we will create the same report functionality inside of Power BI using Dynamics GP Field Service’s service call information. We first need to get 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
Almost all the tables are a straight import of the data, except for the Service Call Analysis Code and the Service Call Master. The Service Call Analysis Code table contains Equipment Item Number, Problem Code, Repair Code and three note indexes with the Service Techs’ notes related to the fix; and, I added a column to the Service Call Master table to calculate the complete time in minutes for each service call. For this data, I created the following view:
CREATE View [dbo].[ServiceCallEquipment]
AS
SELECT [SRVRECTYPE]
,[CALLNBR]
,[LNITMSEQ]
,[EQUIPID]
,[ITEMNMBR]
,[PRDLINE]
,[PROBCDE]
,[CAUSECDE]
,[RPRCODE]
,coalesce(a.TXTFIELD, ”) as [Note_1]
,coalesce(b.TXTFIELD, ”) as [Note_2]
,coalesce(c.TXTFIELD, ”) as [Note_3]
FROM [dbo].[SVC00201]
LEFT OUTER JOIN dbo.SY03900 a on SVC00201.Note_Index_1 = a.NOTEINDX
LEFT OUTER JOIN dbo.SY03900 b on SVC00201.Note_Index_2 = b.NOTEINDX
GO
CREATE View [dbo].[ServiceCalls]
AS
SELECT [CALLNBR]
,[SRVRECTYPE]
,[SRVSTAT]
,[SRVTYPE]
,[SVCDESCR]
,[priorityLevel]
,[CUSTNMBR]
,[Customer_Reference]
,[ADRSCODE]
,[CUSTNAME]
,[ADDRESS1]
,[ADDRESS2]
,[CITY]
,[STATE]
,[ZIP]
,[CNTCPRSN]
,[PHONE1]
,[OFFID]
,[SVCAREA]
,[TECHID]
,[TIMEZONE]
,[ENTDTE]
,[ENTTME]
,[Notify_Date]
,[Notify_Time]
,[ETADTE]
,[ETATME]
,[DISPDTE]
,[DISPTME]
,[ARRIVDTE]
,[ARRIVTME]
,[COMPDTE]
,[COMPTME]
,DATEDIFF(MINUTE, ARRIVTME,COMPTME) as ‘Completion Time’
,[Response_Date]
,[Response_Time]
,[PRICELVL]
,[PYMTRMID]
,[SLPRSNID]
,[LABSTOTPRC]
,[LABPCT]
,[LABSTOTCST]
,[PARSTOTPRC]
,[PARTPCT]
,[PARSTOTCST]
,[MSCSTOTPRC]
,[MISCPCT]
,[MISSTOTCST]
,[TAXEXMT1]
,[TAXEXMT2]
,[PRETAXTOT]
,[TAXAMNT]
,[TOTAL]
,[Invoiced_Amount]
FROM [dbo].[SVC00200]
Now that we have determined our Service Call data points and imported the data into Power BI, we need to create our table relationships. The final table relationship model should look like this:
With our relationships established, we now need to create our measures to be used in our reports. Here are the DAX measures I’ll be creating:
- Completed Service Calls
Completed Service Calls = CALCULATE(
COUNTROWS(ServiceCalls),FILTER(ServiceCalls,ServiceCalls[SRVSTAT]=”70C”))
- Open Service Calls
Open Service Calls = CALCULATE(
COUNTROWS(ServiceCalls),FILTER(ServiceCalls,ServiceCalls[SRVSTAT]<>”70C”),ServiceCalls[SRVSTAT]<>”OOC”)
- Total Completion Time (in Minutes)
Total Completion Time =
SUMX(FILTER(ServiceCalls, ServiceCalls[SRVSTAT]=”70C”),[Completion Time])
- Avg Completion Time per Order (in Minutes)
Avg Completion Time per Order =
DIVIDE(
[Total Completion Time],[Completed Service Calls],0)
- Equip Service Call Count
Equip Service Call Count = COUNTROWS(ServiceCallEquipment)
Ready to Do Even More with Dynamics?
Stay tuned for the second part of the Field Service series, where we will be creating our reporting in Power BI. 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.
