Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

SmartList Designer - Permissions for Publish

  • 1.  SmartList Designer - Permissions for Publish

    GPUG ALL STAR
    Posted 03-12-2018 03:53 PM
    Edited by Amber Bell 03-12-2018 03:54 PM
    Hello Everyone,

    I am working on some final edits for #GPUGAmplify! I am documenting how to publish SmartList Designer reports. I cannot find information about the security needed for Publish to be enabled. (I mean the Publish button that creates the Excel Report).

    **Note: I am not talking about SmartList Builder. I know the rule for that product. I am talking about the GP built in SmartList Designer product**

    I am running GP in test so it's tough to figure out if I updated my SQL security years ago. It worked for me when logged in as 'sa' and as a generic user with the AP CLERK security role. I have a feeling it is a SQL setting...I just can't find the info to make sure I am correct when I document this for my GP Tip.

    Any help is appreciated. I will link to my tip on this question when it is done.
    ​​

    ------------------------------
    Amber Bell
    Training Dynamo LLC
    Email: amber@trainingdynamo.com
    Website: www.trainingdynamo.com
    Twitter: @trainingdynamo
    ------------------------------


  • 2.  RE: SmartList Designer - Permissions for Publish

    TOP CONTRIBUTOR
    Posted 03-12-2018 04:02 PM
    ​Hi @Amber Bell

    I would assume the ability to Publish might have to do with the AD Securities... like can you write to a certain file location, maybe a combination of each.

    Can the AP CLERK publish a payroll report?

    Anyway, look at the file location permissions.  You may have to reduce permissions on shared locations to limit user's abilities to publish.



    ------------------------------
    Regards,

    Jo deRuiter
    "That GP Red Head"
    Senior Financial Systems Consultant
    Heartland Business Systems, LLC
    Milwaukee, WI
    770-906-4504 (Cell)
    ------------------------------



  • 3.  RE: SmartList Designer - Permissions for Publish

    TOP CONTRIBUTOR
    Posted 03-12-2018 06:20 PM
    Edited by Kristen Hosman 03-12-2018 06:32 PM
    Hi @Amber Bell

    I'm glad you asked this.  I'm actually covering this in my Amplify session.  Hope this helps.  If you haven't already deployed the reports then you can click deploy report in the bottom right once you have your reports dictionary path in place.  Once you have this setup log out and back into GP and you will then see the Publish button is activated in SmartList.




    ------------------------------
    Kristen Hosman
    Dynamics ERP Consultant
    Briware Solutions Inc.
    Pine CO
    ------------------------------



  • 4.  RE: SmartList Designer - Permissions for Publish

    GPUG ALL STAR
    Posted 03-13-2018 09:57 AM
      |   view attached
    Hi @Kristen Hosman!

    I have Excel Reports deployed. My question is about who has the security to publish. Here is the rule for SmartList Builder (from the eOne manual for Excel Report Builder):

    To publish a report in Excel Report Builder, the user that is publishing will need special permissions in SQL.  If the View Name field is enabled on the Excel Report Builder setup, when the setup is published, it is going to create a view in the SQL database(s).  The GP user that is doing the publishing will need to be in the SQL Sysadmin server role or be setup as db_owner in the database roles for each database the report is going to interact with.  Without these permissions, the GP user will not be able to publish the report.  By default, the only GP user that would have enough permission in SQL to publish is 'sa'.

    My question is: Is this the same for SmartList Designer? I know SmartList Designer is different for some security features (i.e. you can see SQL views in SmartList Designer without doing the extra step of manually giving security. In SmartList Builder you have to go to: Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security. This is not required for SmartList Designer.)

    I am attaching my tip. I just wanted to clarify the security info for publishing on page 4 (at the top).


    ------------------------------
    Amber Bell
    Training Dynamo LLC
    Email: amber@trainingdynamo.com
    Website: www.trainingdynamo.com
    Twitter: @trainingdynamo
    ------------------------------



  • 5.  RE: SmartList Designer - Permissions for Publish

    TOP CONTRIBUTOR
    Posted 03-13-2018 10:24 AM
    Hi @Amber Bell

    I followed your instructions that you included and I created a new GP user and assigned to the AP Clerk role.  I then was able to use that user to create New SmartLists (used a view from Victoria's website) and it didn't show as you suggested on page 7 of your document you have to grant access.  Once I created the task and added it to the AP Clerk role I was able to Publish.  Didn't need to do anything in SQL to the users security.





    ------------------------------
    Kristen Hosman
    Dynamics ERP Consultant
    Briware Solutions Inc.
    Pine CO
    ------------------------------



  • 6.  RE: SmartList Designer - Permissions for Publish

    GPUG ALL STAR
    Posted 03-13-2018 04:38 PM

    I found the definitive answer!!!

     

    1.     You can only create\publish an Excel report from a SmartList Designer query that has been built using a SQL view.  If you've used SQL tables in your SmartList Designer query you will not be able to publish it to an Excel report.  This is the first version of this new feature so we expect it to be expanded beyond SQL views in a future update.  You can use existing GP SQL Views or build your own.

    2.     The user who is attempting to publish must have Read\Write\Modify permissions to the share location where the Excel reports are deployed to.

    3.     The default Excel reports must be deployed already.

    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

    That is why users don't need extra security to Publish. Unlike SmartList Builder (Which creates a new SQL View), SmartList Designer requires an existing view. I stumbled into the answer because I created a new SmartList report based on a table and I could not publish!! So I searched the internet and found this answer!!



    ------------------------------
    Amber Bell
    Training Dynamo LLC
    Email: amber@trainingdynamo.com
    Website: www.trainingdynamo.com
    Twitter: @trainingdynamo
    ------------------------------