This area is for you to talk about, ask questions, and learn more about the Custom Reporting for QuickBooks Enterprise Solutions.
What is this feature? QuickBooks Enterprise Solutions 11.0 allows you to create custom reports with ODBC-compliant applications using a new direct connection to the QuickBooks database for maximum flexibility in report design.
Please limit your posts, questions, comments to this topic.
Not sure what you are asking, but the US version of Enterprise 11 (and Pro/Premier 2011) haven't made any significant changes to how taxes are handled.
i'am referring to TAX settings like UK, we are based in the Philippines, Enterprise edition use US currency so we cannot even use it nor modify it just to use the sale tax option. maybe intuit could provide philippines version or alikes..
If you want an overview of this feature see my blog article on the new reporting feature.
Can anyone tell me if transaction detail is available for Sales Orders and Purchase Orders with the new Custom Reporting ODBC. Also, can the ODBC be used to access Quickbooks data with a machine without Quickbooks installed. Thanks.
I am trying to use custom reporting and have encountered the following errors after connecting to the QB database from both Excel 2003 & 2010: Date truncated and Can not read this file. QB Support does not have a solution for these errors. Has anyone else had this error and do you have a solution? Thanks!
The only time I've seen this is when I have over 65,536 rows of data. Excel's limit has been reached. You might try pulling specific information, like a date range, to get it to fit in Excel.
I have received the same error. Did you figure out how to get passed it?
Same error though mine says Data truncated. I hit OK to that message and then get a Microsoft Query message that says Couldn't read this file. It happens at the point of connecting to SQL Anywhere. As soon as I put in my User ID and Password and hit OK, this comes up. I have a Win 7, 64 bit operating system, QB Enterprise 12.0, Office 2010 32 bit.
I just noticed that this post is for Enterprise 11. I found that Release 7 or 8 had this note for Enterprise 11: " QuickBooks should no longer display the message "Data Truncated" when using the ODBC reporting feature." So Intuit is aware of this problem and does appear to have fixed it for Enterprise 11. Now I just need for them to fix it for Enterprise 12.
Is there a table schema available?
I need to match $ received to the invoice that was paid. The v_txn_received_payment_hdr table and the v_txn_received_payment_line table do not have a field that "ties to" the v_txn_invoice_hdr table.
I have examined the v_txn_link table but could discover no connection. Can you assist?
Not sure if this is still posing a problem for you, but below is how we addressed this using MS Access:
SELECT QBReportAdminGroup_v_lst_account.name AS account, QBReportAdminGroup_v_lst_customer.name AS client, QBReportAdminGroup_v_lst_class.name AS class, QBReportAdminGroup_v_txn_received_payment_line.transaction_date AS [Payment Date], QBReportAdminGroup_v_lst_account.type, QBReportAdminGroup_v_lst_item.name AS item, QBReportAdminGroup_v_txn_received_payment_line.doc_num_h AS [Invoice Number], QBReportAdminGroup_v_txn_invoice_line.transaction_date AS [Invoice Date], Sum(QBReportAdminGroup_v_txn_link.amount_amt) AS [Payment Amount]
FROM (QBReportAdminGroup_v_lst_class RIGHT JOIN ((QBReportAdminGroup_v_lst_customer INNER JOIN (QBReportAdminGroup_v_txn_received_payment_line INNER JOIN (QBReportAdminGroup_v_txn_invoice_line INNER JOIN QBReportAdminGroup_v_txn_link ON QBReportAdminGroup_v_txn_invoice_line.target_id = QBReportAdminGroup_v_txn_link.target_1_id) ON QBReportAdminGroup_v_txn_received_payment_line.target_id = QBReportAdminGroup_v_txn_link.target_2_id) ON QBReportAdminGroup_v_lst_customer.id = QBReportAdminGroup_v_txn_invoice_line.customer_id) INNER JOIN QBReportAdminGroup_v_lst_account ON QBReportAdminGroup_v_txn_invoice_line.account_id = QBReportAdminGroup_v_lst_account.id) ON QBReportAdminGroup_v_lst_class.id = QBReportAdminGroup_v_txn_invoice_line.class_id) LEFT JOIN QBReportAdminGroup_v_lst_item ON QBReportAdminGroup_v_txn_invoice_line.item_id = QBReportAdminGroup_v_lst_item.id
WHERE (((QBReportAdminGroup_v_txn_link.link_type)=4))
GROUP BY QBReportAdminGroup_v_lst_account.name, QBReportAdminGroup_v_lst_customer.name, QBReportAdminGroup_v_lst_class.name, QBReportAdminGroup_v_txn_received_payment_line.transaction_date, QBReportAdminGroup_v_lst_account.type, QBReportAdminGroup_v_lst_item.name, QBReportAdminGroup_v_txn_received_payment_line.doc_num_h, QBReportAdminGroup_v_txn_invoice_line.transaction_date
HAVING (((QBReportAdminGroup_v_txn_received_payment_line.transaction_date)>#1/1/2012#) AND ((QBReportAdminGroup_v_lst_account.type)=10));
Can you please post more details on this new ODBC Driver that is part of Enterprise 11.0? How can we learn how to use it to do customized reports? I cannot find any updated information on this new driver, how it works and how to do custom reports. Thanks.
I have successfully connected to the datbase wtih Access. Can you also connect using SQL Server Management Studio to connect and is so what are the steps.
Thanks
I wouldn't expect that you could, although I haven't tried. Since it is read-only and only in Enterprise, it isn't of much use to me.
However, SQL Server Management Studio is set up to manage Microsoft SQL Server. That isn't the database here, this is a Sybase product. And the custom report feature is accessible via ODBC, not directly connecting you to the database.
I don't think SQL Server MS is set up to manage ODBC, but I am not positive about that.
Our DBAs did this using the DSN. Check the .dsn file and get server name, use that to connect SQL and QB database.
I have been using AccessBooks RealTime from synergration.com to sync the data with a SQL Server 2008 database. I now have the ability to write reports using SQL Report Server. The benefit is I now can give access from any workstation or webpage based on the users authentication to SQL Server.
