Skip to main content
Dynamics 365 Community / Blogs / Dynamics GP Support and Services Blog / Microsoft Dynamics GP SQL V...

Microsoft Dynamics GP SQL Views for SmartList Designer to Enhance Reporting

Isaac Olson Profile Picture Isaac Olson

Hello,

The Microsoft Dynamics GP Support team has created a living document where users can get sample SQL Views for various modules in GP. This includes a wide variety of modules such as Bank Rec and Manufacturing.  These SQL views can then be brought into SmartList Designer with the ‘sa’ user and can be used within GP by other users using KB 4014658 and can also be exported to Excel . 

KB 4014658 - How to create a SmartList Designer report using a SQL View

Below is a list highlighting a few of our SQL views available:

Bank Rec

Undeposited Receipts

Canadian Payroll

Employee Year To Date Units and Dollars

Fixed Assets

Interface to GL

Retired Assets

General Ledger

Account Summary

Human Resources and Payroll 

ACA Employee and Dependent Information

Beneficiary Dependents

Earning History of when employee received pay increase information

Time Accruals and  Time Available for Attendance

Deduction In Arrears (multiple views)

Employee Summary,  Employee Pay Rates

Hours Worked Per Year

Payroll Year End Wage (W2)

W2 Duplicate Box and Labels for Pay Codes, Benefits, and Deductions

Manufacturing

MO Receipt Status

Non-Closed MO Variances

Project Accounting

PTE Timesheets Pending Approval

Purchase Order Processing

PO Pending Approval Master List

Requisitions Ready for Purchase

System

Security Cheat Sheet

Workflow

List Final Approvers of Documents

Pending Approval List

GL Journal Entry Final Approvers for Workflow

Additional:

Visit this article for a '1099 view' for Payables Management.

---------------------------------------------------

For the entire list please download the following document. (This is a living document that we will continue to update in the future.)

Click Here to download the GP SQL View Living Doc.

There are also many publicly posted SQL views for Microsoft Dynamics GP besides our list above, and you can find almost anything you need or at least a starting point by just searching “GP SQL View” on Bing or any search engine. Then use the KB above to bring it into SmartList Designer.

CLICK HERE TO START YOUR SEARCH!

Note: Issues with non-power user's receiving access errors when attempting to modify SmartLists based on SQL Views have been addressed in GP 2018. 

Helpful Tips:

  • As mentioned in the KB, always run Grant.sql after creating your SQL view.
  • Make sure that your columns are in the correct order and results are already narrowed down in your SQL view before you bring it into Microsoft Dynamics GP.  Your everyday users can view the results and search/filter accordingly, but only ‘sa’ can modify these SmartLists based on SQL views.
  • Be smart with how many columns you use in your SQL view. Include only what you need. The more columns and the more results you have the longer it will take to load the list.
  • By default Microsoft Dynamics GP will display 1000 results at a time which is recommended.

Comments

*This post is locked for comments

  • RBDELAMATER Profile Picture RBDELAMATER
    Posted at
    As a part of the SQL creation I add these lines at the end of the SQL View CREATE statement...At the end of the CREATE statement there is 'GO' After this 'GO' line add these lines. Since this SQL view is only for viewing you should only allow 'SELECT' command used by DYNGRP - [Create Statement]; GO GRANT SELECT ON [dbo].[view] TO [DYNGRP] AS [dbo]; GO
  • Beat Bucher GP Geek GPUG All Star Profile Picture Beat Bucher GP Gee... 28,002 Super User
    Posted at
    Hi Corey, The link for the Word document is still valid and points to some MBS (Customer Source) library share.. It was last revised (v3) back in March 2020 and looks still valid.