Use GP SmartList SQL Views to speed up reporting

Overview

Dynamics GP’s out-of-the-box SmartLists run on underlying SQL views of the same name.  Jet users can build reports in the Jet Excel add-in against any SmartList SQL view.  These views simplify report building by linking multiple tables together and using intuitive field names.

insightsoftware has also built custom SQL views that are optimized for use with the Jet add-in. Use Table Builder to build a table in the style of SmartList refreshable reports, or build the custom Jet report that best fits your needs.

Because SQL views do not have primary keys, detail lists written against SQL views require a Field Cache. See the SQL Views article to learn more.

Finding the View associated with your SmartLists

SmartList is a reporting tool that ships standard with GP.  When you open SmartList from within GP, you will see a list of standard SmartList objects.  Most SmartList objects ship with a corresponding SQL view.  Thus the Account Summary object corresponds to the AccountSummary view, and the Receivings Line Items object corresponds to the ReceivingsLineItems view.  Jet treats these views like tables (with some exceptions) and they appear in the tables or tables and views section of Jet Wizards such as TableBuilder or the Jet Function Wizard.

1. Finding the View associated with your SmarLists :

   

   1. Each folder is a SmartList object.  Most have a corresponding SQL view of the same name.
   2. This is the customized SmartList.
   3. click Columns on the ribbon.  All the columns being used will display.
   4. click the Search button on the ribbon to display filters and criteria.
   5. The Search window will display filters and criteria.

2. Using your SmartLists within the Jet Excel Add-in

   You can report on any of these SmartList views—and any custom views you or your GP consultant have created—within Jet Basics and Jet Reports.  In Jet Basics and Jet Reports, you can do this using the Table Builder, which allows you to link tables or views together to create refreshable Excel tables.  You can then use these tables as the data source to create Pivot Tables and even Dashboards using Excel functionality.

   If you have Jet Reports, you can use its full functionality to report out of any SQL view.