Frequently Asked Questions


What is Custom Reporting?

Custom Reporting provides direct read-only access to your QuickBooks database. You can create your own custom reports in ODBC compliant applications such as Microsoft Access & Excel.

A few things to know before you get started:

  • You will be accessing your data as it is stored in QuickBooks, some familiarity with relational databases is required.
  • Your company file must be open in multi-user mode in order to connect to it from a 3rd party application.

Top

How are ODBC users different from QuickBooks users?

There are several differences between ODBC and QuickBooks users:

  • ODBC user credentials can only be used for custom reporting purposes when connecting directly to the QuickBooks database. QuickBooks user credentials can not be used to connect to the database. This user is needed even if you have Admin rights to QuickBooks.
  • ODBC user credentials are not valid when logging into a QuickBooks company file therefore, an ODBC user that attempts to run QuickBooks will not be recognized.
  • ODBC users do not have the same granular permissions as QuickBooks users and login information should only be shared with trusted power users that already have broad access to QuickBooks.

    Top

How do I connect using Microsoft Excel?

A connection is only possible if you have created an ODBC user in QuickBooks. After you have created an ODBC user, open Excel and use the following instructions to connect:

  • Click on the Data menu
  • Select From Other Sources
  • Select From Microsoft Query
  • Click Browse to locate the file datasource which is located in the same folder as your company file. This file has a 'DSN' extension and has the same name as your company file.
  • Once you select the file, you will be prompted to authenticate. Use the credentials of the ODBC user that you created to connect to the database. From this point on you may start selecting views and pull your data into Excel.

Top

How do I connect using Microsoft Access?

A connection is only possible if you have created an ODBC user in QuickBooks. After you have created an ODBC user, open Microsoft Access then open a blank database and use the following instructions to connect:

  • Click on the External Data menu
  • Click on the More menu
  • Select ODBC Database
  • Select the "Import the source data into a new table in the current database" option
  • Press the OK button and locate the file data source that is needed to make the connection
  • Microsoft Access will look in a default folder for a file datasource. You will need to change this location by clicking on the folder icon in the "Look in:" area. The QuickBooks file datasource is located in the same folder as your company file. It has the same name as your company file with a 'DSN' extension. Navigate to the location of your company file and select the DSN file.
  • Once you select the file, you will be prompted to authenticate. Use the credentials of the ODBC user that you created to connect to the database. As soon as you authenticate, you will see a list of database tables. Some of these are system tables that you do not have access to. Scroll down to locate tables that begin with QBReportAdminGroup to see the dataset that is available for custom reporting. You may select any of the views that begin with QBReportAdminGroup and start creating reports.

Top

How do I connect using Crystal Reports?

A connection is only possible if you have created an ODBC user in QuickBooks. After you have created an ODBC user, open Crystal Reports start the report creation process then and use the following instructions to connect:

  • Open the Database Expert
  • Click on Create New Connection
  • Select ODBC (RDO)
  • Select the radio button next to Find File DSN
  • Select the button next to the File DSN field to locate the file datasource. The QuickBooks file datasource is located in the same folder as your company file. It has the same name as your company file with a 'DSN' extension. Navigate to the location of your company file and select the DSN file.
  • Select Next and use the credentials of the ODBC user that you created to connect to the database. As soon as you authenticate, you will see a list of database tables. Some of these are system tables that you do not have access to. Scroll down to locate tables that begin with QBReportAdminGroup to see the dataset that is available for custom reporting. You may select any of the views that begin with QBReportAdminGroup and start creating reports.

Top

How do I connect using other applications?

Instructions vary by application. Locate the documentation for the application you wish to use and follow the application instructions to connect using a File Data Source. The file data source for Custom Reporting is located in the same folder as your company file. It has the same name as your company file with the 'DSN' extension. Please note that a connection is only possible if you have created an ODBC user.

How do I make sense of the views and their contents?

All view names correspond to how the information is structured in QuickBooks i.e list vs. transactions. All list tables will have 'lst' in the view name, All transaction tables will have 'txn' in the name. Transaction data is exposed via two views (header vs. line items). E.g.:

  • V_lst_item: is a view for the Item List
  • V_txn_invoice_hdr: is a view for Invoice header information
  • V_txn_invoice_line: is a view for Invoice line item information

Top

Why is the Custom Reporting Guide link not working?

The Custom Reporting guide is not available during beta testing. Please use the Beta Support Process to get additional help.

Top

Why is the Online Community link not working?

We do not have a forum available during beta testing. We will provide access to a Custom Reporting Resource Center in the next round of testing.

Top

Do you have any examples to help me get started?

There are some examples available to help you get started though we would like you to try to create reports specific to your business so you can give us feedback. See below for some sample queries. You will need to open your SQL query interface in your ODBC compliant application to run these queries.

    All Invoiced items

    SELECT u.name, l.transaction_id, int_to_txn_type(l.transaction_view_type) trans_type,
    s.city, s.state, s.postal_code,
    l.target_id, l.account_id, l.transaction_date, l.customer_id, l.item_id, l.amount_amt, l.unit_price_amt
    FROM v_txn_invoice_line l, v_lst_item u, v_lst_ship_to s
    WHERE l.item_id = u.id
    AND s.id = l.ship_to_id
    AND l.is_sale = 1
    AND u.item_type IN (0, 1, 2, 3, 5, 8)
    AND ISNULL(l.item_id, 0, l.item_id) != '0'
    AND l.transaction_date >= '2001/01/01'
    AND l.transaction_date <= '2010/01/21'
    ORDER BY 1, 2

    Sales (on Invoices) by Ship To Address

    SELECT C.name,
    SH.street_line1, SH.street_line2, SH.city, SH.state,
    HL.doc_num_h, HL.due_date_h, HL.account_id, HL.amount_amt, HL.quantity_qnty
    FROM QBReportAdminGroup.v_txn_invoice_line HL
    JOIN QBReportAdminGroup.v_lst_ship_to SH ON SH.id = HL.ship_to_id
    JOIN QBReportAdminGroup.v_lst_customer C ON HL.customer_id_h = C.id
    WHERE HL.target_id_h = HL.target_id

Top