Chapter 1. Getting Data from Dynamics GP 2016 to Excel 2016
In more than 25 years of experience working in enterprise resource planning (ERP) consulting, every customer I've worked with put something of importance in Microsoft Excel. Why? The same reason you are reading this book. We all know how to use it, we like using it, and the people we share reports with know how to use it. However, we all want to know more about Excel, we all want to use it better, and (I cannot state this in big enough font) we want to impress someone with how great we are at using it. Yes, this means we want to share files and reports that make our colleagues and managers say, "Wow, how did you do this?," all while creating and managing these reports in less time.
Together, we will explore the power of Microsoft Excel 2016 and Microsoft Dynamics GP 2016 where we will build simple dashboards that looks anything but simple. Don't worry; we will not be doing any of the "developer-ish" stuff such as named ranges, macros, or VLOOKUPs. Microsoft has added so many amazing features to Excel that you can create amazing reports and dashboards all using native tools.
Before we can build a great Excel-based dashboard using the data in Dynamics GP 2016, we have to get the data out of GP and into Excel. This chapter covers eight major ways to get data from Dynamics GP into Excel, with a few extra options thrown in at the end.
By the end of this chapter, you should be able to get data into Excel using:
- SmartList exports
- SmartList Export Solutions
- Get and Transform (formerly Microsoft Power Query)
- Office data-connection files
- SQL Server Reporting Services (SSRS)
- SQL Server Management Studio (SSMS)
- Jet Reports Express for Excel
- Analysis cubes
Tip
Occasionally, we will reference either the Rich Client or the Web Client for Dynamics GP 2016. If you are not sure which option you have, follow the directions for the Rich Client.
- Dynamics Rich Client: This is when Dynamics is installed directly on the machine
- Dynamics Web Client: This is when Dynamics is accessed through a browser (Internet Explorer, Chrome, Firefox, Safari, and so on)