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
- Perform Bank Reconciliation process on a daily basis or use Electronic Bank Reconciliation.
- PowerShell – Automate SQL Server maintenance and tasks with dbatools.
- Clean up and verify vendor information – Validate TIN against www.irs.gov site, create a SmartList to compare vendor ID, vendor name, address, and TIN.
- Reconcile sub-ledger accounts using GL Reconcile – Tools>>Routines>>Financial>>Reconcile to GL.
- SQL Server settings and maintenance plan – Ola Hallengren SQL maintenance plan, MDF & LDF file location, TempDB settings and RAM settings.
- Setup Dynamics GP Workflows – Here are some blog post to help:
- Derek Albaugh Blog Post Series
- Ian Grieve Workflow blogs – www.azurecurve.co.uk/category/microsoft/microsoft-dynamics/microsoft-dynamics-gp/workflow/
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.
Dynamics GP comes with some good reporting capabilities:
- Management Reporter.
- Excel refreshable reports.
- 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.
- Account Transactions
- Inventory Sales Summary Period History
- Inventory Transactions
- Item Quantities
- Purchase Line Items
- Purchase Requisition Lines
- Purchase Requisitions
- Receiving Line Items
- Sales Line Items
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.
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 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.
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.
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.
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.
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.