Enhanced Dynamics GP Payables Workflow Reporting

A couple days ago, Gina left a comment on my 3 Secrets To An Awesome ERP System! blog post. She was wondering if it was possible on the Payables Transaction Workflow to include Document Type, Document Date, Document Number, Transaction Description, Vendor Name and Amount. The answer is YES, it is possible. The below SQL view only works with the Payables Transaction Workflow but can be modified to work with other transactional workflows. It will not work with batch workflows because batch IDs lack an unique ID to create the relationship against. Here is the modified SQL view to complete the request:

GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE VIEW [dbo].[vw_WorkFlow_Status]

AS

 WITH CTE_FINAL (WorkflowInstanceID, Workflow_Name, Workflow_Step_Name, Approver, Workflow_Action, Completion_Date, Completion_Time, Comments  )
AS
(select  d.WorkflowInstanceID, 
       d.Workflow_Name, 
       d.Workflow_Step_Name,
       CASE WHEN a.ADDisplayName is null THEN ''
          ELSE a.ADDisplayName
          END as [Assigned Approver],
       CASE WHEN d.Workflow_Action = 1 THEN 'Submit'
          WHEN d.Workflow_Action = 2 THEN 'Resubmit'
          WHEN d.Workflow_Action = 3 THEN 'Approve'
          WHEN d.Workflow_Action = 4 THEN 'Task Complete'
          WHEN d.Workflow_Action = 5 THEN 'Reject'
          WHEN d.Workflow_Action = 6 THEN 'Delegate'
          WHEN d.Workflow_Action = 7 THEN 'Recall'
          WHEN d.Workflow_Action = 8 THEN 'Escalate'
          WHEN d.Workflow_Action = 9 THEN 'Edit'
          ELSE 'Final Approve'
          END as Workflow_Action,
       convert(varchar(10),d.Workflow_Completion_Date, 101) as [Completion_Date],
       right('0'+LTRIM(right(convert(varchar,d.Workflow_Completion_Time,100),8)),7) as Completion_Time,
       d.Workflow_Comments
         from dbo.WF30100 d
LEFT JOIN WF40200 a ON d.Workflow_Step_Assign_To = a.UsersListGuid
WHERE d.Workflow_Action = 10), CTE_PM as (SELECT    P.VENDORID Vendor_ID,
    V.VENDNAME Vendor_Name,
    P.VCHRNMBR Voucher,
    CASE P.DOCTYPE
         WHEN 1 THEN 'Invoice'
         WHEN 2 THEN 'Finance Charge'
             WHEN 3 THEN 'Misc Charge'
             WHEN 4 THEN 'Return'
             WHEN 5 THEN 'Credit Memo'
         WHEN 6 THEN 'Payment'
         END Document_Type,
    P.DOCDATE Document_Date,
    P.PSTGDATE GL_Posting_Date,
    P.DUEDATE Due_Date,
    P.DOCNUMBR Document_Number,
    P.DOCAMNT Document_Amount,
    P.CURTRXAM Unapplied_Amount,
    P.TRXDSCRN [Description],
    CASE P.VOIDED
         WHEN 0 THEN 'No'
         WHEN 1 THEN 'Yes'
         END Voided

FROM    (SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
     DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
     FROM PM20000
    UNION ALL
     SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
     DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
     FROM PM30200
     UNION ALL
     SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
     DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, 0 as VOIDED
     FROM PM10000) P

     INNER JOIN
    PM00200 V
    ON V.VENDORID = P.VENDORID)
 
 Select  WF.* 
        ,coalesce(pm.Document_Type,'') as Document_Type
        ,coalesce(pm.Document_Date,'') as Document_Date
        ,coalesce(pm.Document_Number, '') as Document_Number
        ,pm.Voucher
        ,coalesce(pm.Description, '') as Description
        ,coalesce(pm.Vendor_Name,'') as Vendor_Name
        ,coalesce(pm.Document_Amount,0) as Document_Amount 
FROM 
 (SELECT    ROW_NUMBER() OVER ( ORDER BY A.DEX_ROW_ID ) RecordSequence ,
            ISNULL(C.WfBusObjKey, '') WFBusObjKey ,
            LEFT(WfBusObjKey, ISNULL(NULLIF(CHARINDEX('~', WFBusObjKey) - 1, -1), LEN(WfBusObjKey))) as Split,
            Workflow_History_User ,
            C.Workflow_Type_Name,
            A.Workflow_Name ,
            A.Workflow_Step_Name ,
            ISNULL(B.WorkflowTaskAssignedTo, '') WorkflowTaskAssignedTo ,
            CASE C.Workflow_Status
              WHEN 1 THEN 'Not Submitted'
              WHEN 2 THEN 'Submitted (Deprecated)'
              WHEN 3 THEN 'No Action Needed'
              WHEN 4 THEN 'Pending User Action'
              WHEN 5 THEN 'Recalled'
              WHEN 6 THEN 'Completed'
              WHEN 7 THEN 'Rejected'
              WHEN 8 THEN 'Workflow Ended (Depricated)'
              WHEN 9 THEN 'Not Activated'
              WHEN 10 THEN 'Deactivated (Depricated)'
              ELSE ''
            END AS WORKFLOW_STATUS ,
            CASE A.Workflow_Action
              WHEN 1 THEN 'Submit'
              WHEN 2 THEN 'Resubmit'
              WHEN 3 THEN 'Approve'
              WHEN 4 THEN 'Task Complete'
              WHEN 5 THEN 'Reject'
              WHEN 6 THEN 'Delegate'
              WHEN 7 THEN 'Recall'
              WHEN 8 THEN 'Escalate'
              WHEN 9 THEN 'Edit'
              WHEN 10 THEN 'Final Approve'
              ELSE ''
            END AS Workflow_Action ,
            A.Workflow_Due_Date ,
            A.Workflow_Completion_Date ,
            A.DEX_ROW_ID ,
            Workflow_Comments,
        CASE WHEN d.Approver is null THEN ''
          ELSE d.Approver
       END as Approver,
       CASE WHEN d.Completion_Date is null THEN ''
          ELSE d.Completion_Date
       END as Completion_Date,
       CASE WHEN d.Completion_Time is null THEN ''
          ELSE d.Completion_Time
       END as Completion_Time,
       CASE WHEN d.Comments is null THEN ''
          ELSE d.Comments
       END as Comments
    FROM    WF30100 AS A
            LEFT OUTER JOIN WFI10004 AS B ON A.WorkflowInstanceID = B.WorkflowInstanceID
            AND A.WorkflowStepInstanceID = B.WorkflowStepInstanceID
            LEFT OUTER JOIN WFI10002 AS C ON C.WorkflowInstanceID = A.WorkflowInstanceID
            LEFT OUTER JOIN CTE_FINAL d ON D.WorkflowInstanceID = c.WorkflowInstanceID) WF
            LEFT OUTER JOIN CTE_PM pm on pm.Voucher = WF.Split
GO

Until next time. Enjoy the code!

Advertisements

3 Secrets To An Awesome ERP System!

Part of being a Microsoft Dynamics Consultant is seeing all the inventive ways clients use the software we deploy and take that knowledge and provide best practices to everyone! Here are just some of the things to enhance your system processes:

  • Best Practices
  • Customization and Modifications
  • Reporting Enhancements

Best Practices

Customization, Modifications and Third Party Add-ins

  • General Ledger, Sales Distribution, and Payables Distribution intercompany Excel paste – James Lyn’s Excel Paste add-in. is great at extending Dynamics GP’s out of the box functionality. While you’re on his site, check out his other add-ins like GP batch attach for Payables.
  • Create Dynamics GP Marcos or use PowerShell scripts to automate tasks – i.e. Macro to log into Dynamics GP and run inventory reconcile process or reboot your web client servers to remove hung processes.
  • Custom workflows and reporting – reporting to provide detail information on current/open and historical/approved workflows. Find out how to do that here.

Reporting Enhancements

Dynamics GP comes with some good reporting capabilities:

  • Management Reporter.
  • Excel refreshable reports.
  • SmartList.
  • Jet Express for Dynamics GP.
  • Solver’s BI360.
  • Power BI.

With Dynamics GP 2018 you can now deploy Power BI GP content pack or embedded Power BI visual inside of Dynamics GP. So what do the Power BI content pack visuals look like and how do we get them installed? As of Microsoft Dynamics GP 2018, the GP OData service was updated to OData version 4. This redesign also brought on paging and filtering of OData requests. This will create a more stable and robust platform for delivering Microsoft Dynamics GP content to authenticated users. The Power BI content pack features sample reports for Financial, Sales, Purchasing and Inventory data. Each report utilizes relationships built between GP tables and various Filters that can be used to display the information that is important to you. You can also review the included Calculated Columns as examples for including calculations on your Power BI reports such as Net Debit/Credit, Profit, and Item Sales amounts.

In order to use the Power BI Content Pack with Dynamics GP, install OData Services. Once this is complete, you will have to publish the following Data Sources inside Dynamics GP. (Administration > System > OData > Data Sources) The following Data Sources will need to be published for the GP content pack.

  • Accounts
  • Account Transactions
  • Customers
  • Inventory Sales Summary Period History
  • Inventory Transactions
  • Item Quantities
  • Purchase Line Items
  • Purchase Requisition Lines
  • Purchase Requisitions
  • Receiving Line Items
  • Sales Line Items
  • Vendors

The Power BI Content Pack will also have to be configured to point to you’re existing Microsoft Dynamics GP OData Service. This can be done by modifying the existing Data source in Power BI, or by creating a new data source and configuring the content to use the new source. The screenshots below show what the GP content pack sample reports look like.

Finance Dashboard

Finance

Sales Dashboard

Sales

Purchasing Dashboard

Purchasing

Inventory Dashboard

Inventory

Enhancing Our Dashboards

These GP content pack reports are a good starting point and can speed up the process of implementing a Power BI solution. With a little work from your Microsoft Dynamics Consultant, we can provide you so much more. In February of 2017, I started a blog series that provided a “how to” on building a Finance, Sales, Customer, Product, and Inventory dashboard. Since my main goal was to show an update of the Excel multi-company dashboard, I choose to first build a small DataMart and integrate the data from Dynamics GP databases before building my visuals. Follow the links below to find out how I built each one of the enhanced dashboards.

Enhanced Finance Dashboard

The finance dashboard, from the blog series, now provides a summary profit & loss statement that you can drill down into line level detail. Additionally, you see total sales by inventory class and customer class and a weekly moving average.

Enhanced Finance Dashboard

Enhanced Sales Dashboard

The sales summary dashboard provides an analysis of sales by inventory item class, customer class and weekly moving averages for total sales dollars and transactions. The report also includes a cumulative sales and cost comparison.

Enhanced Sales

Product Performance and Inventory Reorder Dashboard

In the product performance dashboard, I’ve added to compare total profits and profit margin, cost by warehouse and ABC segmentation analysis. Additionally, there is a weekly moving average cost comparison chart at the bottom of the report.

Enhanced Product Performance

For my inventory reorder analysis report, I’m using some discussed by Belinda Allen in her, Inventory Item Reorder Dashboard. I converted it to Power BI to help your procurement manager evaluate what’s on hand, allocated to open orders and items sold within a given time period.

Enhanced Inventory Dashboard

Future Developments

Returns are generally thought of a loses and return percentages can be dependent both the type of product and the company’s returns policy. While the average industry rate is four percent, consumer durable goods can range from two to 10 percent and apparel can be in excess of 20 percent. There are several reasons for merchandise returns and tracking the costs and reasons associated with them can increase revenues, lower costs, improved profitability and enhanced levels of customer service. Using Dynamics GP’s RMA module can help and analyze that data in Power BI is one of the things that I’m currently working on. Below is a screenshot of that analysis and a subject of a future blog post.

Sales Returns Dashboard

Ready to Do Even More with Dynamics and Power BI?

Stay tuned for more help in lead your organization into becoming data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP journey.

How to Enhance Dynamics GP’s Inventory BOM Report Using SSRS

During a recent project for a client they want to enhance their custom inventory BOM report made by another vendor. Not having access to the original code, I proposed modifying the Bill of Materials Maintenance window to add a print button and developing the report in SSRS.

Modifying the window

1. Log into Dynamics GP and open the Bill of Material Maintenance window in the Inventory Module.

2. On the window navigate to Tools>>Customize>>Modify Current Window. This will open modifier.

Dynamics GP BOM Window

3. Inside of Modifier, click on the “OK” button from the toolbox on the left and drag it onto menu bar of the Bill of Material Maintenance window. As shown on the below screen shot.

BOM window in Modifier

4. Open the Properties of the new “OK” button and change the text for the button. I changed mine to “Print BOM”. Save the changes and Exit Modifier to get back into Dynamics GP.

BOM window in Modifier 2

Adding VBA code to our window

1. With the Window now modified, we need to navigate back to Tools>>Customize>>Add Current Window to Visual Basic. We also need to add the “Print BOM” button and Bill Number field to Visual Basic by selecting the “Add Fields to Visual Basic…” menu.

Add BOM window to VBA Project

2. Now open up Visual Basic Editor, Tools>>Customize. We need to make the button functional by adding some VBA code.

BOM window in VBA

a. First let’s add the References that we need for the project by navigating to Tools>>References from the menu bar. Add or verify that the following References are selected: Visual Basic for Applications, Microsoft Dynamics GP VBA 18.0 Type Library, OLE Automation and Microsoft ActiveX Data Objects 2.1 Library.

Dynamics GP VBA project references

b. Before writing our VBA code we should verified that the window and both fields were added to the VBA project. We can do that by click on the dropdown menu where you see “(General)”. Your screen should look similar to my screen shot below.

VBA project windows and fields

c. Add the following VBA code Object Browser. You will need to change the highlighted VBA code based your SSRS server name, folder location of the report that we will be building and the name of the SSRS report. In the VBA code below:

i. XXXXX – would be my SSRS server name

ii. TWO – is the Folder that I saved the report in

iii. BOM Indented is what I named my report

Sub window_Open(strLocation As String, Menubar As Boolean, top As Long, left As Long, height As Long, width As Long, resizable As Boolean)

With CreateObject(“InternetExplorer.Application”)

.Visible = False

.top = top

.left = left

.height = height

.width = width

.Menubar = Menubar

.Visible = True

.resizable = resizable

.Navigate strLocation

End With

End Sub

Private Sub PrintBOM_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

Dim IBOM

Dim IE As Object

IBOM = CStr(BillNumber)

window_Open “http://XXXXXXX/ReportServer/Pages/ReportViewer.aspx?%2fTWO%2fBOM+Indented&rs:Command=Render&BOMItem=” & IBOM & vtype, False, 10, 10, 750, 1250, True

End Sub

Creating the stored procedures for the SSRS report.

Now the tricky part, dealing with the undefined amount of levels that a BOM hierarchy can go down. With the help of Google, I found this forum post – https://community.dynamics.com/gp/f/32/t/39209?pi53330=2#responses. Original credit and thanks to Beat Bucher and Tim Foster for the post and original SQL code. I have modified the original stored procedure to fit my client’s needs. Here is the stored procedure code that I used:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_BOM_Level_Details] (@current char(31)–, @BOMType smallint

) AS

/*

DROP TABLE #result;

Drop Table #stack;

*/

–DECLARE @current char(31) –> use the 2 lines here to test directly in SQL Studio Mgmt

–SET @current=’BB00069E0000BT9′

SET NOCOUNT ON

–if @current is null set @current = ‘BB00069E0000BT9’

DECLARE @lvl int, @line char(31), @Qty numeric(19,5), @ORD1 int, @UOFM char(9), @comptitm char(31)

CREATE TABLE #stack (item char(31), comptitm char(31), Design_Qty numeric(19,5), ORD1 int, UOFM char(9), lvl int)

CREATE TABLE #result (lvl int, item char(31), comptitm char(31), Qty numeric(19,5), ORD1 int, UOFM char(9),

ord int identity(1,1))

INSERT INTO #stack VALUES (@current, ”,1,1,”,1)

SELECT @lvl = 1

WHILE @lvl > 0

BEGIN

IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)

BEGIN

SELECT @current = item, @comptitm = comptitm, @Qty = Design_Qty, @ORD1 = ORD1, @UOFM = UOFM

FROM #stack

WHERE lvl = @lvl

— SELECT @line = replicate(‘-‘,(@lvl – 1)) + ‘ ‘ + @current –> spacing by level

–PRINT @line –> replace this print with an INSERT to another table like #BOM

INSERT #result SELECT @lvl,@current, @comptitm, @Qty, @ORD1, @UOFM

DELETE FROM #stack

WHERE lvl = @lvl

AND item = @current

INSERT #stack

SELECT CMPTITNM, ITEMNMBR, Design_Qty, ORD, UOFM, @lvl + 1

FROM BM00111 –> I edited this for GP

WHERE ITEMNMBR = @current and Component_Status = 1 –and BOMCAT_I = @BOMType –1 –> added BOMCAT for MFG(1) or ENG(2)

ORDER BY ORD –Order by Part Number as in GP Reports CMPTITNM

–ORDER BY POSITION_NUMBER ASC –Order by Position Number–other sequence in GP

IF @@ROWCOUNT > 0

SELECT @lvl = @lvl + 1

END

ELSE

SELECT @lvl = @lvl – 1

END — WHILE

drop table #stack

;WITH CTE as (select ITEMNMBR FROM BM00101)

SELECT T1.lvl,

CASE WHEN T1.lvl = 1 THEN ”

ELSE RTRIM(T1.item)

END as item,

CASE WHEN T1.lvl = 1 THEN RTRIM(T1.item)

ELSE RTRIM(T1.comptitm)

END as comptitm,

T1.Qty, T1.ORD1,

RTRIM(T1.UOFM) as UOFM,

T1.ord

,T2.ITEMDESC

,T2.STNDCOST

,CASE WHEN T1.item in (select ITEMNMBR FROM CTE)

THEN 0

ELSE T2.CURRCOST

END AS CURRCOST

,CASE WHEN T1.item in (select ITEMNMBR FROM CTE) THEN 0

ELSE (T2.CURRCOST * T1.Qty)

END as [Ext Cost]

FROM

#result T1 INNER JOIN IV00101 T2

ON T2.ITEMNMBR = T1.item

ORDER BY ord;

DROP TABLE #result;

GO

Building our SSRS BOM Indented Report

SSRS (SQL Server Reporting Services) is a server-based reporting system from Microsoft and part of the SQL server stack. It can be used to prepare and deliver prints through a web site. You can build your reports with either Report Builder or Visual Studio. For this project, we are going to be using Report Builder. Let’s start creating our Indented BOM report using a wizard.

Creating the Data Connection

1. Start Report Builder either from your computer or the Reporting Services web portal. Select New Report in the left pane and Table or Matrix Wizard in the right pane.SSRS wizard

2. Specify a Data Connection in the Table Wizard

A data connection contains the information to connect to an external data source such as a SQL Server database. Usually, you get the connection information and the type of credentials to use from the data source owner. To specify a data connection, you can use a shared data source from the report server or create an embedded data source that is used only in this report.

3. Click the New Button to create a new Data Source and then build on the Data Source Properties window that opens.SSRS Data Source

4. Enter your Connection Properties for the SQL Server and Dynamics GP company database.

SSRS Data Source connection

5. Click the General tab again. To verify that you can connect to the data source, click Test Connection.

The message “Connection created successfully” appears.

6. Click OK.

Creating our Datasets

1. Right click on your new Data Source to create a Dataset.

2. Choose to use a dataset embedded in my report and a Query type of Stored Procedure. Choose the stored procedure we created earlier. (usp_BOM_Level_Details)

SSRS Dataset

3. Add a Parameter for the Dataset Properties. I named mine @BOMItem

Organize Data into Groups in the Table Wizard

When you select fields to group on, you design a table that has rows and columns that display detail data and aggregated data. To start organizing your data into groups:

1. Navigate to the Insert menu, click on the Table icon and select Table Wizard.

SSRS Table Wizard

2. On the New Table or Matrix window drag the lvl, comptitm, item, ITEMDESC and UOFM to the Row Groups section. Drag the Qty, CURRCOST and Ext_Cost fields to the Values Section.

SSRS Table or Matrix Wizard

3. Click Next and de-select Expand/collapse groups.

Table Wizard step two

4. Remove the sub-grouping totals, format the numbers to the forth decimal place and change the row height to 0.025. Your table should look like the below screen shot when your complete.

clip_image030

5. Change the Tablix Properties for the Row and Column Headers to Repeat on each page.

Tablix Properties

The Final Product

Once complete, you should have a Dynamics GP Bill of Materials Maintenance window like the below screen shot and when you select a Bill Number and click the Print BOM button the SSRS report will open and produce the results in the second screen shot. You can download zip file the stored procedure, Dynamics GP package file and SSRS rdl report from here.

Modified Dynamics GP BOM Window

SSRS Indented Inventory BOM Report

Ready to Do Even More with Dynamics and Business Intelligence?

Check out my other blog posts that can help your company become data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM.

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.

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.

SSIS and PowerShell – A Powerful Combination

PowerShell is a powerful task automation tool from Microsoft. Although SSIS does not provide something like Execute PowerShell Script Task out of the box, it does have an Execute Process Task which can be used to run PowerShell scripts just as easily.

On a recent project, I was tasked with downloading and updating Federal Election Commission, FEC.GOV, Committee and Candidate Election data into Dynamics GP vendor maintenance and eOne Solutions Extender tables. To accomplish this, I created a PowerShell script to download the zip files for the FEC.GOV web site, added this script inside a SSIS package Execute Script task and then added additional data flow tasks to un-zip the files and import into custom SQL tables for use with a SmartConnect integration into Dynamics GP. The final SSIS package looked like the screen shot below.

SSIS Package Control Flow

Here are the detailed steps to create this SSIS package:

1.  Create the two custom tables

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FEC_Candidate](

[Candidate_ID] [varchar](9) NOT NULL,

[Candidate_Name] [varchar](200) NULL,

[Party_Affiliation] [varchar](3) NULL,

[Year_of_Election] [numeric](4, 0) NULL,

[Candidate_State] [varchar](2) NULL,

[Candidate_Office] [varchar](1) NULL,

[Candidate_District] [varchar](2) NULL,

[Incumbent_Challenger_Status] [varchar](1) NULL,

[Candidate_Status] [varchar](1) NULL,

[Principal_Campaign_Committee] [varchar](9) NULL,

[Street1] [varchar](35) NULL,

[Street2] [varchar](35) NULL,

[City] [varchar](35) NULL,

[State] [varchar](2) NULL,

[Zip] [varchar](9) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[FEC_Committee](

[Committee_ID] [varchar](50) NOT NULL,

[Committee_Name] [varchar](200) NULL,

[Treasurer_Name] [varchar](100) NULL,

[Street1] [varchar](50) NULL,

[Street2] [varchar](50) NULL,

[City] [varchar](50) NULL,

[State] [varchar](50) NULL,

[Zip] [varchar](50) NULL,

[Committee_Designation] [varchar](50) NULL,

[Committee_Type] [varchar](50) NULL,

[Committee_Party] [varchar](50) NULL,

[Filing_Frequency] [varchar](50) NULL,

[Interest_Grp_Category] [varchar](50) NULL,

[Connected_Org_Name] [varchar](200) NULL,

[Candidate_ID] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

2.  Delete Extracted FEC Files – Delete the previous downloaded zip files

a.  Add Script Task to the Control Flow

b.  Add C# script to delete previous extracted files – Add the Script Task to the Control Flow and give it a name “Delete Extracted FEC Files”. Then edit it by choosing the Script Language (C# or VB.NET). I used C# for my solution.

Script Task Editor

C# delete files script

public void Main()

{

string directoryPath = @”D:\KTL\FEC_Downloads\Extract”;

string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, “*.txt”);

foreach (string currFile in oldFiles)

{

FileInfo currFileInfo = new FileInfo(currFile);

currFileInfo.Delete();

}

// TODO: Add your code here

Dts.TaskResult = (int)ScriptResults.Success;

}

3.  Truncate FEC tables – Truncate the custom tables from the previous download

4.  Download FEC files from Website – Attached the below PowerShell script to this data flow task to get the updated FEC data

a.  Create PowerShell script and save as a .ps1 file

Invoke-WebRequest ftp://ftp.fec.gov/FEC/2018/cm18.zip -OutFile D:\KTL\FEC_Downloads\Archive\Committee.zip

Invoke-WebRequest ftp://ftp.fec.gov/FEC/2018/cn18.zip -OutFile D:\KTL\FEC_Downloads\Archive\Candidate.zip

Invoke-WebRequest ftp://ftp.fec.gov/FEC/2018/ccl18.zip -OutFile D:\KTL\FEC_Downloads\Archive\Link.zip

b.  Add PowerShell.ps1 to SSIS Execute Process task – Add the SSIS Execute Process task to the Control Flow and add PowerShell.exe to the Executable field and –F D:\KTL\FEC_Downloads\FEC_Download.ps1 to the Arguments field. The Arguments will need to change based on your system file location and name.

Execute Process for PowerShell script

5.  Foreach loop to Extract the zip files

a.  Add a Variable to the SSIS package – Add a variable to the package named “ZipFullPath”

SSIS Package variable

b.  Add a Foreach Loop to the Control Flow and then add then the following to the Collection and Variable Mappings within the Foreach Loop Editor

Foreach Loop folder location

Foreach Loop variable

6.   Add a Script Task inside the Foreach Loop

a.  Inside the Foreach Loop add the Script Task and open the Editor. Use C# with the ReadOnlyVariables shown below.

C# Script Editor

b.  Click on the Edit Script and add the following script

public void Main()

{

string zipfullpath=Dts.Variables[“User::ZipFullPath”].Value.ToString();

string inputfolder=Dts.Variables[“$Package::InputFolder”].Value.ToString();

using (ZipArchive arch=ZipFile.OpenRead(zipfullpath))

{

foreach(ZipArchiveEntry entry in arch.Entries)

{

entry.ExtractToFile(Path.Combine(inputfolder,entry.FullName));

}

}

File.Delete(zipfullpath);

// TODO: Add your code here

Dts.TaskResult = (int)ScriptResults.Success;

}

The final script should look like this:

C# zip extract script

7. Add a Data Flow task to the Control Flow – Add a Data flow task and two data flow from the Committee text file and one for the Candidate text file into each of the custom tables created in the first step.

Data Flow screen shot

Conclusion

Hope this helps show you some of the possibilities that you can create by combining PowerShell with SSIS.

Sharing the Wealth – Sorry not $$$ just Knowledge

Recently, I created several Power BI visuals on Dynamics GP data and had a request to share the SQL scripts I used to import into my Power BI model’s datasets. The previous Power BI datasets and visuals included:

  • General Ledger financial statement and analysis
  • Inventory reorder point analysis
  • Procurement analysis
  • Sales summary
  • Customer analysis
  • Sales Return (RMA) analysis

Most of the scripts where SQL views and can be used to import into Power BI or in SSIS to import into a Data Mart. Some SQL scripts will need to be changed to meet your needs or fit your environment. The zip file can be found here. Hope this helps with your quest for knowledge.