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 that KTL Solutions’ Microsoft Dynamics Consultant can provide to your company:
- Best Practices
- Customization, Modifications and Third-Party Add-ins
- Reporting Enhancements
Best Practices
- Perform Bank Reconciliation process on a daily basis or use Electronic Bank Reconciliation.
- PowerShell – Automate SQL Server maintenance and tasks with dbatools.
- Use Professional Service Tools Library (PSTL) – Combine/modify vendors and customers, accounts, Inventory reconcile, Document date verify, etc. Find out all about these tools great functionality from Andrew Fraser’s blogs series.
- Use Power BI for dashboards, KPIs, reports, and sub-ledger reconciliations.
- Deferred Revenue/Expenses module.
- Clean up and verify vendor information – Validate TIN against 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 – 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.
- Willoware’s Free SpellCheck application – SpellCheck for Dynamics GP provides spell-checking on any Note, Comment, Text or String field in GP and 3rd party products. SpellCheck can be attached to any text entry field in the application, and it works in the Web Client. Download it here.
- EthoTech Free SmartSort – With SmartSort, you can automatically sort lookup information your way! You’ll quickly find all your important information in your system by customizing your own view based on built-in advanced sorting options or SmartList favorites. Find out more about it and download it here.
- Custom SSRS Reports – Multi-level SSRS BOM Report, multi-company fixed assets report.
- Custom Excel Reports – Create data-connected Excel reports to review contracts, contracts deferred revenue, sales by state, etc.
- Custom window modification or use eOne Solutions Extender application to add new windows and menus.
- Custom workflows and reporting – reporting providing 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 GP2018 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 your 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
Sales Dashboard
Purchasing Dashboard
Inventory Dashboard
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.
Future Developments
Returns are generally thought of as losses and return percentages can be dependent on 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?
As a full-service technology consulting company, KTL Solutions works with business leaders every day in helping them lead their organization into becoming data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM journey. Need help with your journey?
