Processing report responses

Generating reports with the Desktop SDK follows the same request/response model, with qbXML as the message medium, as other SDK-to-QuickBooks interactions. The specific points to consider when processing report responses are:

Report responses contain a mixture of descriptive information (number of rows and columns, column headings, etc.) and the actual rows of report data.
Report response “ret” objects consist of data formatted into rows and columns, which are demarcated with qbXML markup, that an application will iterate over in order to prepare it for display.

This page concentrates on how an application can process a report response. For a general discussion of processing responses, see Receiving and processing responses.

Report response structure

The following example shows how a report response is structured. The first thing to notice is that the response contains two main types of information:

Some descriptive data, beginning with the <ReportRet> tag, that describes the report as a whole, including its title, number of rows and columns, column titles, etc.
The actual report data, beginning with the <ReportData> tag, which is structured as rows of data with accompanying descriptive information.

<qbxml>
  <qbxmlmsgsrs>
    <GeneralSummaryReportQueryRS statuscode="0"
                                 statusmessage="Status OK"
                                 statusseverity="Info">
      <ReportRet>
        <ReportTitle>
          My Sample Report
        </ReportTitle>
        <ReportSubtitle>
          It Has Two Columns -- "Account Name" and "Balance"
        </ReportSubtitle>
        <ReportBasis>
          Accrual
        </ReportBasis>
        <NumRows>
          4
        </NumRows>
        <NumColumns>
          2
        </NumColumns>
        <NumColTitleRows>
          2
        </NumColTitlerows>
        <ColDesc colID="1" datatype="dtSTRTYPE">
          <colTitle titlerow=1 value="Account">
          <colTitle titlerow=2 value="Name">
        </ColDesc>
        <ColDesc colID="2" datatype="dtAMTTYPE">
          <colTitle titlerow=1 value="Balance">
          <colTitle titlerow="2">
        </ColDesc>
        <ReportData>
          <Textrow rownumber="1" value="Accounts and Balances">
          </TextRow>
          <DataRow rownumber="2">
            <RowData rowtype="account" value="nnnn.Account Name">
            </rowdata>
            <ColData ColID="1" value="nnnn.First Account Name">
            </coldata>
            <ColCata ColID="2" value="22222.22">
            </ColData>
          </DataRow>
          <DataRow rownumber="3">
            <RowData rowtype="account" value="nnnn.Second Account Name">
            </RowData>
            <ColData ColID="1" value="nnnn.Second Account Name">
            </coldata>
            <ColCata ColID="2" value="22222.22">
            </ColData>
          </DataRow>
          <SubTotalRow rownumber="4">
            <ColData colid="1" value="Total for Accounts">
            </ColData>
            <ColData ColID="2" value="44444.44">
            </ColData>
          </SubtotalRow>
                    ...
        </ReportData>
      </ReportRet>
    </GeneralSummaryReportQueryRs>
  </qbXMLMsgsRs>
</qbXML

The next few sections look at how this is processed.

Report titling

The report title and the column titles are returned in several different qbXML elements. The values for report title, subtitle, and report basis, are each contained within a single element and appear at the beginning of the response message. They are typically positioned at the top of the report display. Note that not all of the elements in the response are actually displayed. The following shows how the descriptive data from the example above would likely be positioned for display; the tags are included to show you where the displayed text was in the response message:

<ReportTitle>My Sample Report</ReportTitle>
<ReportSubtitle>It Has Two Columns -- Account Name and Balance</ReportSubTitle>
<ReportBasis></ReportBasis>

<NumColTitleRows>2<NumColTitleRows>

Column titling involves several interacting elements. This example places the tags as they would typically be placed for display. Notice that this is a different order than the order in which they appear in the response message:

The actual text for the column titles is contained in the colTitle element’s value attribute.
This example uses two title rows, to demonstrate how the NumColTitleRows element works with the ColDesc elements. NumColTitleRows reserves two rows for column titles; the title for column 1 extends over both rows; the title for column 2 uses only 1 row, and, therefore, the tag for <colTitle titlerow=”2”> has no value.

<ColDesc colID="1" datatype="dtSTRTYPE">      <ColDesc colID="2" datatype="dtAMTTYPE">
<colTitle titlerow=1 value="Account">         <colTitle titlerow=1 value="Balance">
<colTitle titlerow=2 value="Name">            <colTitle titlerow="2">
<ColDesc>                                     </ColDesc>

Report data

Following the <ReportData> tag, each row of report data appears in the response message as a row-type aggregate that contains sub-elements with the data for the columns (cells) in the row. There are four types of row data elements, listed in the following table.

Data row type	Use	Contains these sub-elements
<TextRow>	Contains text only; used mainly to write headings, such as “Income,” or “Cost of Good Sold,” for groups of rows, therefore it does not have column-by-column values	<TextRow rowNumber=”n”> <RowData rowType=”rtlabel” value=”displayed heading text”/> </TextRow>
<DataRow>	Contains the data displayed in report cells. Each cell’s position in the grid is specified by the value of rowNumber and colID. The rowType is an enumerated type that identifies the category of data in the row. The sample here assumes two columns so the data row has two ColData elements.	<DataRow rowNumber=”n”> <RowData rowType=”rowTypeEnum” value=”identifierForRowType”/> <ColData colID=”1” value=”displayed cell value” /> <ColData colID=”2” value=”displayed cell value” /> </DataRow>
<SubTotalRow>	Contains a calculated subtotal. Subtotal rows appear at the end of a logical grouping of rows, such as “Income” rows, and display the total for that group.	<SubTotalRow rowNumber=”n”> <ColData colID=”1” value=”subtotal label text” /> <ColData colID=”2” value=”calculated subtotal value” /> </DataRow>
<TotalRow>	Calculated totals for the entire report	<TotalRow rowNumber=”n”> <ColData colID=”1” value=”total label text” /> <ColData colID=”2” value=”calculated total value” /> </DataRow>

Some points to note:

The column ID refers to the column ID defined in the ColDesc elements that preceded the report data section, and identifies the column in which the value should be placed.
The rowData tag’s rowType and value attributes identify the type of data in the row, and the specific source. For example, in the sample report that follows, rows are identified as rowType=rtaccount, indicating that each of these rows contains the amount for some account in the QuickBooks chart of accounts, account, and the value attribute indicates the specific account.

The row data can be displayed in the order it is received, although not all elements of the are displayed. The rows in the sample report above would typically be placed in rows as follows for display. Once again the tags are included so that you can see where the displayed elements appeared in the response message:

<Textrow rownumber="1" value="Accounts and Balances">
</TextRow>

<ColData ColID="1" value="nnnn.First Account Name"><ColCata ColID="2" value="22222.22">
</coldata>                                         </ColData>
<ColData ColID="1" value="nnnn.SecondAccount Name"><ColCata ColID="2" value="22222.22">
</coldata>                                         </ColData>
<ColData colid="1" value="Total for Accounts">     <ColData ColID="2" value="44444.44">
</ColData>                                         </ColData>
...

Example: a Profit & Loss Standard report

The following example, the first part of a Profit & Loss Standard report, is a more complete example of a report response message, and it is followed by a displayed version of the same report data.

<QBXMLMsgsRs>
    <GeneralSummaryReportQueryRs requestID="862" statusCode="0"
        statusSeverity="Info" statusMessage="Status OK">
        <ReportRet>
            <ReportTitle>Profit & Loss</ReportTitle>
            <ReportBasis>Accrual</ReportBasis>
            <NumRows>55</NumRows>
            <NumColumns>2</NumColumns>
            <NumColTitleRows>2</NumColTitleRows>
            <ColDesc colID="1" dataType="STRTYPE">
                <ColTitle titleRow="1" />
                <ColType>Label</ColType>
            </ColDesc>
            <ColDesc colID="2" dataType="AMTTYPE">
                <ColTitle titleRow="1" value="Dec 1 - 15, 03" />
                <ColType>Amount</ColType>
            </ColDesc>
            <ReportData>
                <TextRow rowNumber="1">
                    <RowData rowType="label" value="Ordinary Income/Expense"/>
                </TextRow>
                <TextRow rowNumber="2">
                    <RowData rowType="label" value="Income" />
                </TextRow>
                <DataRow rowNumber="3">
                    <RowData rowType="account" value="Construction" />
                    <ColData colID="1" value="Construction" />
                </DataRow>
                <DataRow rowNumber="4">
                    <RowData rowType="account" value="Construction:Labor" />
                    <ColData colID="1" value="Labor" />
                    <ColData colID="2" value="12226.00" />
                </DataRow>
                <DataRow rowNumber="5">
                    <RowData rowType="account" value="Construction:Materials"/>
                    <ColData colID="1" value="Materials" />
                    <ColData colID="2" value="27346.60" />
                </DataRow>
                <DataRow rowNumber="6">
                    <RowData rowType="account" value="Construction:Miscellaneous"/>
                    <ColData colID="1" value="Miscellaneous" />
                    <ColData colID="2" value="2232.03" />
                </DataRow>
                <DataRow rowNumber="7">
                    <RowData rowType="account" value="Construction:Subcontractors"/>
                    <ColData colID="1" value="Subcontractors" />
                    <ColData colID="2" value="16305.76" />

                     ...

            </ReportData>
        </ReportRet>
    </GeneralSummaryReportQueryRs>
</QBXMLMsgsRs>

And the displayed report:

Report datatypes

A column description classifies the data in the column in two different ways:

                   <ColDesc colID="2" dataType="AMTTYPE">
                         <ColTitle titleRow="1" value="Dec 1 - 15, 03" />
                         <ColType>ctAmount</ColType>
                   </ColDesc>

The datatype attribute specifies the data as a formatted qbXML data type. A value of type Percent, for example, would have two digits after the decimal point: 7.90. A value of type Duration that is shown in QuickBooks as “hr:min” (for example “8:20”) would be returned in qbXML as “PT8H20M.” The datatype attribute is generally more reliable than the column type in determining how the data is formatted.
The ColType element also describes the data in the column, but in a more functional way, describing the business meaning of the data.

Both of these values are enumerated types. For a complete list of the column types and their corresponding datatypes, see Report datatype reference.

The column’s dataType value may be overridden on a cell-by-cell basis, but this is not common. In such cases, the datatype of the particular cell is specified with the ColData.element’s dataType attribute. In the following excerpt, for example, the datatype in the column descriptor is AMTTYPE, but the datatype for column 9 is QUANTYPE.

<ColDesc colID="9" dataType="AMTTYPE">
    <ColTitle titleRow="1" value="Sales Price" />
    <ColType>UnitPrice</ColType>
</ColDesc>
...
<ColData colID="9" value="57.75111" dataType="QUANTYPE" />

Enumerated values for the “ClearedStatus” column

Some columns, for example the “Clr” column in a transaction detail report, use symbols to indicate whether an item has cleared or not. The following enumerated values are used in the SDK in place of the symbols that appear in the QuickBooks user interface:

Symbol	Enumerated Value
* (asterisk)	Pending
✓ (checkmark)	Cleared
⚡ (lightning bolt)	Not cleared

Transaction Detail Reports

In a transaction detail report, each column has a different label—for example, TxnNumber, PONumber, DeliveryDate, Quantity, SalesPrice, Clear. The following qbXML example shows an Accounts Receivable Aging Detail report. The displayed report, as it appears in QuickBooks, is shown below the qbXML.

<?xml version="1.0" ?>
<QBXML>
   <QBXMLMsgsRs>
      <AgingReportQueryRs statusCode="0" statusSeverity="Info" statusMessage="Status OK">
         <ReportRet>
            <ReportTitle>A/R Aging Detail</ReportTitle>
            <ReportSubtitle>As of December 15, 2022</ReportSubtitle>
            <ReportBasis>Accrual</ReportBasis>
            <NumRows>12</NumRows>
            <NumColumns>11</NumColumns>
            <NumColTitleRows>1</NumColTitleRows>
            <ColDesc colID="1" dataType="STRTYPE">
               <ColTitle titleRow="1" />
               <ColType>Blank</ColType>
            </ColDesc>
            <ColDesc colID="2" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Type" />
               <ColType>TxnType</ColType>
            </ColDesc>
            <ColDesc colID="3" dataType="DATETYPE">
               <ColTitle titleRow="1" value="Date" />
               <ColType>Date</ColType>
            </ColDesc>
            <ColDesc colID="4" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Num" />
               <ColType>RefNumber</ColType>
            </ColDesc>
            <ColDesc colID="5" dataType="STRTYPE">
               <ColTitle titleRow="1" value="P. O. #" />
               <ColType>PONumber</ColType>
            </ColDesc>
            <ColDesc colID="6" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Name" />
               <ColType>Name</ColType>
            </ColDesc>
            <ColDesc colID="7" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Terms" />
               <ColType>Terms</ColType>
            </ColDesc>
            <ColDesc colID="8" dataType="DATETYPE">
               <ColTitle titleRow="1" value="Due Date" />
               <ColType>DueDate</ColType>
            </ColDesc>
            <ColDesc colID="9" dataType="STRTYPE">
               <ColTitle titleRow="1" value="Class" />
               <ColType>Class</ColType>
            </ColDesc>
            <ColDesc colID="10" dataType="INTTYPE">
               <ColTitle titleRow="1" value="Aging" />
               <ColType>Aging</ColType>
            </ColDesc>
            <ColDesc colID="11" dataType="AMTTYPE">
               <ColTitle titleRow="1" value="Open Balance" />
               <ColType>OpenBalance</ColType>
            </ColDesc>
            <ReportData>
               <TextRow rowNumber="1" value="Current" />
               <DataRow rowNumber="2">
                  <ColData colID="1" value="Total Current" />
               </DataRow>
               <TextRow rowNumber="3" value="1 - 30" />
               <DataRow rowNumber="4">
                  <ColData colID="2" value="Invoice" />
                  <ColData colID="3" value="2021-12-05" />
                  <ColData colID="4" value="980322" />
                  <ColData colID="6" value="Molotsi, Hugh:Second Story Addition" />
                  <ColData colID="7" value="Due on receipt" />
                  <ColData colID="8" value="2022-12-05" />
                  <ColData colID="10" value="10" />
                  <ColData colID="11" value="13500.00" />
               </DataRow>
               <SubtotalRow rowNumber="5">
                  <ColData colID="1" value="Total 1 - 30" />
                  <ColData colID="11" value="13500.00" />
               </SubtotalRow>
               <TextRow rowNumber="6" value="31 - 60" />
               <DataRow rowNumber="7">
                  <ColData colID="1" value="Total 31 - 60" />
               </DataRow>
               <TextRow rowNumber="8" value="61 - 90" />
               <DataRow rowNumber="9">
                  <ColData colID="1" value="Total 61 - 90" />
               </DataRow>
               <TextRow rowNumber="10" value="&gt; 90" />
               <DataRow rowNumber="11">
                  <ColData colID="1" value="Total &gt; 90" />
               </DataRow>
               <TotalRow rowNumber="12">
                  <ColData colID="1" value="TOTAL" />
                  <ColData colID="11" value="13500.00" />
               </TotalRow>
            </ReportData>
         </ReportRet>
      </AgingReportQueryRs>
   </QBXMLMsgsRs>
</QBXML>

The displayed report:

images/accounts_receivable_aging_detail.png

The order column

The Order column that appears on some reports in the QuickBooks UI includes a checkmark to indicate when a particular item should be reordered. In reports responses generated by the SDK, this information is communicated by the Suggested Reorder field. This is a boolean value, and true is the equivalent of the checkmark in the QuickBooks UI.

Requesting reports with personal data

An application must have authorization to access fields that contain personal data such as SSNOrTaxID. If your application requests a report that contains personal data, QuickBooks checks for the required authorization before it returns the report with personal data. If the application does not have the appropriate authorization, QuickBooks returns the following status message:

status code 3261: The integrated application has no permission to access personal data.

Requesting reports with payroll data

The following payroll data items are restricted:

IncomeSubjectToTax
PayrollItemDetail
WageBase
WageBaseTips

In order for them to be included in a payroll report requested via the Desktop SDK:

The QuickBooks administrator must have authorized the application to access personal data.
The application must be accessing a company file that is currently signed up for a subscription to a payroll service

If an application that does not have authorization sends a report query that would include any of these items of payroll data, QuickBooks will return the following message:

status code 3262: In order to complete this request, the company data file has to be subscribed to the Intuit Payroll Service.

It is important to note that if an application requests a detail report and the request includes a value for IncludeColumn that specifies one or more of these payroll items and the application is not authorized to access that information, the entire request fails.

A report response has no data

Report requests apply to a category of report, and each category includes several QuickBooks reports. This means it is possible for a request message to include attributes, including filters, that do not apply to every report in its group. Because of this, it is possible to create a request message that is programmatically correct (that is, it is valid qbXML or compilable QBFC code) but that asks for fields that are invalid for the particular report that is being requested. In these cases, no data is returned.

As you prepare a report request, check the QuickBooks user interface to determine whether the relevant fields are actually included in the report you’re interested in. Also, check that the SDK supports all the fields you’re requesting–see Report request reference.

When an application requests a field that is not supported by the requested report, QuickBooks returns status code 3151 and the following status message:

Cannot use the element XXX in this request.

This message would be generated, for example, if you requested a Physical Inventory Worksheet along with an entity filter, because the entity filter is not used by the4 Physical Inventory Worksheet.

When a field is supported, but a particular value for that field is not supported, QuickBooks returns status code 3152, and the following status message:

The enumerated value XXX may not be used in the element YYY in this request.

For example, in a PurchaseByVendorSummary report, the element SummarizeColumnsBy is supported, but the value Vendor is not supported, so that combination would result in this error.