QUESTION:
How do you create a Smartlist Deisgner report using a SQL View in Microsoft Dynamics GP?
ANSWER:
Note: The steps below assume that you have already created the view*.
1. Run grant.sql against the Company database and the Dynamics database before attempting the steps below.
Note: The default location for this SQL script would be similar to 'C:\Program Files\Microsoft Dynamics\GP\SQL\Util\grant.sql'.
2. Open SmartList (Click on Smartlist under Microsoft Dynamics GP)
3. Click New to open Smartlist Designer
4. Enter the List Name you want to name it.
5. For Product, select Microsoft Dynamics GP at the top of the pick list.
6. For Series, select the module the list will be for.
7. In the left margin for Database View, scroll to the bottom and expand Views, then expand Company and the view list will load.
8. Mark the check box next to the SQL View you wish to use. (You can also expand the View and select specific columns within that view too if you wish. All are marked by default.)
Note You can do formatting such as adding decimal places or dollar signs to amount fields by creating a calculated field. Click on the Fx button in the header for the Selected Fields section to open the Calculated Fields window. Click Add. Name the calculated field. Select the type of Currency, Date, Integer, Long Integer or string. Click on the field under Table Fields, and use the Functions and Constants to build your expression at the bottom of the window. (For decimals, you may select a type of currency, and use the Function for Arithmetic and multiply (*) the field by .00001 or how many you need). Save. Click OK on the message and OK at the top of the Calculated fields window to insert the field to your report.
9. Click Excecute Query to see the results of your report.
10. Click OK to save your report. (It may take some time to process and create it.)
11. Run grant.sql again against the Company database and Dynamics database. (otherwise only the 'sa' user will have access to it.)
---------------------------------------------------------
*ADDITIONAL:
Or you could also add a tag to your view so it will work for other users. Example:
go
grant select on [YOURVIEWNAME] TO DYNGRP