How to Create Reports with SQL Scripts & SLB

SmartList Builder is one of the most popular add-ons for Dynamics GP. It allows users to create custom SmartLists easily! The out of box SmartLists are good but advanced usage the out of box lists may not have the tables or columns you need. SQL view reporting provides more flexibility with combining tables, but it requires access (and SQL knowledge) to SQL Management Studio.

Since SmartList Builder 2013, SmartList Builder provides a feature of writing SQL scripts to get the data you need directly within GP.

 

Table Security

You must grant Security to SQL server tables and views before using them in SmartList Builder. To do so, go to GP – Tools – SmartList Builder – Security – SQL Table Security – Mark the companies – Mark Tables and Views that you want to grant access to or Mark All – Ok

 

When running SQL scripts against these tables, by default have access to all the available. However, there may be scenarios where you do not want a user to have access to certain tables; table security can be used to exclude user access. To do so, in GP go to Tools – SmartList Builder – Security – Table Security – Mark the tables to excluded from access, for example exclude all the Payroll Tables by selecting Product ‘Microsoft Dynamics GP, series ‘Payroll’ then check all the Payroll tables. If you need few users such as Payroll accountants to be able to see these Payroll tables, you can add them to the corresponding roles in GP (- Administration – User Security window ), then select this role in the Exclusions are not applied to role field as shown below.

 

Here are instructions on how to create SmartList Builder reports using Select SQL scripts.

Microsoft Dynamics GP – Tools – SmartList Builder – New – ‘+’ in the Tables box

The SQL Script option shows as one of the table types.

In the SQL script box, you can type in the select SQL scripts then click on the Preview icon to validate.

If there are some errors in the script, the ‘SQL script is invalid’ window will show up. Keep in mind that line-level comments are not allowed in the SQL script box.

After all the issues are fixed, the Preview window will return some data.

You will also need to select the key fields from the script. Then click Save.

Mark the Default fields then Click Save.

The new report will show in the SmartList under the Series you selected.

And that’s it – tada!

You can also reach out to us at any time by clicking the contact us or chat functions, and we can assist!

Thank you!

-GP365 Team

Help us with your feedback!

This blog is for you – the GP users of today. We are always looking for your feedback and questions to help create relevant content. As GP365 continues to develop, our customer feedback loop must continue to evolve at the same pace.

Did you like this blog and wish to receive future blogs like it? Sign up to our user group by going to www.gp365.ca, scroll to the bottom of the page and enter your email in the Stay in Touch with GP365. We will keep you posted of new blogs, training events and lots more!

You can find and interact with us on Facebook, Twitter, and LinkedIn.

If you want to get in touch with us, you can email us at [email protected] or reach out here!

Thanks for reading, and we look forward to hearing from you soon!

We provide a seamless Dynamics GP hosted experience. If you are looking to host your Microsoft Dynamics Great Plains (GP) system in the cloud, look no further! We are the leader and only provider truly providing GP in a SaaS experience. No version upgrade fees, no headaches, enterprise security, and  a better GP experience. We are The Future of Dynamics GP!