I love Dynamics GP and Microsoft Excel. What are the options to use them together?

For those of us in the Dynamics GP community that love to use Excel for reporting purposes, we have a few different options for viewing information from Dynamics GP directly in Excel.  This could include exporting various reports to Excel, or pulling up data directly in Excel.  I'll outline each of the major options that users have and try to highlight the upsides to each of the options.

The first option that most users are already familiar with is exporting SmartList reports to Excel.  Just like the screenshot shown below, you can send specific sets of information with filters already applied to Excel by clicking the Excel button at the top of the SmartList window.  In the screenshot, I am using the Account Summary list with filters to show information from the 2027 Fiscal Year under the 000-1100-00 account.

You can also load in custom views or any of the predefined SQL views that we have gathered together in the document that can be accessed from the article linked below.

https://community.dynamics.com/gp/b/dynamicsgp/archive/2017/06/21/microsoft-dynamics-gp-sql-views-for-smartlist-designer-to-enhance-reporting

In the article, you will also find a link to instructions for loading a custom SQL view into SmartList to be used for creating new reports. 

This is probably the easiest of the options and generally the most utilized for simple sets of transactional and summary types of information.  This type of export is really beneficial for looking at, modifying, and saving quick snapshots of information from almost all of the modules in Dynamics GP.  

The biggest downside to using SmartList for viewing information in Excel are the limitations of SmartList themselves.  You may run into performance issues viewing lots of records, and normally can't use SmartList for the more complex reports from within Dynamics GP, such as the Receivables Management Historical Aging Trial Balance report, Payables Management Historical Aged Trial Balance report, and the Historical Inventory Trial Balance report come to mind as some of the more popular complex reports from within Dynamics GP.

The next option would be refreshable Excel Reports deployed from Dynamics GP.  These reports can be deployed from the Reporting Tools Setup window (Microsoft Dynamics GP | Tools | Setup | System | Reporting Tools Setup) using either a Network Share or SharePoint as the location where they are stored.  Once you have selected a location for the Excel Reports and clicked the Deploy Reports button, you should see the bottom progress bar of the Business Intelligence Deployment Process window progress forward as shown in the screenshot below.  If you select to deploy SSRS reports at the same time (or redeploy them) then you will also see the top bar progress at the same time.

Once the Excel Reports are deployed, you have a lot of the great Excel features at your fingertips in order to modify the reports that are already present, or even create new reports using the custom views mentioned previously or views/tables already present in the system.  Many of the reports will open with the attached data connection to the report, so you will just need to open the XLSX file from the location you deployed the reports to.  In a lot of different systems, the report will open with the security warning shown in the screenshot below, but you can click the Enable Content button to allow the connection to pull in the data from the report.

Once enabled, most of the reports will look like the following screenshot.

There is an incredible amount of customization that can be done within the Excel product using this report type, and to help walk through setting up new connections, I have linked an article on creating a connection to an Azure SQL Database.  The steps are similar for an on premise SQL Database as well, you would use the 'From Database' option instead of the 'From Azure' option when selecting where you are getting your data from.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-excel

Some of the other benefits to the Excel Reports are that they can be pulled into other Office programs, viewed in SharePoint, or online in Office 365.  Creating new reports is often fairly easy once you have set up the connections, and there is always the 'comfortability' factor to consider as well.  I hear from a lot of customers that they are just more comfortable using Excel and this is a great way to leverage GP data without even having to launch GP.

The third option for viewing Dynamics GP reports in Excel is to export them from SQL Server Reporting Services directly to Excel.  If you have deployed SSRS reports for Dynamics GP, this is an option to you already, and you can do so using the Excel option under the Save icon after you have run the report with any filters set on the parameters as shown below.

Once exported to Excel, the report will automatically open and look very much like it did within the SSRS Report Manager site.  This works great for the RM and PM Historical Aged Trial Balance report and the Historical Inventory Trial Balance reports especially, as these are some of the more complex reports that don't have a great option to send to Excel, other than the last option for sending reports to Excel from Dynamics GP.

The last option outlined is saving Dynamics GP Report Writer reports to CSV file type when prompted to select the output of the report.  Users can mark File, and then choose the type of file they would like to save the report to.  CSV files allow users to open the report in Excel, but it does not always look the best when initially opened, as shown below.

Exporting reports this way will definitely work, but there can be some extra formatting involved to get the headers to line up properly for some of the reports.  It is quite plain to start, but it can do the job just as well.

Hopefully one or more of these options peaks your interest to look into them further.  Our community has a lot of information available on each of these options, but feel free to reach out to us here on the GP Support Team if you have any questions on setting these options up!