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.

May the Force of PowerShell Module dbatools be with you

Dbatools is a great PowerShell module that has 106 ways to help ease the administrative burden faced by SQL Server DBA’s and developers. As a IT Consultant that handles Microsoft Dynamics products that install within SQL Server, I would suggest that this is a great module for us to know and use too.

For those that may not know what PowerShell is, it was developed by Microsoft for task automation and configuration management. You can learn more about PowerShell at Microsoft Virtual Academy

How to Install

There are four ways to install dbatools module into PowerShell. The PowerShell command that I used was:

Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in)

The complete instructions on how to install the module can be found on dbatool’s download page: https://dbatools.io/download/

Getting Started

There are several scenarios to use dbatools. The developer’s goal with every command is to support:

  • SQL Server 2000 – 2016
  • Express – Datacenter Edition
  • Clustered and stand-alone instances
  • Windows and SQL authentication
  • Default and named instances
  • Multiple instances on one server

You can learn more about dbatools and the Functions associated with the PowerShell module here: https://dbatools.io/getting-started/

Functions to Know for Dynamics

As IT Consultant in the Microsoft Dynamics space, we are sometimes tasked with setting up SQL Server and/or migrating the Dynamics databases to the client’s new SQL Server. Some of the key functions I have used in the past to do this are:

clip_image001 Databases

clip_image002 Security

clip_image003 Server Objects

clip_image004 SSIS

clip_image005 Management

clip_image006 SQL Server Agent

Best Practice Commands

General Administration Commands

A complete list of the 106 Functions contained in the current version, 0.8.693. Can be viewed here, https://dbatools.io/functions/, and just follow the link for each Function on that page to get the details on that Functions use.

Conclusion

Until next time, I hope this helps you with your SQL Server Administration and database migrations.

Dynamic Security – a look at the final product

Introduction

Recently I posted a blog titled “How to implement Analysis Services Dynamic Security based on Microsoft CRM user access rights”. That post talked about how to implement the back-end structure needed to put it in place but didn’t show the result of all that hard work.

The dynamic row level security (RLS) that we setup inside Analysis Services will work within reports and dashboards built inside Reporting Services, Power BI and Excel. Power BI has other ways of deploying RLS when not using an Analysis Services connection. You can learn more about that here:

· http://sqlkover.com/dynamic-security-in-power-bi/

· http://radacad.com/row-level-security-configuration-in-power-bi-desktop

The End Result

We built are Dashboard in Power BI Designer and published the Dashboard to our end users. One of the end users “Tom” has access to 558 CRM Customer account within the US. When he accesses the Dashboard, his can view results that he has privileges to. (See Figure 1 below)

Power BI dashboard image 1

Figure 1 – Tom’s access view

If “Tom” tries to access an area that he doesn’t have privileges to, say Canada, the dashboard allows him to select the filter and changes the chart(s) results to show no data. (See Figure 2 below)

Power BI dashboard image 2

Figure 2 – Tom’s view with Canada selected

Additionally, the chart(s) change based on the end user that’s accessing the dashboard. In Figure 3 below, I show an administrative user accessing the dashboard and the result shown to them when selecting the “Canada” filter.

Power BI Dashboard image 3

Figure 3 – Administrative user view of Canadian Sales

Conclusion

I hope this helps you with your future Analysis Services development and Dashboard projects.

How to Create GP Workflow 2.0 Custom Reporting

Recently, I completed a GP 2015 R2 upgrade with Workflow setup for vendor and check batch approval. Microsoft has made changes to the Workflow to move it off SharePoint and the old Workflow version’s reporting capabilities could only be accessed through the Workflow web site. Knowing that the client was subjected to audit reviews on a quarterly basis, I needed to come up with a way for them to produce reports on both current/open and historical/approved workflows. I created the following SQL view in their company database and then in GP used SmartList Designer to create a SmartList based on the view.

SET ANSI_NULLS ON

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)

Select –c.WorkflowInstanceID,

c.Workflow_Name,

c.Workflow_Type_Name,

c.Workflow_Originator,

c.WfBusObjKey as Approval_Request,

CASE WHEN c.Workflow_Status = 1 THEN ‘Not Submitted’

WHEN c.Workflow_Status = 2 THEN ‘Submitted’

WHEN c.Workflow_Status = 3 THEN ‘No Action Needed’

WHEN c.Workflow_Status = 4 THEN ‘Pending User Action’

WHEN c.Workflow_Status = 5 THEN ‘Recalled’

WHEN c.Workflow_Status = 6 THEN ‘Completed’

WHEN c.Workflow_Status = 7 THEN ‘Rejected’

WHEN c.Workflow_Status = 8 THEN ‘Workflow Ended’

WHEN c.Workflow_Status = 9 THEN ‘Not Activated’

ELSE ‘Deactivated’

END as Workflow_Status,

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 dbo.WFI10002 c

LEFT JOIN CTE_FINAL d ON D.WorkflowInstanceID = c.WorkflowInstanceID

GO

Grant Select on dbo.vw_WorkFlow_Status to DYNGRP

Once your done adding the SQL view you can create a SmartList report with SmartList Designer and the complete SmartList should look like the screen shot below.

SmartList Workflow Report

Dynamics GP SmartList default produces no data

Recently when working with the client that has eOne Solutions SmartList Builder installed they came across an issue where their default (*) Sales Line Items SmartList was not producing data when they clicked on it.  Dynamics GP would freeze and the process would be stuck in the Process Monitor.  When you install the SmartList Builder add-in to Dynamics GP, it copies all of your SmartLists and the favorites that you created into eOne Solution tables.  Any Dynamics GP user that selects the “Modify” button on the SmartList window will duplicate any default SmartLists and cause the issue of that SmartList then not producing any data. This button’s functionality should be removed from the general GP users security role to eliminate this from happening in the future.

GP SmartList 1

To fix the issue follow the steps below –

  1. Open SmartList Builder

GP SmartList 2

  1. Select “Existing” and then any SmartList ID with “*” at the end. (i.e. Sales Line Items* in screen shot)

GP SmartList 3

  1. Select the SmartList and then click on “Delete” button to remove modified default SmartList causing the issue.

GP SmartList 4

4. Select SmartList and the Default SmartList you were having an issue with and it should now produce data with no issues.