Don't Forget- Standard GP SQL Views

From time to time, we still get inquires from folks building views in SQL server that actually already exist.  So I thought I would post a quick reminder that every SmartList has a SQL view that corresponds and can be used for your own purposes as well (e.g., SQL reports, Excel queries, SmartList Builder, etc).  And, remember, you can link views together as well as to other tables when creating reports.  Just don't modify the standard views (if you need to add to them, just create a new once with the same design and then modify).  Here are some of the most common ones available (this is NOT all of them) on any GP database...

• AATransactions
• Accounts
• AccountSummary
• AccountTransactions
• BankTransactions
• AttendanceDetail
• BillofMaterials
• CertificateList
• Customers
• EmployeeBenefit
• Employees
• EmployeeSummary
• FixedAssets
• FixedAssetsBooks
• FixedAssetsPurchase
• InventoryPurchaseReceipts
• ItemQuantities
• PayrollTransactions
• PayablesTransactions
• PurchaseLineItems
• SalesLineItems
• Vendors

When I teach beginner reporting classes, I advise students to always "look twice" for a standard view before embarking on creating new views or combining open/history/work tables in a SQL statement (as often the views already do this for you).  Good luck and happy reporting!


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.