Using Extender to Enhance Dynamics GP Functionality

I had a recent request from a client where their current third-party salesperson commission software wasn’t working for them anymore. During my discussion with the client then outlined the following requirements:

  • Provide a place to store up to five additional sales people per customer maintenance address card.
  • At time of sales order integration, associate the current five additional sales people with the sales order.
  • When sales order is transferred to invoice have the sales people associated with the sales order transferred to the invoice.

The client already owned eOne Solutions Extender software so we proposed the following solution:

  • Create Extender window on Customer Address Maintenance window for the additional sales people.
  • Create Extender window on Sales Transaction Entry window for the additional sales people.
  • Create Extender solution view of Customer Address Maintenance and Sales Transaction Entry window data.
  • Create SQL trigger on SOP10100, Sales Order Header table, to transfer sales order sales people to sales invoice.
  • Create SQL trigger on SOP10100, Sales Order Header table, to handle sales order creation and copying current sales people from customer address maintenance record sales order.

Creating the Extender Windows

First, we need to create the Extender windows to store the additional sales people associated with each customer address.

1. Create two Extender windows named CUSTSLSPERSON and SOPSLSPERSON. I’ll go through the steps for SOPSLSPERSON below.

Extender Window names

2. Open Extender and create a solution and then a new Extender window.

3. Name the window ID SOPSLSPERSON and give it and description.

Extender Window Setup

4. From the Product drop down list choose “Microsoft Dynamics GP”

5. For the Series drop down choose “Sales” and then “Sales Transaction Entry” from Form and Window.

6. Add five fields and make them lookup fields associated with the salesperson table in GP.

Using the copy function on the Extender solution window you can copy the SOPSLSPERSON window and name it CUSSLSPERSON and then open it and change the Microsoft Dynamics GP window it’s associated with and the key fields. The changes should look like the screen shot below when you’re done.

Extender Window setup details

Create Extender SQL views

Now create two Extender views associated with each of the Extender windows that we just created. My going to use “vw_CustomerSalesPerson” as the SQL name for the Customer Extender window and “vw_EXT_SOPSLSPERSON” as the SQL name for the Sales Transaction Entry Extender window. To create the view, select the Extender Windows as the Primary Table and then the Window ID. Select all of the fields and then Save the Extender view to publish to SQL server.

Extender view setup

Extender view setup details

Creating SQL triggers to record the salesperson data

With all the Extender windows and views created, we need to create a way to handle the population of the Sales Transaction Entry Extender window when the Sales Order is first created and then the copying of the Sales Order sales people from the order to the invoice. For this I created two SQL triggers on the SOP10100 table.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[KTL_U_SOP10100_SLSPERSON] ON [dbo].[SOP10100]

AFTER UPDATE

as

DECLARE @insSOPNUMBE char(21)

DECLARE @SOPTYPE int

SELECT @insSOPNUMBE = SOPNUMBE

FROM Inserted

WHERE SOPTYPE=3

SELECT @SOPTYPE = SOPTYPE

FROM Inserted

WHERE SOPNUMBE = @insSOPNUMBE

IF (@SOPTYPE = 3)

BEGIN

DECLARE @ORIGTYPE2 int

DECLARE @ORIGNUMB2 char(21)

DECLARE @FieldValue1 char(15)

DECLARE @FieldValue2 char(15)

DECLARE @FieldValue3 char(15)

DECLARE @FieldValue4 char(15)

DECLARE @FieldValue5 char(15)

SET @ORIGNUMB2 = (SELECT ORIGNUMB from SOP10100 WHERE SOPNUMBE = @insSOPNUMBE)

SET @ORIGTYPE2 = (SELECT ORIGTYPE from SOP10100 WHERE SOPNUMBE = @insSOPNUMBE)

SET @FieldValue1 = (Select [Salesperson 1] from [dbo].[vw_EXT_SOPSLSPERSON] where rtrim([SOP Number]) = rtrim(@ORIGNUMB2) and rtrim([SOP Type]) = rtrim(convert(char(15),@ORIGTYPE2)))

SET @FieldValue2 = (Select [Salesperson 2] from [dbo].[vw_EXT_SOPSLSPERSON] where rtrim([SOP Number]) = rtrim(@ORIGNUMB2) and rtrim([SOP Type]) = rtrim(convert(char(15),@ORIGTYPE2)))

SET @FieldValue3 = (Select [Salesperson 3] from [dbo].[vw_EXT_SOPSLSPERSON] where rtrim([SOP Number]) = rtrim(@ORIGNUMB2) and rtrim([SOP Type]) = rtrim(convert(char(15),@ORIGTYPE2)))

SET @FieldValue4 = (Select [Salesperson 4] from [dbo].[vw_EXT_SOPSLSPERSON] where rtrim([SOP Number]) = rtrim(@ORIGNUMB2) and rtrim([SOP Type]) = rtrim(convert(char(15),@ORIGTYPE2)))

SET @FieldValue5 = (Select [Salesperson 5] from [dbo].[vw_EXT_SOPSLSPERSON] where rtrim([SOP Number]) = rtrim(@ORIGNUMB2) and rtrim([SOP Type]) = rtrim(convert(char(15),@ORIGTYPE2)))

EXEC [dbo].[taExtenderWindowAddUpdate]

@I_vWindowID = ‘SOPSLSPERSON’,

@I_vKey1 = @insSOPNUMBE,

@I_vKey2 = ‘3’,

@I_vFieldValue1 = @FieldValue1,

@I_vFieldValue2 = @FieldValue2,

@I_vFieldValue3 = @FieldValue3,

@I_vFieldValue4 = @FieldValue4,

@I_vFieldValue5 = @FieldValue5,

@O_iErrorState = 0,

@oErrString = 0

END

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[KTL_U_SOP10100_SLSPCUS] ON [dbo].[SOP10100]

AFTER UPDATE

as

DECLARE @insSOPNUMBE char(21)

DECLARE @SOPTYPE int

SELECT @insSOPNUMBE = SOPNUMBE–ORIGNUMB

FROM Inserted

WHERE SOPTYPE=2

SELECT @SOPTYPE = SOPTYPE–ORIGNUMB

FROM Inserted

WHERE SOPNUMBE = @insSOPNUMBE

IF (@SOPTYPE = 2)

BEGIN

DECLARE @PRSTADCD char(15)

DECLARE @CUSTNMBR char(15)

DECLARE @FieldValue1 char(15)

DECLARE @FieldValue2 char(15)

DECLARE @FieldValue3 char(15)

DECLARE @FieldValue4 char(15)

DECLARE @FieldValue5 char(15)

SET @ORIGNUMB2 = (SELECT ORIGNUMB from SOP10100 WHERE SOPNUMBE = @insSOPNUMBE)

SET @ORIGTYPE2 = (SELECT ORIGTYPE from SOP10100 WHERE SOPNUMBE = @insSOPNUMBE)

SET @PRSTADCD = (SELECT PRSTADCD from SOP10100 WHERE SOPNUMBE = @insSOPNUMBE)

SET @CUSTNMBR = (SELECT CUSTNMBR from SOP10100 WHERE SOPNUMBE = @insSOPNUMBE)

SET @FieldValue1 = (Select [Physical] from dbo.vw_CustomerSalesperson where rtrim([Customer Number]) = rtrim(@CUSTNMBR) and rtrim([Address Code]) = rtrim(@PRSTADCD))

SET @FieldValue2 = (Select [HPV] from dbo.vw_CustomerSalesperson where rtrim([Customer Number]) = rtrim(@CUSTNMBR) and rtrim([Address Code]) = rtrim(@PRSTADCD))

SET @FieldValue3 = (Select [Balances] from dbo.vw_CustomerSalesperson where rtrim([Customer Number]) = rtrim(@CUSTNMBR) and rtrim([Address Code]) = rtrim(@PRSTADCD))

SET @FieldValue4 = (Select [LCMS Biotech] from dbo.vw_CustomerSalesperson where rtrim([Customer Number]) = rtrim(@CUSTNMBR) and rtrim([Address Code]) = rtrim(@PRSTADCD))

SET @FieldValue5 = (Select [FNIRS] from dbo.vw_CustomerSalesperson where rtrim([Customer Number]) = rtrim(@CUSTNMBR) and rtrim([Address Code]) = rtrim(@PRSTADCD))

EXEC [dbo].[taExtenderWindowAddUpdate]

@I_vWindowID = ‘SOPSLSPERSON’,

@I_vKey1 = @insSOPNUMBE,

@I_vKey2 = ‘2’,

@I_vFieldValue1 = @FieldValue1,

@I_vFieldValue2 = @FieldValue2,

@I_vFieldValue3 = @FieldValue3,

@I_vFieldValue4 = @FieldValue4,

@I_vFieldValue5 = @FieldValue5,

@O_iErrorState = 0,

@oErrString = 0

END

Conclusion

This post was a little different from my normal Power BI posts and is just one of many ways you can use eOne Solutions’ Extender software to Enhance Dynamics GP functionality. Stay tuned for the future Power BI developments.

Advertisements

Powerful Dynamics GP Field Service Reporting with Power BI

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 post, 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. This table contains Equipment Item Number, Problem Code, Repair Code and three note indexes with the Service Techs’ notes related to the fix. For this data, I created the following view:

                    ALTER 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

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:

Power BI Service Call Table Relationships

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)

Analyzing Open Service Calls

Now it’s time to create our reports. 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 click on them to cross filter.

Here is what the Open Work Orders report looks like:

Power BI Service Call Open Orders

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:

Power BI Completed Orders

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:

Power BI Equipment Repair analysis

Conclusion

Stay tuned for the future Power BI developments and an overview of their functionality and for help with building reports like this.

Enhancing our Inventory Reorder Point Analysis in Power BI

In my original Inventory Reorder Analysis report, I’m using some the technics discussed by Belinda Allen in her Inventory Item Reorder Dashboard. I converted it to Power BI and added a little extra by also analyzing SOP product sales data and computing “Inventory Logic” based on whether the “Inventory Sold in Time Period” columns total is three times the current “Qty Available” producing a report that looked like the screen shot below.

Original Inventory Re-Order Analysis

Enhancing the Inventory Reorder Analysis

A good Inventory Reorder analysis includes the recalculations to forecast future demand and safety stock find your reorder point. For the sales quantity forecasts for my lead time demand, I’m going to use the DAX time series discussed here by Fountain Analytics. After following time series instructions my sales forecast results looked like this.

Sales Quantity Usage Forecast

With the Sales forecast quantity usage in place, I then needed to calculate our Safety Stock based on the below formula.

Safety Stock Formula

Using the data from the Procurement Analysis and the Sales Analysis reports, I calculated the four data points with the below DAX formulas.

Max Days Consumption =

MAXX (

VALUES ( Dates[Date] ),

MAXX(

VALUES( ‘Products'[ItemNumber] ),

‘Sales Measures'[Units Sold]

))

Max Days Inventory Lead Time =

MAXX (

VALUES ( Dates[Date] ),

MAXX(

VALUES( ‘Products'[ItemNumber] ),

[Lead Time]

))

Average Days Consumption =

AVERAGEX (

VALUES ( Dates[Date] ),

AVERAGEX(

VALUES( ‘Products'[ItemNumber] ),

‘Sales Measures'[Units Sold]

))

Average Days Inventory Lead Time =

AVERAGEX (

VALUES ( Dates[Date] ),

AVERAGEX(

VALUES( ‘Products'[ItemNumber] ),

[Lead Time]

))

You put it all together to your Reorder Point – (Lead time demand) + (safety stock) = Reorder Point. The end result is the updated Inventory Reorder Analysis report shown below.

Update Inventory Re-Order Point Analysis

Updated Template

You can download the updated template here. In the template, I also updated the Inventory Reorder Point report using the New Matrix functionality.

Stay tuned for the future developments of the reports and an overview of their functionality and for help with building reports like this.

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

2 more Inventory Power BI Dashboards to Supercharge Your Dynamics GP Reporting

As I continue to build out my Dynamics GP Power BI dashboards, we look at product performance and inventory reorder analysis.

Product Performance

In the Product Performance dashboard, I’ve add ABC segmentation, moving averages and time comparisons using DAX measures. You can find additional information on the technics I’m using within my dashboards and reports at www.daxpatterns.com.

In the Product Performance report below, I used a scatter chart to analysis the Product classes profit margin and totals profits, the Sales ABC by ABC Class stacked bar chart segments the products using ABC segmentation and allows you to drill down into each segment to see the under-lining detail. Additionally, there is a total cost by warehouse location and weekly moving average cost comparison chart at the bottom of the report.

Power BI GP Product Performance

Inventory Reorder Analysis

For my Inventory Reorder Analysis report I’m using some the technics discussed by Belinda Allen in her YouTube video on Inventory Item Reorder Dashboard. I converted it to Power BI and added a little extra by also analyzing SOP product sales data and computing “Inventory Logic” based on whether the “Inventory Sold in Time Period” columns total is three times the current “Qty Available”

Power BI Inventory Reorder

Updated Template

You can download the updated template here. In the template, I also updated the Finance Summary report to use the New Matrix visual that was released in March. This adds the drill down functionality to the Profit and Loss Matrix Visual.

Future Dashboard developments

In the coming months, I plan additional Power BI reports associated with my Dynamics GP data. These reports will include:

  • Vendor Analysis
  • Field Service – Contract and Service Call Analysis

Stay tuned for the future developments of the reports and an overview of their functionality.

Supercharge Your Dynamics GP Dashboards & Reports with Power BI

During my last blog post, I walked you through creating a financial dashboard using Power BI with Dynamics GP data. I have created additional analysis and measures now.

clip_image002

If you look closely at the bottom left, you will see two additional reports for Sales Summary and Customer Analysis.

Sales Summary

The data used from Dynamics GP for this report are the Sales Order Processing (SOP) tables, Inventory, (IV) and Customer (RM) tables. The Sales Summary contains the same report filters, Year, Month and Company as the Financial Summary report. The top left visual shows the total sales by product class ranked in descending order. The bottom left visual shows the total sales by customer class/sales channel. The top two visuals in the middle and right show the total sales and transactions based on the filters selected with a seven-day moving average behind them. The bottom right visual shows a comparative between cumulative sales and cumulative costs.

clip_image004

Customer Analysis

The Customer Analysis report allows you to dig into Dynamics GP sales data by Customer, Year, Month and Company. By selecting a customer on the left you get to see three years of comparative sales, sales detail, last sales date and amount, total sales, sales last year, totals profits and percent sales growth for that customer.

clip_image006

Future Dashboard developments

In the coming months, I plan additional Power BI reports associated with my Dynamics GP data. These reports will include:

  • Vendor Analysis
  • Inventory Analysis
  • Field Service – Contract and Service Call Analysis

Stay tuned for the future developments of the reports and an overview of their functionality.

Power BI Visuals in On Premise SQL Server Reporting Services

Yesterday, January 17, 2017, Microsoft released the Power BI reports in SQL Server Reporting Services (SSRS) technical preview. You can read all about it here. I downloaded the technical preview and installed it on my system and deployed a Power BI Visual to my SSRS web site. Here are the steps to get it installed on your system.

· Download the SSRS Technical Preview and Power BI Designer for the preview

Power BI SSRS

· The Technical Preview has the following system requirements:

    • Your own VM or server (not one in production use)
    • Windows Server 2012 or later (or Windows 8 or later)
    • .NET Framework 4.5.2 or later
    • SQL Server Database Engine (2008 or later), to store the report server database
    • SQL Server Analysis Services (2012 SP1 CU4 or later), to store your data models

· I first installed the SQLServerReportingServices.exe on my system and accepted the licensing terms and agreement and then selected the “Install” button.

POwer BI SSRS

· After the installation completed I needed to configure the technical preview of SSRS by selecting the “Configure Report Server” button.

Power BI SSRS

· I provided my SQL Server name to Connect the Reporting Services Configuration Manager.

Power BI SSRS

**NOTE – While the configuration manager let, me configure the SSRS technical preview web site with my original VM’s computer name of ‘BARRYCROWEL38F0’ I could not publish by Power BI report with that computer name. I shortened the name to 7 characters and removed the numbers from the name to get the Power BI Designer Technical Preview to function with the SSRS web site.

· Design a Power BI visual with the Power BI Designer Technical Preview. In my visual, I connected to an SQL Server Analysis Services (SSAS) database. To publish you report to SSRS, select the ‘Save As’ menu option.

Power BI Technical Preview SSRS

· Following the screen prompts and supply the Reporting Server.

Power BI Technical Preview SSRS

· Then enter the report name.

Power BI technical preview SSRS

The final product is a Power BI visual with slicer functionality in an on premise SSRS web site.

**NOTE – the current SSRS technical preview does not work with custom visuals or R visuals.

Power BI On Premise

Conclusion

I’m loving the first on premise technical preview and can’t wait for what next.