Microsoft Dynamics Blog

Welcome to Armanino’s Microsoft Dynamics Blog, where you’ll find information on topics across Microsoft Dynamics 365 ERP and CRM, Dynamics GP, Power BI, PowerApps, Flow and more. Use these tips, tricks and insights to get the most out of your applications! Want these articles delivered straight to your inbox? Subscribe to our newsletter below.

Thursday, January 22, 2015

Refreshable Excel Reports from Smartlist Designer in GP 2015

Posted by Steve Chapman

Dynamics GP 2015 includes the option to publish SmartList Designer reports to Refreshable Excel Reports. This is a big advantage because you can use the data connection created by the Refreshable Excel Report to create reusable reports.

To be publishable, the SmartList Designer reports must be based on a SQL View. SQL Views are readily available in your Dynamics GP databases, you can download them from such excellent sites as: victoriayudin.com, or you can create them yourself.
ProTip: Go to Victoria’s site first to see if she’s already created something you can use.

Additionally, some changes you make to the SmartList Designer and save as a favorite will be published to Refreshable Excel Reports as well. For example, if I have SmartList Designer report created from a view and remove a column, that column won’t be published. I made some changes using the “Search” tool and those changes were not published.

Here’s specific description from Microsoft on this new feature:

Deployment

  • Excel reports can be deployed to a network share or to SharePoint (online or on-premise) from Administration> Setup> System> Reporting Tools Setup.
  • A Publish button is added to the top ribbon in SmartList. The button will be available if the SmartList is created from a SQL view.
  • When you publish an Excel report from SmartList Designer:  the Excel report will be deployed to the location you have deployed Excel Reports and to the folder that corresponds to the series selected for the SmartList on the SmartList Designer window.

Users

  • Users will need to have proper security to the network share or SharePoint location in order to successfully publish new refreshable excel reports.
  • The Excel reports will attempt to access SQL data using the Windows credentials that you are currently logged in with.  Generally you will need to add the windows accounts in SQL and assign them the RPT_ roles based on the reports and data you want them to have access to.
  • If you create new Views you will need to grant access to these views to the users for them to be able to refresh the Excel report data.

Here’s a pane from SQL Server Management Studio that shows some of existing views in the Dynamics GP databases:

Dynamics GP 2015 Refreshable Excel Reports
Check out this video showing this functionality in action: SmartList Designer to Refreshable Excel Reports in Dynamics GP 2015.

COMMENTS

comments powered by Disqus
« | »