Intuit does not make available any direct payroll information via "tables". The most information we have is the EmployeeEarning, PayrollItemWage and PayrollItemNonWage tables, but there is no information on payroll checks.

The only way we can access the information is via sp_reports. Using the PayrollSummary or EmployeeEarnings would the best way to view the data.

When using the Rock Castle Construction QuickBooks 2006 sample company file: sample_product-based business.qbw, you should get the following results:

Payroll Summary

sp_report PayrollSummary show Label, AmountHours, AmountRate,
Amount parameters DateMacro = 'ThisYearToDate'

Empoyee Earnings

sp_report EmployeeEarningsSummary show Label, Amount
parameters DateMacro = 'ThisYearToDate'

That's a horrible decision on Intuit's part!  I am writing job reports for construction contractors, and the lack of data just about kills my chances of doing so.  I am preferring to work directly with the QB tables nuts and bolts because the queries are much faster than the sp pass throughs.  I did us an sp pass-through query but because the query pulls on ALL history it just about smashes the machine by using up 100% of the resources and takes a very long time to run.  I did manage to get the query to run and will now just add to the data I stored in an MS Access table  incrementally each month.

Now of my questions -

Does anyone know if Intuit plans to add payroll data to the tables they make available?  Seems like they should because payroll is very important to most users.

I tried like heck to optimize the QB data, choosing what seems the fastest which is to optimize only when I click the button or to run from a command prompt, but am receiving the following error - BillItemLine.ItemGroupLineItemGroupRefListID may not be NULL

I have tried to rebuild the data within QB but it does not improve the situation.  Seems that if I could build this table on my hard drive, then queries should be much faster and may not hog up the machine.  Here is the query I am running -

sp_report CustomTxnDetail UNOPTIMIZED show account, accountlistid, item,  name, sourcename, TxnType, Date, RefNumber,  Memo, class, SplitAccount, Debit, Credit parameters datemacro='All', AccountFilterType = 'IncomeAndExpense', ReportDetailLevelFilter = 'AllExceptSummary', SummarizeRowsBy = 'Employee', TxnFilterTypes = 'Paycheck'

Does anyone have any ideas or suggetions?

Intuit has already annouced what's new for QuickBooks 2007. See: QuickBooks SDK 6.0 for U.S. editions of QuickBooks 2007 (and there's nothing about Payroll).

As far as your BillItemLine table is concerned, make sure you are running QODBC v6.00.00.176 (or later) and run this pass-thorugh query:

sp_optimizefullsync BillItemLine

You cannot use the UNOPTIMIZED tag in any sp_report, but what about limiting the report to smaller date ranges?:

sp_report CustomTxnDetail show account, accountlistid, item,  name, sourcename,
TxnType, Date, RefNumber,  Memo, class, SplitAccount, Debit, Credit
parameters AccountFilterType = 'IncomeAndExpense',
ReportDetailLevelFilter = 'AllExceptSummary', SummarizeRowsBy = 'Employee',
TxnFilterTypes = 'Paycheck', DateFrom = {d'2006-07-01'}, DateTo={d'2007-12-31'}

To make this faster still, you need to be using the QuickBooks Enterprise edition.

I ran the query and received this message - "Pass-through query with ReturnRecords property set to True did not return any records."  Seems that the same issue presents with this query.  So it seems I have a record that violates integrity rules.  I checked my driver version and it is not the most current.  How do I update to the current?

sp_optimizefullsync BillItemLine

doesn't return any records, it just does a full resync of the BillItemLine optimized table. See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more.

See also: How can I get the latest version of QODBC? 

Is the optimization that occurs dependent upon my setting on the DSN Optimization tab?  I did manage to optimize the entire database.  When I run the sp_report above, where the setting is for the most optimized, i.e. only when I force updates in whatever manner, am I then only extracting data from the optimized file on the C drive and not from QB?  Seems like that shoud be much faster but it's not.  What am I missing?

Has anyone asked Intuit to include payroll data in tables?

Do we have an official channel for requesting payroll data from Intuit, in tables?  What is the issue and why won't they include that data?  I would like to make an official request, or at least understand why payroll data is not included.

The official place is: QuickBooks SDK Futures

The "QuickBooks SDK Futures" Intuit forum is the place to discuss what you'd like to see added to the QuickBooks SDK.

Have you made sure that you have allowed QODBC to access personal data? Try opening one of the sample company files to see how it should work. What Payroll Service are you subscribed to?