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 to ensure that you are making the best use of your company’s resources. Here are a couple of thing to look at when analyzing your purchasing department – Cost and Delivery Time Analysis.

Cost Analysis
There are several things to look at 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 and the Item’s Current Cost from Dynamics GP Inventory Item Card vs. the Average purchasing cost 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. Staff need to analysis delivery/lead times and plan accordingly to prevent stock outs and limit storage area and cash resource needs.

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 ,
            f.PONUMBER ,
            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 end 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

clip_image002

Future Dashboard developments

In the coming months, I plan 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

Advertisements