Create SQL View Report Scripts from Microsoft Dynamics GP to Excel for 1099 box number aggregation
Data Engineering and Automation Series: Excel as SQL Server / Database Client Problem: Microsoft Dynamics GP does not have an out-of-the-box report which SUMS the 1099 amounts based on the type (i.e., Misc, Dividend, Interest) and the box number (i.e., Rents, Tax withheld), so I created one with the scripts below. The key words here are Box Number. An accounting team could take hours upon hours reviewing box numbers and amounts for their vendors, because in the application, they have to click into each vendors 1099 detail window. This review process for accountants could take a while especially for instances with multiple companies that file their own 1099's. Solution: I used the first script to create a SQL view and then the second script used Microsoft Excel's ODBC to for a friendly interface on the user's local computer. the user can slice and dice the data in their Excel file and they can refresh the data anytime to obtain a live feed of data hosted on
