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 not great, especially for advance 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.
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.