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 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=&#8221; & 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.