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 SQL Server, without using any GP licenses. This is similar to mechanisms used for Excel Reports. This reporting method can be applied to other reports depending on your business needs.

Feel free to use the scripts and let me know how it works out or if you have any questions. As recommended, start in your Development environments before bringing this tool to Production.

--------------------------------------------------------------------------------------------------------------------------
USE [Your GP Company Database]
GO

/****** Object:  View [dbo].[Vw_1099BOXAMNT]   11:14:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[Vw_1099BOXAMNT] AS

SELECT
B.VENDNAME,
A.VENDORID,
--A.TEN99TYPE,
CASE
WHEN A.TEN99TYPE = '1' THEN '1 Not a 1099 Vendor'
WHEN A.TEN99TYPE = '2' THEN '2 Dividend'
WHEN A.TEN99TYPE = '3' THEN '3 Interest'
WHEN A.TEN99TYPE = '4' THEN '4 Miscellaneous'
WHEN A.TEN99TYPE = '5' THEN '5 Witholding'
ELSE 'No Type in Vw or Withholding'
END AS TEN99TYPE,

--A.TEN99BOXNUMBER,
CASE
WHEN (A.TEN99TYPE = '1' AND A.TEN99BOXNUMBER = '0') THEN '0'
--2 = Dividend
WHEN (A.TEN99TYPE ='2' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '1a') THEN '1a Ordinary Dividends'
WHEN (A.TEN99TYPE ='2' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '1b') THEN '1b Qualified Dividends'
WHEN (A.TEN99TYPE ='2' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '2a') THEN '2a Capital Gain Dist.'
WHEN (A.TEN99TYPE ='2' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '2b') THEN '2b Unrecap. 1250 Gain'
WHEN (A.TEN99TYPE ='2' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '2c') THEN '2c Section 1202 Gain'
WHEN (A.TEN99TYPE ='2' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '2d') THEN '2d 28% Rate Gain'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '3') THEN '3 Nontaxable Dist.'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '4') THEN '4 Federal Tax Withheld'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '5') THEN '5 Investment Expense'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '6') THEN '6 Foreign Tax Paid'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '8') THEN '8 Cash'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '9') THEN '9 Noncash'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '10') THEN '10 Exempt Interest Dividend'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '11') THEN '11 Specified Private Activity'
WHEN (A.TEN99TYPE ='2' AND A.TEN99BOXNUMBER =  '14') THEN '14 State Tax Withheld'
-- 3 = Interest
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '1') THEN '1 Interest'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '2') THEN '2 Early Withdrawal'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '3') THEN '3 U.S. Savings Bonds Int.'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '4') THEN '4 Federal Tax Withheld'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '5') THEN '5 Investments Expense'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '6') THEN '6 Foreign Tax Paid'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '8') THEN '8 Tax-Exempt Interest'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '9') THEN '9 Private Activity Bond Int.'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '10') THEN '10 Market Discount'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '11') THEN '11 Bond Premium'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '12') THEN '12 Bond Premium on Treasuries'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '13') THEN '13 Bond Premium on Tax-exe'
WHEN (A.TEN99TYPE ='3' AND A.TEN99BOXNUMBER =  '17') THEN '17 State Tax Withheld'
--Miscellaneous
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '1') THEN '1 Rents'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '2') THEN '2 Royalties'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '3') THEN '3 Other Income'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '4') THEN '4 Federal Tax Withheld'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '5') THEN '5 Fishing Boat Proceeds'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '6') THEN '6 Medical Payments'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '7') THEN '7 Nonemployee Compensation'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '8') THEN '8 Substitute Payments'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '10') THEN '10 Crop Insurance'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '13') THEN '13 Golden Parachute'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '14') THEN '14 Attorney Proceeds'
WHEN (A.TEN99TYPE ='4' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '15a') THEN '15a Section 409A Deferrals'
WHEN (A.TEN99TYPE ='4' AND CAST (A.TEN99BOXNUMBER AS VARCHAR) =  '15b') THEN '15b Section 409A Income'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '16') THEN '16 State Tax Withheld'
WHEN (A.TEN99TYPE ='4' AND A.TEN99BOXNUMBER =  '18') THEN '18 State Income'
-- otherwise
ELSE 'No box number in Vw or Withholding'
END AS TEN99BOXNUMBER,

-- sum 1099 amount for the year
SUM(A.TEN99AMNT) AS _1099AMNT,
A.YEAR1

FROM PM00204 A
LEFT JOIN PM00200 B
ON A.VENDORID =B.VENDORID
WHERE A.TEN99AMNT > 0
GROUP BY A.VENDORID, A.TEN99BOXNUMBER,A.YEAR1,B.VENDNAME,A.TEN99TYPE
;
GO


--------------------------------------------------------------------------------------------------------------------------
/****** Script for 1099BOX TYPE AND AMOUNT command from SSMS to Excel Report  ******/
SELECT[VENDNAME]
      ,[VENDORID]
      ,[TEN99TYPE]
      ,[TEN99BOXNUMBER]
      ,[_1099AMNT]
      ,[YEAR1]
      ,[PERIODID]
  FROM [UPI].[dbo].[Vw_1099BOXAMNT]




By
Mwalimu Phiri