Teams Collaboration

Analyzing Dynamics GP Procurement Transactions with Power BI

Procurement is an important part of any business.  Regularly analyzing this data is easier with the use of Power BI. It ensures that you are making the best use of your company’s resources. Here are a couple of things to look at when analyzing your purchasing department – Cost and Delivery Time Analysis.

Cost Analysis

There are several things to consider when analyzing Inventory Costs. Obviously, the unit cost of the item, but we also have to look at delivery, handling, storage, deposits, and item returns from customers.  In the Power BI analysis below, I’m simply looking at the Item’s Current Cost from Dynamics GP Inventory Item Card vs. the Average Purchasing and Per Order Unit Costs.

Delivery/Lead Time Analysis

Delivery/Lead times can be important to companies, particularly when perishable and large goods are concerned.  Delivery/Lead times that start to creep higher will hamper your Item Resource Planning Process. Your staff needs to analysis delivery/lead times and plan accordingly to prevent stock outs, limit storage area, and cash resource requirements.

Putting It All Together

I first created the below SQL view in my Dynamics GP company.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_POPTransactions]
AS
WITH CTE as
  (Select [PONUMBER]
      ,[ORD]
      ,[POLNESTA]
      ,[POTYPE]
      ,[ITEMNMBR]
      ,[ITEMDESC]
      ,[VENDORID]
      ,[VNDITNUM]
      ,[VNDITDSC]
      ,[NONINVEN]
      ,[LOCNCODE]
      ,[UOFM]
      ,[UMQTYINB]
      ,[QTYORDER]
      ,[QTYCANCE]
      ,[UNITCOST]
      ,[EXTDCOST]
      ,[INVINDX]
      ,[REQDATE]
      ,[PRMDATE]
      ,[PRMSHPDTE]
      ,[REQSTDBY]
      ,[COMMNTID]
      ,[DOCTYPE]
      ,[POLNEARY_1]
      ,[POLNEARY_2]
      ,[POLNEARY_3]
      ,[POLNEARY_4]
      ,[POLNEARY_5]
      ,[POLNEARY_6]
      ,[POLNEARY_7]
      ,[POLNEARY_8]
      ,[POLNEARY_9]
      ,[DECPLCUR]
      ,[DECPLQTY]
      ,[ITMTRKOP]
      ,[VCTNMTHD]
      ,[BRKFLD1]
      ,[JOBNUMBR]
      ,[COSTCODE]
      ,[COSTTYPE]
      ,[ORUNTCST]
      ,[OREXTCST]
      ,[LINEORIGIN]
      ,[FREEONBOARD]
      ,[ODECPLCU]
      ,[Product_Indicator]
      ,[Source_Document_Number]
      ,[Source_Document_Line_Num]
      ,[RELEASEBYDATE]
      ,[Released_Date]
      ,[Purchase_IV_Item_Taxable]
      ,[Purchase_Item_Tax_Schedu]
      ,[Purchase_Site_Tax_Schedu]
      ,[PURCHSITETXSCHSRC]
      ,[BSIVCTTL]
      ,[TAXAMNT]
      ,[ORTAXAMT]
      ,[BCKTXAMT]
      ,[OBTAXAMT]
      ,[Landed_Cost_Group_ID]
      ,[SHIPMTHD]
      ,[LineNumber]
      ,[ORIGPRMDATE]
      ,[FSTRCPTDT]
      ,[LSTRCPTDT]
      ,[RELEASE]
      ,[ADRSCODE]
      ,[CMPNYNAM]
      ,[CONTACT]
      ,[ADDRESS1]
      ,[ADDRESS2]
      ,[ADDRESS3]
      ,[CITY]
      ,[STATE]
      ,[ZIPCODE]
      ,[CCode]
      ,[COUNTRY]
      ,[PHONE1]
      ,[PHONE2]
      ,[PHONE3]
      ,[FAX]
      ,[ADDRSOURCE] from POP10110
                        UNION ALL
            Select [PONUMBER]
      ,[ORD]
      ,[POLNESTA]
      ,[POTYPE]
      ,[ITEMNMBR]
      ,[ITEMDESC]
      ,[VENDORID]
      ,[VNDITNUM]
      ,[VNDITDSC]
      ,[NONINVEN]
      ,[LOCNCODE]
      ,[UOFM]
      ,[UMQTYINB]
      ,[QTYORDER]
      ,[QTYCANCE]
      ,[UNITCOST]
      ,[EXTDCOST]
      ,[INVINDX]
      ,[REQDATE]
      ,[PRMDATE]
      ,[PRMSHPDTE]
      ,[REQSTDBY]
      ,[COMMNTID]
      ,[DOCTYPE]
      ,[POLNEARY_1]
      ,[POLNEARY_2]
      ,[POLNEARY_3]
      ,[POLNEARY_4]
      ,[POLNEARY_5]
      ,[POLNEARY_6]
      ,[POLNEARY_7]
      ,[POLNEARY_8]
      ,[POLNEARY_9]
      ,[DECPLCUR]
      ,[DECPLQTY]
      ,[ITMTRKOP]
      ,[VCTNMTHD]
      ,[BRKFLD1]
      ,[JOBNUMBR]
      ,[COSTCODE]
      ,[COSTTYPE]
      ,[ORUNTCST]
      ,[OREXTCST]
      ,[LINEORIGIN]
      ,[FREEONBOARD]
      ,[ODECPLCU]
      ,[Product_Indicator]
      ,[Source_Document_Number]
      ,[Source_Document_Line_Num]
      ,[RELEASEBYDATE]
      ,[Released_Date]
      ,[Purchase_IV_Item_Taxable]
      ,[Purchase_Item_Tax_Schedu]
      ,[Purchase_Site_Tax_Schedu]
      ,[PURCHSITETXSCHSRC]
      ,[BSIVCTTL]
      ,[TAXAMNT]
      ,[ORTAXAMT]
      ,[BCKTXAMT]
      ,[OBTAXAMT]
      ,[Landed_Cost_Group_ID]
      ,[SHIPMTHD]
      ,[LineNumber]
      ,[ORIGPRMDATE]
      ,[FSTRCPTDT]
      ,[LSTRCPTDT]
      ,[RELEASE]
      ,[ADRSCODE]
      ,[CMPNYNAM]
      ,[CONTACT]
      ,[ADDRESS1]
      ,[ADDRESS2]
      ,[ADDRESS3]
      ,[CITY]
      ,[STATE]
      ,[ZIPCODE]
      ,[CCode]
      ,[COUNTRY]
      ,[PHONE1]
      ,[PHONE2]
      ,[PHONE3]
      ,[FAX]
      ,[ADDRSOURCE] from POP30110)
Select 
                a.POPIVCNO,
        a.IVCLINNO,
        a.POPRCTNM,
        a.RCPTLNNM,
        b.VENDORID,
        b.VENDNAME,
        b.VNDDOCNM AS [Invoice No],
        b.receiptdate as [Invoice Date],
                        f.Released_Date,
                        DATEDIFF(day, f.Released_Date, c.receiptdate) as Lead_Time,
                        --d.PONUMBER,
                        f.PONUMBER,
                        --d.RCPTLNNM,
                        --f.ORD,
        c.VNDDOCNM as [Bill of Lading],
        c.receiptdate as [Date Received],
        d.ITEMNMBR,
        d.ITEMDESC,
        d.VNDITNUM,
        d.VNDITDSC,
        a.QTYINVCD,
        e.QTYSHPPD,
        e.QTYREJ,
                        f.UNITCOST
From    POP10600 a
INNER JOIN POP30300 b ON a.POPIVCNO = b.POPRCTNM
INNER JOIN POP30300 c ON a.POPRCTNM = c.POPRCTNM
INNER JOIN POP30310 d on a.POPRCTNM = d.POPRCTNM
                         and a.RCPTLNNM = d.RCPTLNNM
INNER JOIN CTE f on d.PONUMBER = f.PONUMBER and d.RCPTLNNM = f.ORD
INNER JOIN POP10500 e on a.POPRCTNM = e.POPRCTNM
                         and a.RCPTLNNM = e.RCPTLNNM

Next, I imported the data into my Power BI Desktop ‘pbix file’ and created DAX measures for Total Lead Time, Total Purchasing Unit Cost, Average Lead Time, Average Purchasing Unit Cost, and Lead Time Weekly Moving Average.  The result is the Power BI report below that compares:

  • Item Current Cost vs. Average Purchasing Unit Cost and Order Detailed Costs
  • Average Lead Time vs. per order lead times

Results for comparisons

Future Dashboard developments

In the coming months, I plan to add additional Power BI reports associated with my Dynamics GP data.  The report will include

  • an updated inventory procurement analysis using the lead time analysis from this post and sales forecasts.

Stay tuned for the future developments of the reports and an overview of their functionality. For help with building reports like this, please contact KTL Solutions where we can help kick start your organization into becoming data driven by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.


BARRY CROWELL, MBA | BI/EDW Solution Architect

Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360.  His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University.  Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.

Share this post

Related Posts

KTL Solutions Makes Inc. 5000 List for 2022

Each year, Inc. Magazine comes out with its list of the fastest-growing private companies in the nation. The 2022 list includes 131 Maryland businesses, with 11 of them making the prestigious Top 500. Those 131 MD-based businesses represent $2.7 billion in total revenue and more than 10,500 jobs added.

Read More »