Dynamics GP interfaces with Excel in several different ways. The most common ways are through built in functions like Smartlists, Excel Budgets, and Dynamics pre-defined Excel reports. These are the base interfaces, but there are also several other methods to link GP data to Excel. Some of the other ways are through an ODBC driver, a third party add-ons, an Excel connection, etc. In this article, we will discuss the base methods as well as the Excel connection, as these are the quickest ways to get linked.
There are several ways that Excel can be used with Microsoft Dynamics GP:
SmartList
SmartList is a very powerful reporting tool found in Dynamics GP that provides custom results based on criteria presented by the user. Once the user has created the desired results in the SmartList utilizing the Search and Column buttons on the SmartList toolbar, the results can then be exported to Excel utilizing the Excel button on the SmartList toolbar. This allows the user to further customize the SmartList results by adding custom sorts, groupings, calculations, formats, etc. and other features found in Excel.
SmartList also has the capabilities to create more advanced exports to Excel utilizing the Export Solutions option found under the SmartList Options toolbar button. This feature allows the user to not only export to Excel, but apply Macros prior to or post Export.
Excel Budgets
Excel Budgets interface with Dynamics GP. When creating a new budget in Dynamics GP, the options to create the budget “using Microsoft Dynamics GP” or “using Budget Wizard from Excel”. To utilize and Excel Budget, select the “using Budget Wizard from Excel”. This will kick off the budget wizard in Dynamics GP to create a new budget that links to an Excel spreadsheet.
Dynamics GP budgets can be imported from or exported to Excel. This allows the user to change how the budget data is stored either from Dynamics GP or Excel.
Excel Reports for Dynamics GP
Excel Reports for Dynamics GP offers options for linking to Dynamics GP data. It contains numerous pre-defined reports that may be modified that are directly linked and refreshable. It further is a great tool for quickly creating custom reports directly linked to Dynamics GP data. This tool allows the user to quickly build a report within Dynamics GP and utilize the robust analytical features found in Excel.
Excel Direct links to Dynamics GP
Similar to the Excel Reports for Dynamics GP, from Excel, you can create a direct link to Dynamics GP SQL data. This allows you to link directly to views and tables in GP, which may be refreshed with the current data, when the report is open.
· Open Excel
· Select the Data tab
· Select From Other Sources
· Select From SQL Server
· Enter the name of the server
· Enter Credentials (AD or SQL)
· Select the Database from the drop down list where you want to get the data from
· Locate and select the table or view from the list
· Click Next
· Click Finish
· Select the Type of Connection
· Select OK
· View, Customize, and Format the Report in Excel
Want more follow up? Have questions? Contact our consultants at 301.360.0001.
ANDREW FRASER |Senior Business Software Consultant
As a Senior Business Consultant, Andrew is responsible for the implementation and support of CRM, GP, and other Microsoft products assuring that our solution scoping, architecting, and delivery meet and exceed customer expectations. He helps clients analyze their current business processes and provide them with process improvements. During his time at KTL, Andrew has built lasting relationships with clients by understanding their business needs and provided value by leveraging their existing resources. Andrew has more than nineteen years of experience in implementing business software for a variety of industries including service, government, distribution, not-for-profit and manufacturing. He graduated with a Bachelor’s Degree in Math with concentration in Computer Science and a Bachelor’s Degree in Business Marketing from St. Andrew’s University in Laurinburg, North Carolina. His experience includes helping companies embrace technology to fulfill their organizational needs and creating innovative solutions using CRM, GP and other Microsoft products.
