Connecting to Quickbooks Desktop

The Sisense Quickbooks Desktop connector is a standalone connector that allows you to import data from Quickbooks Desktop’s API into the ElastiCube Manager. After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense in the ElastiCube Manager. The connection string is used to authenticate users who connect to the Quickbooks Desktop APIs. To obtain a connection string, you will need to create a Quickbooks Desktop app.

Once you have connected to Quickbooks Desktop, you can import a variety of tables from the Quickbooks Desktop API.

This page describes how to install the Quickbooks Desktop connector, how to connect to Quickbooks Desktop with a connection string, and what tables you can import into the ElastiCube Manager:

Installing the Quickbooks Desktop Connector

Sisense provides the Quickbooks Desktop connector as a standalone connector that you can download and add to your list of default Sisense connectors.

To install the Quickbooks Desktop connector:

  1. Download the Quickbooks Desktop installation file.
  2. Open the installation file and click Install.
  3. After the installation process is complete, click Close.

The Quickbooks Desktop connector is displayed in the ElastiCube Manager under Add Data > Web Services.

Connecting to the Quickbooks Desktop REST API

The Quickbooks Desktop connector makes requests to QuickBooks through the Remote Connector. The Remote Connector is an easy-to-use tool that enables developers to access QuickBooks data remotely.The Remote Connector runs on the same machine as QuickBooks and accepts connections through a lightweight, embedded Web server.

The server supports SSL/TLS, enabling users to connect securely from remote machines. The first time you connect, you will need to authorize the driver with QuickBooks.

The Remote Connector can be used to read and write to QuickBooks in situations where direct COM access to QuickBooks is not available (e.g., ASP.NET, Java, or QuickBooks on a remote machine).

In the Remote Connector, you can define users and their passwords and then use these credentials in the connection string that you create to connect to your QuickBooks Desktop tables.

Follow the procedure below to connect to QuickBooks for the first time through the Remote Connector:

  1. Download the Remote Connector from remoteconnector.com and install the Remote Connector on the machine where QuickBooks is installed.
  2. Open the company file you want to connect to in QuickBooks using an administrator account in single-user mode.
  3. Open the Remote Connector from the system tray and add a user on the Users tab. Enter a User and Password and select the level of access in the Data Access menu.

    Note: The Remote Connector does not use the User and Password properties to access QuickBooks; the User and Password properties authenticate the user to the Remote Connector. Authentication to QuickBooks is handled based on the Application Name property.
  4. When you first connect, a dialog will appear in QuickBooks prompting you to authorize the application. After authorizing the application, you can then execute commands to QuickBooks. Specify the URL of the Remote Connector and the User and Password. By default, the Remote Connector connects to the currently open company file.
  5. If you want to access QuickBooks when QuickBooks is not running, save the company file information for the user. The Remote Connector will then automatically open QuickBooks in the background with the company file for that user.

Note: If the QuickBooks UI is open, you can only connect to that company file. Additionally, note that the user permissions you run the Remote Connector under must match the user permissions you run QuickBooks under. The Remote Connector installation process installs the Remote Connector as a service under the current user account.

Adding Quickbooks Desktop Tables to your ElastiCube

Sisense uses connection strings to connect to Quickbooks Desktop and import data into the ElastiCube Manager.

The connection string to connect to Quickbooks Desktop has the following structure:

jdbc:Quickbooks Desktop:Property1=Value1;Property2=Value2;

The following is an example of a Quickbooks Desktop connection string:

jdbc:quickbooks:User=xxxxx;Password=xxxxxxxxxx;URL=http://xxxxxxxxxxxx;

Note: To switch between accounts, you need to delete the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\QuickbooksDesktop\DataProvider.

To add Quickbooks Desktop data:

  1. In ElastiCube Manager, click Add Data and then, Quickbooks Desktop. The Connect to Quickbooks Desktop window is displayed.
  2. In Datasource Connection String, enter your connection string.
  3. Click Connect to Server. Quickbooks Desktop is displayed in the Select Database list.
  4. Click OK. Sisense connects to Quickbooks Desktop and displays a list of tables available for you to import.
  5. Select the relevant tables and click Add.
    The tables are displayed in the ElastiCube Manager.

Switching between Accounts

When you connect to the Quickbooks Desktop data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Quickbooks Desktop Data Provider on your Sisense server. To connect to the Quickbooks Desktop data source with another user on the same machine, you must delete the OAuthsettings.txt file. Sisense will then generate a new file for that user.

Another option to support multiple users is to define the location and file name of an OAuthsettings file for each unique user in your connection string through the OAuthSettingsLocation parameter. When each user connects to the data source, Sisense generates the OAuth file with the file name you specify in the location you define. In the examples below, two users are allowed to access the Quickbooks Desktop data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.

jdbc:QuickbooksDesktop:OAuthSettingsLocation=C:\QuickbooksDesktop\auth\john.txt;OAuthClientId=11276856774486;

OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

jdbc:QuickbooksDesktop:OAuthSettingsLocation=C:\QuickbooksDesktop\auth\sally.txt;OAuthClientId=11276856774486;

OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

In the example above, to OAuth files are created, one for John and one for Sally in the location C:\QuickbooksDesktop\auth\.

This is useful if you support many users who each need to access the Quickbooks Desktop data source.

Quickbooks Desktop Tables

Quickbooks Desktop’s RESTful APIs expose the following Quickbooks Desktop tables that you can import into the ElastiCube Manager through the Sisense Quickbooks Desktop connector:

Available Tables

Name Description
Accounts Create, update, delete, and query QuickBooks Accounts. To update Accounts, set the QBXMLVersion to 6.0 or higher.
BillExpenseItems Create, update, delete, and query QuickBooks Bill Expense Line Items.
BillLineItems Create, update, delete, and query QuickBooks Bill Line Items.
BillPaymentChecks Create, update, delete, and query QuickBooks Bill Payment Checks. QBXMLVersion must be set to 6.0 or higher to update a BillPaymentCheck.
BillPaymentChecksAppliedTo Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
BillPaymentCreditCards Create, update, delete, and query QuickBooks Bill Payments.
BillPaymentCreditCardsAppliedTo Create, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
Bills Create, update, delete, and query QuickBooks Bills.
BuildAssemblies Insert, Update, delete, and query QuickBooks Build Assembly transactions.
BuildAssemblyLineItems Create and query QuickBooks Build Assembly transactions.
CheckExpenseItems Create, update, delete, and query QuickBooks Check Expense Line Items.
Checks Create, update, delete, and query QuickBooks Check Line Items.
Class Create, update, delete, and query QuickBooks Classes. QuickBooks requires QBXML version 8.0 or higher for updates to a Class.
CreditCardChargeExpenseItems Create, update, delete, and query QuickBooks Credit Card Charge Expense Line Items.
CreditCardChargeLineItems Create, update, delete, and query QuickBooks Credit Card Charge Line Items.
CreditCardCharges Create, update, delete, and query QuickBooks Credit Card Charges.
CreditCardCreditExpenseItems Create, update, delete, and query QuickBooks Credit Card Credit Expense Line Items.
CreditCardCreditLineItems Create, update, delete, and query QuickBooks Credit Card Credit Line Items.
CreditCardCredits Create, update, delete, and query QuickBooks Credit Card Credits.
CreditMemoLineItems Create, update, delete, and query QuickBooks Credit Memo Line Items.
CreditMemos Create, update, delete, and query QuickBooks Credit Memos.
Currency Create, update, delete, and query QuickBooks Currencies. This table requires QBXML version 8.0 or higher, and you will need to enable multiple Currencies in your QuickBooks company file to use it.
CustomerContacts Create, update, delete, and query QuickBooks Customer Contacts. This table requires QBXML version 12.0 or higher, and is only available in QuickBooks editions 2016 and above.
CustomerMessages Create, delete, and query Customer Messages.
CustomerNotes Create, update, and query QuickBooks Customer Notes. This table requires QBXML version 12.0 or higher.
Customers Create, update, delete, and query QuickBooks Customers.
CustomerShippingAddresses Create, update, delete, and query QuickBooks Customer Shipping Addresses. Multiple Customer Shipping Addresses are supported in only QuickBooks 2013 and higher. Additionally, this table requires QBXML version 12.0 or higher.
CustomerTypes Create, update, delete, and query QuickBooks Customer Types.
DateDrivenTerms Create, delete, and query QuickBooks Date Driven Terms.
DepositLineItems Create, update, delete, and query QuickBooks Deposit Line Items. QBXMLVersion must be set to 7.0 or higher to update a deposit.
Deposits Create, update, delete, and query QuickBooks Deposits. QBXMLVersion must be set to 7.0 or higher to update a deposit.
EmployeeEarnings Create, update, delete, and query QuickBooks Employee Earnings.
Employees Create, update, delete, and query QuickBooks Employees.
EstimateLineItems Create, update, delete, and query QuickBooks Estimate Line Items.
Estimates Create, update, delete, and query QuickBooks Estimates.
InventoryAdjustmentLineItems Create and query QuickBooks Inventory Adjustment Line Items.
InventoryAdjustments Create, query, and delete QuickBooks Inventory Adjustments.
InventorySites Create, update, delete, and query QuickBooks Inventory Sites. Inventory Sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
InvoiceLineItems Create, update, delete, and query QuickBooks Invoice Line Items.
Invoices Create, update, delete, and query QuickBooks Invoices.
ItemLineItems Create, update, delete, and query QuickBooks Item Line Items.
ItemReceiptExpenseItems Create, update, delete, and query QuickBooks Item Receipt Expense Line Items.
ItemReceiptLineItems Create, update, delete, and query QuickBooks Item Receipt Line Items.
ItemReceipts Create, update, delete, and query QuickBooks Item Receipts.
Items Create, update, delete, and query QuickBooks Items.
JobTypes Create and query QuickBooks JobTypes.
JournalEntries Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
JournalEntryLines Create, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
OtherNames Create, update, delete, and query QuickBooks Other Name entities.
PaymentMethods Create, update, delete, and query QuickBooks Payment Methods.
PayrollNonWageItems Query QuickBooks Non-Wage Payroll Items.
PayrollWageItems Create and query QuickBooks Wage Payroll Items.
PriceLevelPerItem Create and query QuickBooks Price Levels Per Item. Only QuickBooks Premier and Enterprise support Per-Item Price Levels. Note that while Price Levels can be added from this table, you may only add Per-Item Price Levels from this table. Price Levels may be deleted from the PriceLevels table. This table requires QBXML version 4.0 or later.
PriceLevels Create, delete, and query QuickBooks Price Levels. Note that while Price Levels can be added and deleted from this table, you may add only fixed-percentage Price Levels from this table. Per-Item Price Levels may be added via the PriceLevelPerItem table. This table requires QBXML version 4.0 or higher.
PurchaseOrderLineItems Create, update, delete, and query QuickBooks Purchase Order Line Items.
PurchaseOrders Create, update, delete, and query QuickBooks Purchase Orders.
ReceivePayments Create, update, delete, and query QuickBooks Receive Payment transactions. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment.
ReceivePaymentsAppliedTo Create, update, and query QuickBooks Receive Payment AppliedTo aggregates. In a Receive Payment, each AppliedTo aggregate represents the transaction to which this part of the payment is being applied. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment.
SalesOrderLineItems Create, update, delete, and query QuickBooks Sales Order Line Items.
SalesOrders Create, update, delete, and query QuickBooks Sales Orders.
SalesReceiptLineItems Create, update, delete, and query QuickBooks Sales Receipt Line Items.
SalesReceipts Create, update, delete, and query QuickBooks Sales Receipts.
SalesReps Create, update, delete, and query QuickBooks Sales Rep entities.
SalesTaxCodes Create, update, delete, and query QuickBooks Sales Tax Codes.
SalesTaxItems Create, update, delete, and query QuickBooks Sales Tax Items.
ShippingMethods Create, update, delete, and query QuickBooks Shipping Methods.
StandardTerms Create, update, delete, and query QuickBooks Standard Terms.
StatementCharges Create, update, delete, and query QuickBooks Statement Charges.
TimeTracking Create, update, delete, and query QuickBooks Time Tracking events.
ToDo Create, update, delete, and query QuickBooks To Do entries.
TransferInventory Query and delete QuickBooks Transfer Inventory transactions. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
TransferInventoryLineItems Create and query QuickBooks Transfer Inventory Line Items. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
Transfers Create, update, and query QuickBooks transfers. Requires QBXML version 12.0 or higher.
UnitOfMeasure Create, update, delete, and query QuickBooks units of measure. QuickBooks requires QBXML version 7.0 or higher to use this table.
UnitOfMeasureDefaultUnits Create and query QuickBooks unit-of-measure default units. QuickBooks requires QBXML version 7.0 or higher to use this table.
UnitOfMeasureRelatedUnits Create and query QuickBooks unit-of-measure related units. QuickBooks requires QBXML version 7.0 or higher to use this table.
VehicleMileage Create, update, delete, and query QuickBooks Vehicle Mileage entities. QuickBooks requires QBXML version 6.0 or higher to use this table.
VendorCreditExpenseItems Create, update, delete, and query QuickBooks Vendor Credit Expense Line Items.
VendorCreditLineItems Create, update, delete, and query QuickBooks Vendor Credit Line Items.
VendorCredits Create, update, delete, and query QuickBooks Vendor Credits.
Vendors Create, update, delete, and query QuickBooks Vendors.
VendorTypes Create, update, delete, and query QuickBooks Vendor Types.
WorkersCompCodeLines Query QuickBooks Workers Comp Code entries by line. Requires QBXML Version 7.0 or higher.
WorkersCompCodes Query QuickBooks Workers Comp Code entries. Requires QBXML Version 7.0 or higher.

Available Views

Name Description
BillLinkedTransactions Query QuickBooks Bill Linked Transactions.
CompanyInfo Query the company information from QuickBooks.
CreditMemoLinkedTransactions Query QuickBooks Credit Memo Linked Transactions.
DeletedEntities Query deleted Entities.
DeletedTransactions Query deleted Transactions.
EstimateLinkedTransactions Query QuickBooks Estimate Linked transactions.
Host Query the QuickBooks host process. The Host represents information about the QuickBooks process currently being executed.
InvoiceLinkedTransactions Query QuickBooks Invoice Linked Transactions.
ItemReceiptLinkedTransactions Query QuickBooks Item Receipt Linked Transactions.
ItemSites Create, update, delete, and query QuickBooks Item Sites. Item sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. This table requires a minimum of QBXML version 10.0.
Preferences Query information about many of the preferences the QuickBooks user has set in the company file.
PurchaseOrderLinkedTransactions Query QuickBooks Purchase Order Linked Transactions.
SalesOrderLinkedTransactions Query QuickBooks Sales Order Linked Transactions.
StatementChargeLinkedTransactions Query QuickBooks Statement Charge Linked Transactions.
Templates Query QuickBooks templates.
Transactions Query QuickBooks transactions. You may search the Transactions using a number of values including Type, Entity, Account, ReferenceNumber, Item, Class, Date, and TimeModified.
VendorCreditLinkedTransactions Query QuickBooks Vendor Credit Linked Transactions.