Tips and Tricks for Dynamics GP (part 2)

Three quick steps for accessing your Microsoft Dynamics GP data via Excel

Are you a Microsoft Dynamics GP user and would like to create your own SQL queries, as well as easily access your data and be able to share them with specific users? Well there’s nothing simpler.

During the installation of Microsoft Dynamics GP, SQL queries are created for each of the companies in your GL.

Did you know that it is possible to use those views and to display content from Excel, which will allow you to refresh, sort, filter, group and create subtotals?

Follow these steps on your local computer:

  • Be sure to have a valid ODBC connection to Microsoft Dynamics GP
    • Control Panel/Administrative Tools/ODBC Data Sources, in System DSN tab
ODBC-administrative-tool-1

 

  • Start Excel, open the ‘Data’ menu and select ‘From Other Source’ and ‘From Microsoft Query’
  • Select your ODBC connection in the ‘Databases’ tab; for example, ‘Dynamics GP 2013’
    choose-data-source-4
  • Log in as ‘Use Trusted Connection’ and press ‘OK’
  • In the ‘Available tables and columns’ tab, select a SQL view such as ‘AccountTransactions’ or ‘AccountSummary’ to display the GL transactions in Excel
  • Select all the fields to be displayed, along with the filter and sort using the ‘Next’ button for each step
  • Check ‘Return Data to Microsoft Excel’ and select cell A1
  • Use Excel to filter, sort, search for and/or create totals
  • Save the file, then next time, open the saved file and press ‘Refresh All’ under ‘Data’ to update the data from GP to Excel, without having to go through the same steps

 

 *** Smartlists are used in GP to display the SQL views ***

*** The name of the Smartlist is the same as the SQL view ***

 

Now create your own SQL queries, give users access and enjoy how easy it is!

Leave a Reply