Chuck's Reporting Corner January 2009

1099 Info using the QODBC Driver?

Well, not quite.

Every year about this time, we get requests to see if we can create a report that lists and - conditionally - collapses the detail for vendor purchases to review both posting accounts, transaction posting behavior, and then group it according to 1099 categories.

The SDK does not give us a table that allows us to retrieve the accounts earmarked for each 1099 category/box, as well as the threshold limits. Now using a report writer like Crystal Reports, we could hard code or create user input filters to achieve such a result, but it is both tedious and messy. So all we can hope for is a better mechanism in a future SDK.

Is there anything we CAN do?

Yes. We CAN create a simple query in Excel that lists the vendors, 1099 eligibility status, Tax ID number, and address pieces. Granted we can run a similar report in QuickBooks, but what some people like about it, is that you can use Excel to highlight, sort or group the information; one client liked the fact that they could use the vendor create date to glean a little more information about the vendor's origin.

Second, we can also create a list of transactions for each vendor showing the posting account, amount, date, reference number etc.

I am going to include both examples in this article. The 1099/Vendor list example is for beginners, or those not familiar with SQL statements; it is all Wizard driven. The second example that lists the vendor transaction amounts, is for more advanced users; secondarily it showcases the new *Union* Statement in the latest version of the QODBC Driver for 2009. (A Union statement allows us to manually aggregate information from several transactions - e.g. make a unified table from two or more other tables)

Example 1 - 1099 Vendor List

Here are the steps (Using Office 2007):

1. Choose the Data Tab, and choose "From Other Sources"; Select "From Microsoft Query"



2. Choose "QuickBooks Data" and make SURE the Query Wizard is checked below:



3. A list of tables will appear; Choose the Vendor Table on the left hand side, and click on the "+" sign to its left so it turns into a minus, and displays the fields for that table:



4. Select the following fields and move each one over to the column on the right:
   

   

   

   then click on the "Next" Button as shown above.

5. Click "Next" again:



6. Click "Next" again:



7. Return the Data to Microsoft Excel as shown below:



8. Determine the starting cell and row you want to the data returned to:





9. You get data returned like this:



10. One of the nice features in Office 2007 is the ease with which you can filter and select data. In this example, we have clicked on the down arrow in the "IsVendorEligibleFor1099" column so we can filter by only those who are 1099 vendors:



11. If we want to show only the 1099 eligible vendors, w e choose "1"



12. Now we see only the 1099 Eligible Vendors, and can spot which one is missing an ID Number

Example 2 - Vendor Transactions

Now bear in mind that this could take a long time on files over 150 Meg... on Enterprise files it might take several hours just to "optimize" the data; however the subsequent requests for data will be much quicker.

Again, using Excel 2007:

1. Choose the Data Tab, and choose "From Other Sources"; Select "From Microsoft Query



2. Choose "QuickBooks Data" and make SURE the Query Wizard is UNchecked below:



3. Click on the "Close" button to dismiss the Add Tables window. We will be using Raw SQL here.



4. Click on the 'View' Menu, and then choose "SQL"



5. In this SQL statement box paste the WHOLE SQL statement AS IS from below:



SELECT "BillExpenseLine"."TxnNumber" AS TxnNumber,
"BillExpenseLine"."TxnDate" AS TxnDate,
"BillExpenseLine"."RefNumber" AS RefNumber,
"BillExpenseLine"."VendorRefFullName" AS VendorName,
"BillExpenseLine"."ExpenseLineAmount" AS Amount,
"Account"."FullName" AS AccountName,
"Vendor"."IsVendorEligibleFor1099" AS Is1099,
"Vendor"."VendorTaxIdent" AS VendorTaxID
FROM ("BillExpenseLine" "BillExpenseLine"
LEFT OUTER JOIN "Account" "Account"
ON "BillExpenseLine"."ExpenseLineAccountRefListID"="Account"."ListID")
LEFT OUTER JOIN "Vendor" "Vendor" ON "BillExpenseLine"."VendorRefListID"="Vendor"."ListID"
UNION
SELECT "CheckExpenseLine"."TxnNumber" AS TxnNumber,
"CheckExpenseLine"."TxnDate" AS TxnDate,
"CheckExpenseLine"."RefNumber" AS RefNumber,
"CheckExpenseLine"."PayeeEntityRefFullName" AS VendorName, "CheckExpenseLine"."ExpenseLineAmount" AS Amount,
"Account"."FullName" AS AccountName,
"Vendor"."IsVendorEligibleFor1099" AS Is1099,
"Vendor"."VendorTaxIdent" AS VendorTaxID
FROM ("CheckExpenseLine" "CheckExpenseLine"
LEFT OUTER JOIN "Account" "Account"
ON "CheckExpenseLine"."ExpenseLineAccountRefListID"="Account"."ListID")
LEFT OUTER JOIN "Vendor" "Vendor"
ON "CheckExpenseLine"."PayeeEntityRefListID"="Vendor"."ListID"

6. Click on the "OK" button when done



7. You will get this warning message; click on "OK"



8. The data is returned into Excel.



9. From within Microsoft Query above, click on File>Return Data to Excel. You will get something along the lines of the following:

In this example I used just Check and Bill, however to cover ALL of your bases you should also include the following tables:

• Bill
• Vendor Credit
• Check
• CreditCardCharge
• CreditCardCredit
• Deposit
• Journal Entry
• SalesTaxPaymentCheck
• PayrollLiabilityCheck

It is not realistic to expect that there are transactions for 1099 vendors in Deposits, Sales Tax Payments, or Payroll Liabilities, BUT you can never assume what a client does - whether intentionally, or unintentionally. This is one of those things you learn from doing thousands of custom projects - QuickBooks Data is NEVER organized, or inputted cleanly.

There is no documentation from FLEXquarters yet on the UNION driver, but they have promised me that they will give me some more information for the accountant geeks, and database geeks to play with. As I have already found out, there are some limitations. That is something for another newsletter.

W-2 and 1099 Time is still ingrained into my yearly clock, even though I don't perform that hands on work anymore; but I STILL remember. So, I wish the best of luck to those who are.

Happy New Year, and Tax Season is upon us again!

0 Comments