Transcription
QuickBooksCustom ReportingODBCImplementation Guide
QuickBooks Custom Reporting-ODBC Implementation GuideTable of ContentsChapter 1: Introduction . Page 2Chapter 2: Understanding the QuickBooks Database. Page 4Chapter 3: Create an ODBC user in QuickBooks . Page 14Chapter 4: Create ODBC Connections/Reports . PageMicrosoft Access . PageMicrosoft Excel . PageSAP Crystal Reports . Page16173140Chapter 5: Custom Report Examples . Page 51Unpaid Bills by Class . Page 52Names Lists with Contact Information by List Type . Page 561 Page
QuickBooks Custom Reporting-ODBC Implementation GuideChapter 1: IntroductionWelcome to the QuickBooks custom reporting-ODBC (Open Database Connectivity) implementationguide. This guide shows you how to get started with QuickBooks custom reporting capabilities.Why ODBC Custom Reports?QuickBooks has over 100 built-in reports, so why is custom reporting with ODBC even necessary?Companies big and small use QuickBooks to run their business. The built-in reports satisfy the needs ofmost QuickBooks users. However, what if: You need to tweak a report to make it perfect, but the report modifications in QuickBooks justaren't doing it.You have exported reports to Excel, but found the repetitive nature of reporting too laborintensive.Your company has grown and now you need to synchronize QuickBooks data with othersoftware applications or reporting systems.These are all good reasons to dive into custom reporting with ODBC.Custom reporting provides direct, read-only access to your QuickBooks company file. This lets youcreate custom reports in ODBC compliant applications such as Microsoft Access, Microsoft Excel, andothers.Creating custom reports with QuickBooks can be challenging. But once you master the basic steps andtechniques, you'll find a whole new world of reporting open up to you. Most important: when you need areport that QuickBooks doesn't provide, you'll have a solution. You'll be able to access QuickBooksdata and massage it into useful information to help you run your business.What You Will LearnHere's what you will learn in this guide: Understand the QuickBooks databaseHow to create an ODBC user in QuickBooksHow to create ODBC connections with:o Microsoft Accesso Microsoft Excelo SAP Crystal ReportsSample custom reportsTables Cross Reference and Field Guide2 Page
QuickBooks Custom Reporting-ODBC Implementation GuideProcess OverviewThere are several steps to create a custom report using ODBC.1. Create an ODBC user in QuickBooks.2. Create an ODBC connection with an ODBC compliant application.3. Create a custom report:a. Open the ODBC connection from your ODBC compliant application.b. Select the data (tables) you want to use on your report (typically using queries).c. Build relationships (or joins) as necessary between the selected data tables.d. Select the QuickBooks fields to display on your custom report.e. Organize and format the report based on your needs and the capabilities of your ODBCcompliant application.This guide teaches you each of these steps and provides examples to help you get started.Recommendation for New UsersWhether you are new to QuickBooks or new to Open Database Connectivity, you can learn to createcustom reports with some practice, some experimentation, and some time. Parts of this guide arehighly technical and may be beyond what you would ever want to know. Focus on the chapters that arerelevant to you. Here is our recommendation: Skip directly to chapter 3 and create an ODBC user. Decide which application to use to create custom reports (Access, Excel, or Crystal Reports)and follow the steps to create an ODBC connection and your first simple custom report. Practice creating custom reports using the additional examples provided in this guide. Practice creating custom reports using information you are already very familiar with. Forexample, take a simple report you run within QuickBooks and try to reproduce it with customreporting. This will give you the confidence to start experimenting. Finally, try creating that custom report you've always wanted.Expect to stumble a little along the way. When you hit a roadblock, pick up this guide and see if itprovides an answer to your problem. Don’t be afraid to ask other people for help. Search the internetand you'll find many people willing to help you along. Good luck and happy reporting.RequirementsTo create custom reports with QuickBooks Enterprise Solutions, you need version 11.0 or higher, andan ODBC compliant application. You should have strong technical computer skills and be comfortableworking with relational databases.Versions UsedThe examples used in this guide are based on the versions listed below. If you use different versions,the concepts still apply but the actual steps may vary. If you use a different ODBC-compliantapplication, the steps will be slightly different but the principles are the same. QuickBooks Enterprise Solutions 11.0 Microsoft Access 2007 Microsoft Excel 2007 SAP Crystal Reports 20083 Page
QuickBooks Custom Reporting-ODBC Implementation GuideChapter 2: Understanding the QuickBooks DatabaseThe QuickBooks company file is a database made up of many tables of data. To create custom reports,it's crucial that you understand: Different types of tables QuickBooks lists and transactions and matching tables and key fields Field ID Cross References Which fields contain usable information in each table Table relationshipsDifferent Types of QuickBooks Tables accessible via ODBCThere are several types of tables accessible via ODBC. List Tables: have v lst (list name) in the table name.Such as v lst customer Transaction Tables: have v txn (transaction type) in the table name.Such as v txn check lineThere are several sub-categories of transaction tables: Header (contains no line item detail): has hdr at the end of the table name.Such as: v txn invoice hdr Line (contains header information line item detail): has line at the end of the tablename.Such as: v txn invoice line Inventory (contains inventory accounting information such as asset and COGSaccounts on inventory transactions): has line inv at the end of the table name.Such as: v txn invoice line invOther Tables: There are a few other tables that are explained where applicable in this guide.4 Page
QuickBooks Custom Reporting-ODBC Implementation GuideQuickBooks Tables and Key FieldsQuickBooks ListsThe key field for all list tables is the ID field.List NameCustomers & Jobs ListChart of AccountsClass ListCurrency ListCustomer Messages ListCustomer Type ListEmployees ListFixed Assets ListItem ListJob Types ListOther Names ListPayment Method ListPrice Level ListSales Rep ListShip Via ListTerms ListVendors ListVendor Type ListTable Name(Group Prefix View/Table Suffix)QBReportAdminGroup v lst customerQBReportAdminGroup v lst accountQBReportAdminGroup v lst classQBReportAdminGroup v lst currencyQBReportAdminGroup v lst customer messageQBReportAdminGroup v lst customer typeQBReportAdminGroup v lst employeeQBReportAdminGroup v lst fixed assetQBReportAdminGroup v lst itemQBReportAdminGroup v lst job typeQBReportAdminGroup v lst otherQBReportAdminGroup v lst payment methodQBReportAdminGroup v lst price levelQBReportAdminGroup v lst sales repQBReportAdminGroup v lst ship viaQBReportAdminGroup v lst termsQBReportAdminGroup v lst vendorQBReportAdminGroup v lst vendor typeMiscellaneous Internal QuickBooks ListsThe key field for all list tables is the ID field.List NameTransaction Types ListCustomer Custom Fields ListVendor Custom Fields ListEmployee Custom Fields ListItem Custom Fields ListShip To Addresses ListTimeSheet Entries ListUnit of Measure ListContacts (combined list of names andcontact information for customers,vendors, employees, and other names)Names (combined list of names andassociated type for customers, vendors,employees, and other names)5 PageTable NameQBReportAdminGroup v lookup txn typeQBReportAdminGroup v cf customerQBReportAdminGroup v cf vendorQBReportAdminGroup v cf employeeQBReportAdminGroup v cf itemQBReportAdminGroup v lst ship toQBReportAdminGroup v lst time activityQBReportAdminGroup v lst unitQBReportAdminGroup v lst contactsQBReportAdminGroup v lst names
QuickBooks Custom Reporting-ODBC Implementation GuideQuickBooks TransactionsTable TypesFor each transaction type, there is a table that ends in hdr and a table that ends in line. Sometransaction types also have a table that ends in line inv.The hdr or "header" table only includes a summary of the transaction (no line item detail). It's useful ifyou are looking for a simple count or listing of transactions, but no detail.The line table includes all of the fields in the hdr table, but also includes fields to hold line itemdetails and amounts.The line inv table includes the inventory accounting part of the transaction, such as the inventoryasset and cost of good sold account references.Key FieldsThe key field for all hdr tables is the transaction id field.The key field for all line tables is the combination of transaction id and target id fields.The key field for line inv tables is the inventory record id field. The target id field is a link back to thetarget id field in the line table.Table ExamplesBelow, we show the table name prefix for each transaction type such asQBReportAdminGroup v txn check.When selecting a table or tables in an ODBC connection, select the appropriate table type to get thelevel of detail you need, such as QBReportAdminGroup v txn check line.Table DescriptionAR-Refund Line Item DetailInventory Build AssembliesBill Payment (by Credit Card)Bill Payment (by Check)Vendor BillsCredit Card ChargesCredit Card CreditsStatement ChargesChecksCredit MemosDepositsEstimatesInventory AdjustmentsInvoicesItem ReceiptsGeneral Journal EntriesPurchase OrdersReceive Payments (Customer Payments)Sales OrdersSales ReceiptsVendor Credit Memos6 PageTable Name(Group Prefix View/Table Suffix)QBReportAdminGroup v txn ar refundQBReportAdminGroup v txn assemblyQBReportAdminGroup v txn bill ccQBReportAdminGroup v txn bill checkQBReportAdminGroup v txn billQBReportAdminGroup v txn cc chargeQBReportAdminGroup v txn cc creditQBReportAdminGroup v txn chargeQBReportAdminGroup v txn checkQBReportAdminGroup v txn credit memoQBReportAdminGroup v txn depositQBReportAdminGroup v txn estimateQBReportAdminGroup v txn inv adjustQBReportAdminGroup v txn invoiceQBReportAdminGroup v txn item receiptQBReportAdminGroup v txn journalQBReportAdminGroup v txn poQBReportAdminGroup v txn received paymentQBReportAdminGroup v txn sales orderQBReportAdminGroup v txn sales receiptQBReportAdminGroup v txn vendor credit
QuickBooks Custom Reporting-ODBC Implementation GuideField ID Cross ReferencesItem Types (no associated table)Item Type Code01234567891011Item Type DescriptionServiceInventory PartInventory AssemblyNon-inventory PartFixed AssetOther ChargeSubtotalGroupDiscountPaymentSales Tax ItemSales Tax GroupAccount Types (no associated table)Type0123456789101112131415Account Type DescriptionBankAccounts ReceivableOther Current AssetFixed AssetOther AssetAccounts PayableCredit CardOther Current LiabilityLong Term LiabilityEquityIncomeCost of Goods SoldExpenseOther IncomeOther ExpenseNon-PostingNames Lists (v lst names)Type Id01237 PageList TypeCustomerVendorEmployeeOther Name
QuickBooks Custom Reporting-ODBC Implementation GuideTables and Important FieldsQBReportAdminGroup v lst customerField NameIdNameIs hiddenOpen dateCredit limit amtCustomer type idParent idDescriptionUnique number identifying the entry in thelistName of the list entryIs the customer:job active or inactive?Date the list entry was first used on atransactionCredit limit for the customer:jobType of Customer – on Customer TypelistApplies to Jobs.Ship to idId of the ship to addressEnd balance amt Customer's current balanceSales tax code id Is this customer taxable or non-taxable?Tax item idTerms idNameIs hiddenOpen dateDescriptionUnique number identifying the entry in thelistName of the list entryIs the vendor active or inactive?Credit limit amtVendor type idDate the list entry was first used on atransactionCredit limit for the vendorType of Vendor – on Vendor Type listEnd balance amtTerms idIs 1099 vendorVendor's current balanceThe vendor's payment termsIs the vendor eligible for a 1099 formTax identifierVendor's tax id number8 PagePossible Values0 ACTIVE-1 or 1 INACTIVEDepends on thenumber of customertypesIf this is a job for acustomer, theparent id will point tothe customer the job isrelated to. Otherwise,the parent id 0.Points to list of salestax codes which areeither taxable or nontaxable.Id of the sales tax item – defines what taxrate the customer pays on taxable salesThe customer's payment termsQBReportAdminGroup v lst vendorField NameIdCustomers & Jobs ListVendor ListPossible Values0 ACTIVE-1 or 1 INACTIVEDepends on thenumber of customertypes0 NOT ELIGIBLE-1 of 1 ELIGIBLE
QuickBooks Custom Reporting-ODBC Implementation GuideQBReportAdminGroup v lst itemField NameIdItem ListNameDescriptionPurchase descriptionIs hiddenDescriptionUnique number identifying the entry inthe listName of the list entryDescription of item on sales formsDescription of item on purchasesIs the vendor active or inactive?item type idType of ItemParent idIf item is a subitem, shows id of parentitemMfg part numUnit cost amtAverage cost qntyIs groupManufacturer's part numberCost of item (on purchases)Average cost of item from priorpurchasaesSales price of item to customersIncome account for items soldId of vendor you typically purchaseitem fromId of asset account used to hold thevalue of inventory until the item is soldThe account used to store the cost ofthe items after being sold to acustomerIs this a "group" type item?Sales tax code idIs this item taxable or non-taxable?Tax agency idId of tax agency (only applies to salestax items)Quantity at which the item should beassembled (to build more)Quantity at which the item should bepurchased (to have more on-hand)Current on-hand quantityValue of inventory on hand in dollarsQuantity of item already on outstandingpurchase order(s)Quantity of item on pending inventoryassembly (component)Quantity of item on sales orderQuantity of item on pending inventoryassembly (final product)Sales price amtPost account idPreferred vendor idAsset account idCogs account idAssembly build point qtyReorder point qtyOn hand qtyValue on hand amtOn po order qtyReserved for pendingassemblies qtyOn so order qtyPending assemblies qty9 PagePossible Values0 ACTIVE-1 or 1 INACTIVESee separate list underField ID Cross Referenceson page 7If this is a subitem for an item,the parent id will point to theitem it is related to. Otherwise,the parent id 0.0 Not a group item-1 or 1 Is a group itemPoints to list of sales tax codeswhich are either taxable ornon-taxable.
QuickBooks Custom Reporting-ODBC Implementation GuideQBReportAdminGroup v lst accountField NameIdChart of Accounts ListTypeDescriptionUnique number identifying the entry in thelistType of AccountAccount numNameDescriptionIs hiddenAccount NumberName of the list entryDescription of accountIs the account active or inactive?Parent idIf account is a subaccount, shows id ofparent accountEnding balance amt Account's current balanceBank numberThe bank account number for bank typeaccounts10 P a g ePossible ValuesSee separate list underField ID CrossReferenceson page 70 ACTIVE-1 or 1 INACTIVEIf this is a subaccountfor an account, theparent id will point tothe account it is relatedto. Otherwise, theparent id 0.Can also be a “Note” or“Credit Card Number”depending on accounttype.
QuickBooks Custom Reporting-ODBC Implementation GuideTable RelationshipsThe following list shows important tables and which relate to other tables.Parent TableNames(v lst names)Contacts(v lst contacts)Parent FieldIdName IdCustomersId(v lst customer)Customer type idSales tax code idShip to idTerms id11 P a g eRelated TableCustomers(v lst customer)Vendors(v lst vendor)Employees(v lst employee)Other Names(v lst other)Customers(v lst customer)Vendors(v lst vendor)Employees(v lst employee)Other Names(v lst other)Contacts(v lst contacts)Names(v lst names)Customer Types(v lst customer type)Sales Tax Codes(v lst sales tax)Ship To Addresses(v lst ship to)Payment Terms(v lst terms)Related FieldIdIdIdIdIdIdIdIdName idIdIdIdIdId
QuickBooks Custom Reporting-ODBC Implementation GuideAdditional InformationSource and Target Records:QuickBooks transactions consist of multiple table records: one source (also called a "master" or"header") record and one or more target records for each line item of detail. For transactions includingsales tax, QuickBooks adds an additional target record.The first record for a transaction is the source record (is source -1 or 1). The source record includesthe first target (target id field). Then each target points to the next target record (next target id field).This creates a chain or link between line items for the same transaction and to the source record.When a transaction is created, the source record is assigned a record number N. The target recordsare assigned a record number N 1, N 2, etc. A transaction can be modified later, and target recordscan be added and deleted. See diagram below for help understanding this structure.When added later, target records are assigned the next available sequential record number (not nextavailable for the particular transaction, but next available target number based on the last targetnumber (N) for the last transaction entered in the company file. Line items (targets) in QuickBooks canbe added between exiting line items of an existing transaction, thus causing target id numbers to nolonger be consecutive.To recreate the transaction detail records the same way they are viewed in QuickBooks, it's necessaryto list each record based on the next target id, which numerically may be out of sequence, but onscreen will match what QuickBooks displays.Each record in a transaction table is therefore unique by using the transaction id field and the target idfield as a combined primary key. Also, this combined primary key is unique across all records and alltransaction tables within a QuickBooks company file.12 P a g e
QuickBooks Custom Reporting-ODBC Implementation GuideFor example: Let's examine a Check transaction that includes two expense accounts.Check in QuickBooksQBReportAdminGroup v txn check line(filtered to just show this one transaction, and only showing important fields)13 P a g e
QuickBooks Custom Reporting‐ODBC Implementation GuideChapter 3: Create an ODBC user in QuickBooksAn ODBC user is like a "gatekeeper" between ODBC applications and your company data. The usercreates the connection to a compliant application.An ODBC user is different than a QuickBooks user. ODBC users: Have permission to access your company data only for custom reporting purposes. Can create custom reports through an ODBC connection. Have full admin, read-only access to your company file. Can’t sign in to QuickBooks to perform any other activities.Note: Unlike a QuickBooks user, you can’t customize or limit an OBDC user’s access to company data.Create an ODBC userTo create an ODBC user, complete the following steps:1.2.3.4.5.6.7.8.Open QuickBooks and sign in to your company file as the administrator.Choose Reports Custom Reporting.Cl
Chapter 2: Understanding the QuickBooks Database The QuickBooks company file is a database made up of many tables of data. To create custom reports, it's crucial that you understand: Different types of tables QuickBooks lists and transactions and
