We've seen a couple of cases asking about the Publish button in the SmartList window of Microsoft Dynamics GP and decided to blog about it again to bring it to the forefront of everyone's minds.
The main question we get asked is why the Publish button in the SmartList window is disabled/grayed out, when attempting to publish a custom SmartList into a new, refreshable Excel report.
First, there are two requirements that must be in place in order for the Publish button to be enabled in order to publish a report:
A. In the SmartList window, we're selecting a custom SmartList report that was created in the SmartList Designer using a SQL View. This does not include a modification of an existing SmartList report done through SmartList Designer, a custom SmartList report created in the SmartList Designer using a GP table(s) to pull information from, nor a SmartList Favorite.
B. The Excel Reports for Microsoft Dynamics GP must be deployed for the specific company database that you're logged into attempting to publish a report to an Excel Report, and the Reporting Tools Setup window (Administration > Setup > System > Reporting Tools Setup) must have a valid path to those Excel Reports, as that is where any new Excel Reports will be published to.
An example of this, is to do the following steps:
1. Login to Dynamics GP as 'sa' and verify the Excel Reports have been deployed for the company database you're logged into. You can use the Reporting Tools Setup window to verify this. If they are not, deploy them. If you're not sure, re-deploy them. 2. Next, open the SmartList window and immediately click on 'New' to open SmartList Designer. Do not click on anything before clicking the 'New button, otherwise SmartList Designer will open with information for whatever SmartList clicked on, and we don't want that. 3. Name this new SmartList something like 'Test' and you can leave the Product and Series default. 4. In SmartList Designer, under 'Database View', browse down to the bottom and expand 'Views' and then expand 'Company'. 5. Select any view such as 'Customers' which will populate the 'Selected Fields' section of the Designer window with the columns from the selected view. 6. Click OK to create this custom SmartList, which will show in the SmartList window under the Series specified in the Designer window when it was created, such as 'Sales' for the Customer view. 7. Click on this new custom SmartList and the Publish button should be available now. If you click to publish this custom SmartList report, it'll create an Excel Report at the location you've deployed the Excel Reports to, under the 'Reports' folder, under the company and series directory for that report.
If you are using the SmartList Designer View Approval workflow in Microsoft Dynamics GP, once the new SmartList report has been given a 'Completed' status for Workflow, you can then select it in the SmartList window and the Publish button will again be available to publish the new report as an Excel Report. This is true because when we complete a new SmartList report through Workflow, we're actually creating a new, custom SQL View in that company database, which again is one of the requirements for the Publish button to be available to us.
**NOTE: When completing a SmartList Designer report via Workflow, you must be logged on as a Windows user account that has permissions to create a new SQL View in the company database for Microsoft Dynamics GP, as being assigned the PowerUser security role in the GP application does not give these permissions. By default, only the 'sa' and 'DYNSA' logins would have those SQL permissions to create new SQL Views.
We've also seen cases where, when using eOne's SmartList Builder, this will open instead of SmartList Designer and the Publish button doesn't work the same with SmartList Builder as it does with SmartList Designer. In the case where eOne's SmartList Builder is installed with Microsoft Dynamics GP, most customers have found that using Excel Report Builder, also from eOne, to publish SmartLists into Excel Reports, is an option they can use.
Additional information can be found on these two blogs as well:
>>Microsoft Dynamics GP 2015 R2 – SmartList Designer View Workflowhttps://community.dynamics.com/gp/b/dynamicsgp/archive/2015/06/25/microsoft-dynamics-gp-2015-r2-smartlist-designer-view-workflow
>>GP 2015 New Feature – Creating refreshable Excel reports based on SmartList Designer queries.https://community.dynamics.com/gp/b/dynamicsgp/archive/2014/12/18/draft-gp-2015-new-feature-creating-refreshable-excel-reports-based-on-smartlist-designer-queries
Hopefully this helps answer any questions anyone has about the Publish button in the SmartList window and why it may not be enabled for use when attempting to publish a report into a Excel Report in Microsoft Dynamics GP. Thank you!!