Power BI Dynamics GP Sales and Inventory Analysis Report Modifications

Recently, I’ve made some updates to my Power BI Sales and Inventory Analysis Reports and wanted to share those with you. On the Sales Analysis report, I’ve added the ability to segment my sales revenue by growth rate and compare that growth rate to the previous period selected. Here are the steps I used to add the changes to the report.

  1. First, I clicked on the “Enter Data” icon from the Home tab in Power BI Desktop and then entered the following information.Power BI Create Table
  2. I then created a DAX Sales Growth measure with the following code.
    Sales Growth = DIVIDE([Sales CY],[Sales LY],0)-1
  3. Next I created a SAX measure to segment the data based on the Sales Growth Groups that we created in step 1.
    Sales per Growth Group =
            CALCULATE (
                [Sales CY],
                FILTER (
                    VALUES ( Customers[Customer Class] ),
                    COUNTROWS (
                        FILTER (
                            'Sales Growth Groups',
                            [Sales Growth] >= 'Sales Growth Groups'[Min]
                                && [Sales Growth] < 'Sales Growth Groups'[Max]                     )                 )                     > 0
                )
            )
    
  4. Next, I modified my Product by Sales and Customer Class by Sales visuals to use the new Sales per Growth Group measure by dragging the segment from the Sales Growth Group table into the Legend field, Sales Growth measure into the Tooltips and Sales per Growth Group measure into the Values field.Screenshot of field wells
  5. Below is a screenshot of the end results. You can now clearly tell which segments are performing better or worse than last year.Power BI Sales Summary Report

My next modification was to my Inventory Order Analysis report. This one was just a small change by adding one DAX measure to calculate slow moving inventory and a Table visual to view the results.

  1. First, I added a DAX measure for slow moving inventory
    SM Inventory Value = 
    VAR SumOf = 
    CALCULATE(
        SUMX(
            Inventory,
            Inventory[Quantity on Hand] * Inventory[Unit Cost]
        )
    )
    VAR Range = DATESINPERIOD(Dates[Date], EDATE(TODAY(), -12), -24, MONTH)
    RETURN
    CALCULATE(
        IF(
            COUNTROWS(
            INTERSECT(
               VALUES(Inventory[Last Sale Date]),
               Range)
           ) > 0, 
           SumOf, 0
        )
    )

  1. Then I added the following columns and measure to a table visual to show my slow moving inventory.Table Visual Field Wells Screenshot
  2. Below is a screenshot of the modification to the Inventory Order Analysis report.Power BI Inventory Order Analysis

Until next time, Enjoy the code and Happy Analyzing!

Advertisements

How to Create a Better RMA Dashboard For Mammoth Saving

OK, maybe not mammoth saving but controlling quality and costs during the Return Material Authorizations (RMA) process has a multiplicative effect for growth in a company. Finding new ways to improve perfect order performance continually reduces RMA and increases customer satisfaction leading to more sales.

RMAs are a direct measure of product quality and a products’ nonconformance to customers’ specifications and requirements. They are issued for a wide variety of reasons. The RMA module within Dynamics GP is designed to provide detailed traceability for both customer and supplier returns. Before we get to designing our Power BI RMA dashboard let’s look at Dynamics GP RMA module.

Return Material Authorization Module

The Returns Management module for Microsoft Dynamics GP enables you to meet customer and vendor demands for product and part returns by streamlining tasks and improving your responsiveness to customer queries and complaints. Generate returns from historical customer invoices, service calls, or manually. Return an item to a vendor to fix it within your company and automatically transfer items from the returns warehouse to the main warehouse. Give your customers up-to-date information about the status of their returned items.

You can use Returns Management to enter, set up, and maintain your RMA and Return To Vendor (RTV) documents. If Returns Management is integrated with Service Call Management, an RMA is created automatically from a returnable parts line on a service call. If Returns Management is integrated with Sales Order Processing, you can select items directly from the historical Sales Order Processing invoice that was generated for a specific customer.

Dynamics GP’s RMA Life Cycle

When operating a returns warehouse, you sometimes interact with customers who need to return equipment for various reasons. When they bring you a part, you complete one of a few tasks: issue a credit, repair and return the item to the customer, or replace the item. You also may be accepting the item in exchange for an item you already provided to the customer. Once you’ve completed your transaction with the customer, you have a few more options: repair the item and return it to your inventory, return the item to the vendor, or scrap the item.

The following diagram outlines the life cycle of RMAs, from entry through completion. When Returns Management is integrated with the other modules of the Field Service Series (Service Call Management, Preventive Maintenance, Contract Administration, and Depot Management), many new options and paths become available.

RMA Life Cycle

You can create RMAs from two different points of access throughout Field Service:

  • Manual entry in the RMA Entry/Update window
  • From return lines for returnable items on a service call

Entry in the RMA Entry/Update window is the method described in this manual. Refer to the Service Call Management documentation for more information regarding service calls and returnable items.

RMA types Inside Dynamics GP

RMA documents are used to track an item return from your customers. The available RMA document types are as follows:

  • Credit – Provide a credit to your customer’s account in Receivables Management for the value of the items the customer returned to you.
  • Replacement – Provide the same item, or a similar item, as a replacement to your customer. You must receive the original item from your customer before you send the replacement item on a new order document in Sales Order Processing.
  • Advance Cross–ship – Provide the same item, or a similar item, as a replacement to your customer. You can send the replacement item using a new Sales Order Processing order document prior to receiving the original item from your customer.
  • Repair and Return – You, or your vendor, will repair the item that is received from the customer. Your customer will receive the item after it’s been repaired.
  • None – The customer’s original item is picked up by your field service technician and returned to your returns warehouse. This type of RMA document was designed to integrate directly with Service Call Management.

Analyzing Our RMA Data with Power BI

With all the RMA data we will be using the following three visuals and associated measures:

  • Rate of Return – This is an incredibly useful KPI in a distribution center, especially when segmented by cause for return. Identifying causes for returns — damage, late delivery, inaccurate product description, wrong item shipped, etc — helps warehouse managers address underlying issues, and make necessary improvements.Number of Units Returned/Number of Units Sold = Rate of Return.
  • Perfect Order Rate – This KPI measures how many orders your warehouse successfully delivers without incident: the correct item, shipped on time and received in good condition by the customer who ordered it. Lean practices help identify errors or inaccuracies before orders leave the warehouse.Orders Completed Without Incident/Total Orders Placed = Perfect Order Rate
  • RMA Pareto Analysis – Done on the top 20% of factors that drive 80% of the returns. This will make cause troubleshooting more efficient, leading to permanent solutions to problems that may be causing RMAs to begin with. I’m not going to go into detail on how to build this chart in this blog post. You can find the steps on how to complete it here: https://powerbi.tips/2016/10/pareto-charting/.

First, we need to get our Dynamics GP RMA data into Power BI and we’ll do that with the SQL script below:

WITH CTE
AS (SELECT
  300 AS Company_Key,
  b.RETDOCID AS [RMA Document ID],
  b.LNSEQNBR,
  a.Return_Record_Type,
  b.RETREF AS [RMA Reference],
  b.RETSTAT AS [RMA Status],
  b.RETTYPE AS [RMA Type],
  a.COMPDTE AS [DocDate],
  a.USERID,
  b.OFFID AS [Office ID],
  b.LOCNCODE AS [Location Code],
  a.CUSTNMBR,
  b.SOPTYPE,
  b.SOPNUMBE AS [Invoice Number],
  b.SVC_RMA_Reason_Code AS [RMA Reason Code],
  b.SVC_RMA_Reason_Code_Desc AS [RMA Reason Code Description],
  b.ITEMNMBR,
  b.QUANTITY,
  b.UNITCOST AS [Unit Cost],
  b.EXTDCOST AS [Extended Cost],
  b.[Credit_SOP_Number]
FROM dbo.SVC05200 b (NOLOCK)
JOIN dbo.SVC05000 a (NOLOCK)
  ON b.RETDOCID = a.RETDOCID
WHERE b.QUANTITY > 0
AND b.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) -- Didn't want everything so limiting to last 4 years of RMA data 
AND a.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) -- Didn't want everything so limiting to last 4 years of RMA data

UNION ALL

SELECT
  300 AS Company_Key,
  b.RETDOCID AS [RMA Document ID],
  b.LNSEQNBR,
  a.Return_Record_Type,
  b.RETREF AS [RMA Reference],
  b.RETSTAT AS [RMA Status],
  b.RETTYPE AS [RMA Type],
  a.COMPDTE AS [DocDate],
  a.USERID,
  b.OFFID AS [Office ID],
  b.LOCNCODE AS [Location Code],
  a.CUSTNMBR,
  b.SOPTYPE,
  b.SOPNUMBE AS [Invoice Number],
  b.SVC_RMA_Reason_Code AS [RMA Reason Code],
  b.SVC_RMA_Reason_Code_Desc AS [RMA Reason Code Description],
  b.ITEMNMBR,
  b.QUANTITY,
  b.UNITCOST AS [Unit Cost],
  b.EXTDCOST AS [Extended Cost],
  b.[Credit_SOP_Number]
FROM dbo.SVC35200 b (NOLOCK)
JOIN dbo.SVC35000 a (NOLOCK)
  ON b.RETDOCID = a.RETDOCID
WHERE b.QUANTITY > 0
AND b.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) -- Didn't want everything so limiting to last 4 years of RMA data
AND a.COMPDTE >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0)) -- Didn't want everything so limiting to last 4 years of RMA data

SELECT
  b.*,
  rs.STSDESCR AS [RMA Status Description],
  iv.ITMCLSCD AS [Item Class]
FROM CTE b (NOLOCK)
LEFT JOIN dbo.IV00101 iv (NOLOCK)
  ON iv.ITEMNMBR = b.ITEMNMBR
LEFT JOIN dbo.SVC05500(nolock) rs
  ON b.[RMA Status] = rs.RETSTAT
WHERE b.QUANTITY > 0

With the data now loaded into Power BI Desktop, I created relationships with my Inventory, Date and Customer dimension that I already had in Power BI Desktop from my previous blog posts.

Creating Our Measures

Time to build the Rate of Return Measure. Rather than create one big DAX measure we will be building several small measures that build upon each other. Here are the DAX formulas that we will be using for the Rate of Return measure:

  • Return of Lbs CY
Returns Lbs. CY = CALCULATE(
    ABS(
        SUM(Returns[Total Return Lbs])
        )
)
  • Lbs. CY

Lbs. CY = SUM(Sales[ExtendedWeight])

  • Rate of Return
Rate of Return = DIVIDE 
    ([Returns Lbs. CY], [Lbs. CY], 
    Blank()
 )
  • Monthly Average Rate of Return
Monthly Avg. Rate of Return = AVERAGEX( 
    DATESINPERIOD( 
        Dates[Date] , 
        LASTDATE( Dates[Date] ),
        -30,
        DAY),
    [Rate of Return]
)

With our calculations now complete, we create a line chart with Date on the Axis and Monthly Avg. Rate of Return for the Value as seen in the bottom left line chart visual of our Returns Analysis report screen shot below.

Up next, our Perfect Order Rate measure. Once again we will build several small calculation and add them together to create our Monthly Avg. Perfect Order Rate. Here are the DAX formulas that I used for this:

  • Total Invoice Count – Since my data includes line level detail for each Sales Order type in the Sales Table I’ll count all lines invoice and evaluate each line later to make sure it was a perfect order.
Total Invoices Count = CALCULATE(
    COUNTROWS('Sales'),
    FILTER('Sales','Sales'[SOPTYPE] = 3)
    )
  • Perfect Order – Now we will determine if the order was fulfilled before the customer’s required date.
Perfect Order = CALCULATE(COUNTROWS
    ('Sales'),
    FILTER('Sales','Sales'[SOPTYPE] = 3),
    FILTER('Sales',[Document Date]<='Sales'[Req Ship Date])
    ) 
  • Perfect Order Rate
Perfect Order Rate = DIVIDE(
    [Perfect Order],[Total Invoices Count],
    BLANK()
    )
  • Monthly Average Perfect Order Rate
Monthly Avg. Perfect Order Rate = AVERAGEX( 
    DATESINPERIOD( 
        Dates[Date] , 
        LASTDATE( Dates[Date] ),
        -30,
        DAY),
    [Perfect Order Rate]
)

With all of our DAX measures complete, we can create the visuals needs to Monthly Average Perfect Order Rate and Monthly Average Return Rate of Lbs. Here is what my final Returns Analysis report looks like.

RMA Dashboard

Until next time, Happy Analyzing!

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.