Dynamics GP is a trusted ERP solution that offers users multiple ways to combine their financial data into helpful lists that can be used for data analysis. Smartlist Designer is one of those tools that enables users to create custom data reports that can be analyzed and used for decision-making. To enhance reporting capabilities, users can add custom SQL views to their SmartLists. This allows users to easily combine multiple data tables and sources to create views that can be intuitively pulled into a SmartList, as opposed to combining the tables and setting up filters within SmartList Designer.
Creating the SQL View
Users will first need to create a new view in SQL Server Management Studio. Users can combine two or more data tables and create the relationship between the tables. For example, the Payables Transactions data table does not contain the full remit to address details. We can combine the Payables Transactions data with the Vendor Address data to give us the remit to addresses on payables transactions and select the fields to include in the view.
Once the view is created, the data can be filtered, and columns can be rearranged to group the data in the way you want it to appear in the SmartList. In our example, we can filter the results of the view to include only unpaid Payables invoices by pulling all Documents with Document Type = 1 and Current Transaction Amount > 0.00.
Note that Dynamics GP users need to be granted access to the SQL view to be able to use it within SmartList Designer.
Creating the SmartList
Create a new SmartList in Dynamics GP to open SmartList Designer. Users will then find and select their custom SQL view in the Database View pane. When you select the checkbox, Dynamics GP will automatically pull all the fields into the SmartList, but you can remove any as needed.
Once the SQL view is added, users can Execute the results of the query, and the resulting SmartList will be created. Users will now see that data is available with the fields and filters established in the SQL view. Note that users will need to have access to this SmartList to be able to use it for data analysis purposes.
Creating and pulling custom SQL views into SmartLists in Dynamics GP offers users an easy way to create robust data reports that combine multiple data sources and tables. These reports can then be pulled into Excel, where the data can be grouped into pivot tables and displayed in ways that help organizations make financial decisions. Dynamics GP has numerous functions that enable users to aggregate and analyze their financial data. To learn how to make full use of your financial system, contact a Rand Group representative today. We are an experienced Dynamics GP partner that has almost 20 years of experience helping our clients improve their operations through technology.

The post How to Pull SQL Views into a SmartList in Dynamics GP appeared first on Rand Group.