Learn more
 

In this article

Related articles

Extending QuickBooks Desktop Reporting


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

Query Reports

You can use the ADO.NET provider to retrieve and search data in reports. You can retrieve detailed reports by calling the corresponding stored procedure. Many report types are available. For example, you can execute the following command to retrieve a standard profit and loss report: 

EXEC ReportGeneralSummary ReportType='PROFITANDLOSSSTANDARD'

To execute SELECT queries against reports, you can call the CreateSimpleReportSchema stored procedure to generate the schema, or view definition, for the report. Schemas are simple, XML-based files that are easy to extend. You can avoid regenerating the entire report by modifying report parameters in the schema.

Generate Report Schemas

You can call the CreateSimpleReportSchema stored procedure to generate the schema for almost any report available in QuickBooks Desktop edition.

Before you call the stored procedure, you will first need to set the Location connection property to the location of the table schemas. This is the db subfolder in the installation directory.

After setting the Location property, call CreateSimpleReportSchema with the needed inputs. For example, to create the Standard Profit and Loss Report, set the ReportType to 'PROFITANDLOSSSTANDARD': 

EXEC CreateSimpleReportSchema ReportType=PROFITANDLOSSSTANDARD

When you reconnect, the provider will pick up the newly created schema. Schemas are simple, XML-based .rsd files that include column definitions and allowed 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 several available columns for the ReportProfitAndLossStandard view: 

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">

  <rsb:info title="ReportProfitAndLossStandard" description="Profit & Loss Standard Report.">
    <attr name="ID" xs:type="integer" readonly="true" key="true" desc="The row number." />
    <attr name="Label" xs:type="string" readonly="true" desc="The Label column of the ProfitAndLossStandard report." />
    <attr name="Dec_15_17" xs:type="double" readonly="true" desc="The Dec_15_17 column of the ProfitAndLossStandard report." />

    <input name="ReportPeriod"               desc="Report date range in the format fromdate:todate where either value may be omitted for an open-ended range (e.g., 2009-12-25:). Supported date format: yyyy-MM-dd." />
    <input name="ReportDateRangeMacro"       desc="A macro that can be specified for the report date range." value="ALL,TODAY,THISWEEK,THISWEEKTODATE,THISMONTH,THISMONTHTODATE,THISQUARTER,THISQUARTERTODATE,THISYEAR,THISYEARTODATE,YESTERDAY,LASTWEEK,LASTWEEKTODATE,LASTMONTH,LASTMONTHTODATE,LASTQUARTER,LASTQUARTERTODATE,LASTYEAR,LASTYEARTODATE,NEXTWEEK,NEXTFOURWEEKS,NEXTMONTH,NEXTQUARTER,NEXTYEAR" />
    <input name="AccountType"                desc="The specific type of account to request in the report." value="NONE,ACCOUNTSPAYABLE,ACCOUNTSRECEIVABLE,ALLOWEDFOR1099,APANDSALESTAX,APORCREDITCARD,ARANDAP,ASSET,BALANCESHEET,BANK,BANKANDARANDAPANDUF,BANKANDUF,COSTOFSALES,CREDITCARD,CURRENTASSET,CURRENTASSETANDEXPENSE,CURRENTLIABILITY,EQUITY,EQUITYANDINCOMEANDEXPENSE,EXPENSEANDOTHEREXPENSE,FIXEDASSET,INCOMEANDEXPENSE,INCOMEANDOTHERINCOME,LIABILITY,LIABILITYANDEQUITY,LONGTERMLIABILITY,NONPOSTING,ORDINARYEXPENSE,ORDINARYINCOME,ORDINARYINCOMEANDCOGS,ORDINARYINCOMEANDEXPENSE,OTHERASSET,OTHERCURRENTASSET,OTHERCURRENTLIABILITY,OTHEREXPENSE,OTHERINCOME,OTHERINCOMEOREXPENSE" />
    ...
  </rsb:info>

You can then use SELECT statements to query the report: 

SELECT * FROM ReportProfitAndLossStandard WHERE ReportPeriod = '1/1/2015: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 detail, profit and loss detail, and standard balance sheet. Simply copy the .rsd files into the db subfolder in the installation directory, set the Location property to this folder, and reconnect.