Learn more
 

In this article

Related articles

Extending QuickBooks Online Reporting


The CData Data Providers for QuickBooks Online enable you to request almost any report available in QuickBooks Online. You can access reports as views, or read-only tables. This article shows how to use the ADO.NET provider to execute SQL queries against QuickBooks Online reports.

Reports are not surfaced as tables by default for the CDataQuickBooks Online data providers. You can generate schemas, or view definitions, for the reports you want by calling the corresponding stored procedure.

Generate Report Schemas

Before you call the stored procedure, you will first need to set the Location connection property to the location of the table schemas. After setting the Location property, call the stored procedure with the needed inputs. 

EXEC CreateProfitAndLossSummaryReport

When you reconnect, the provider will pick up the newly created schema. Schemas are simple, XML-based files that include column definitions and allowed column values.

Opening the .rsd file, you can easily find information about how to use columns in the WHERE clause. Columns are defined with the attr attribute in the rsb:info section. Additionally, pseudo columns are defined with input attribute and can be used in the WHERE clause. Below is an example of the columns and allowed values for the ProfitAndLossSummaryReport view: 

<rsb:info title="ProfitAndLossSummaryReport" description="The QuickBooks report ProfitAndLoss.">
  <attr name="Account" xs:type="string" readonly="true" desc="A column for Account containing Account data." />
  <attr name="Total" xs:type="double" readonly="true" desc="A column for Total containing Money data." />
  <input name="StartDate"                  desc="The startdate of the report. StartDate must be less than EndDate." default="2015-01-01"/>
  <input name="EndDate"                    desc="The enddate of the report. EndDate must be greater than StartDate." default="2015-12-31"/>
  <input name="DateMacro"                  desc="A predefined date range. Use this if you want the report to cover a standard report date range. Otherwise, use the StartDate and EndDate to cover an explicit range."  values="Today,Yesterday,This Week,This Week-to-date,Last Week,Last Week-to-date,Next Week,Next 4 Weeks,This Month,This Month-to-date,Last Month,Last Month-to-date,Next Month,This Fiscal Quarter,This Fiscal Year,This Fiscal Year-to-date,Last Fiscal Year,Last Fiscal Year-to-date,Next Fiscal Year" />
  <input name="AccountingMethod"           desc="The accounting method used in the report." values="Cash,Accrual" />
  <input name="Customer"                   desc="Filters report contents to include information for specified customers. This is a comma separated list of one or more Customer IDs." />
  <input name="Vendor"                     desc="Filters report contents to include information for specified vendors. This is a comma separated list of one or more Vendor IDs." />
  <input name="Item"                       desc="Filters report contents to include information for specified items. This is a comma separated list of one or more Item IDs." />
  <input name="Class"                      desc="Filters report contents to include information for specified classes if so configured in the company file. This is a comma separated list of one or more Class IDs." />
  <input name="Department"                 desc="Filters report contents to include information for specified departments if so configured in the company file. This is a comma separated list of one or more Department IDs." />
</rsb:info>

You can then query the report: 

SELECT * FROM ProfitAndLossSummaryReport WHERE StartDate='1/1/2015' AND EndDate='12/31/2015'

In Server Explorer, the report will be available in the Views folder:

Example Schemas

To help you get started using the provider to access QuickBooks reports, you can download the sample report schemas. You can use the schemas to immediately start querying the following reports: aging summary and profit and loss summary. Simply copy the .rsd files into the db subfolder in the installation directory, set the Location property to this folder, and reconnect.