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:
- 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 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:
Steve has over 15 years of experience in business systems consulting, and 20 years of experience in management accounting and finance with small and mid-sized businesses in the construction, distribution, service, and technology industries.