NOTE: The source code has been modified slightly from the original post as we are continuously updating/enhancing our interfaces with QuickBooks Online APIs.

When previously using QuickBooks Desktop (QBD) getting sales by state was fairly easy to do.  Export the data to Excel, sort, format and total.

Now with QuickBooks Online (QBO) things aren't that easy, especially if you are using a lower tiered account that doesn't provide all of the reports available.  And even then I haven't seen very good results from the reports.

Well, I just happen to have set up a developer account for QuickBooks in order to get access to the QBO APIs and my own personal data.  And I was able to create a set of functions (right now called GreenBooks, a GreenTools for QuickBooks Online application) that I was able to query my own data to build a report.

How?  First we look through all of the payments for the year 2023 (we use Cash basis):

paymentCount = 1; //set to 1 so that we loop at least once
position = 1;

dow (paymentCount > 0);
  sqlString = 'select * from Payment where TxnDate >= ''2023-01-01''' +
              ' and TxnDate <= ''2023-12-31''' +
              ' ORDER BY Id' + 
              ' STARTPOSITION ' + %char(position) +
              ' MAXRESULTS ' + %char(MAX_records);

  gbooks_resetValues();
  gbooks_setValue('sqlStatement':sqlString);
  gbooks_setValue('dataType':'Payment');
  gbooks_setValue('saveFile':'*YES');
  paymentCount = gbooks_query(paymentID:paymentFile:errorMsg);

  if (paymentCount < 0);
    //error
  else;
    exsr $Payment;
    position += paymentCount;
    gbooks_setValue('file':paymentFile);
    gbooks_cleanup();
  endif;

enddo;

This first piece of code creates an SQL query to run to select all the payment records for 2023 (since I do business on a cash basis).  In the background gbooks_query is calling an online API provided by QuickBooks.

What this does is return a JSON file with the payment records.  Because 100 records is the max that can be returned by the API, we put this is loop and paginate through the data 100 records at a time.

For each payment we execute the $Payment subroutine.

//***************************************************************
//* Get Payment Data
//***************************************************************
begsr $Payment;

  clear Payment_Out;

  monitor;
    data-into Payment_Out %DATA(%trim(paymentFile):
              'doc=file case=any countprefix=num_ allowextra=yes allowmissing=yes')
               %PARSER('YAJLINTO');
  on-error;
    errorMsg = 'Error parsing payment file ' + %trim(paymentFile) + '. See job log.';
    exsr $return;
  endmon;

  for p = 1 to Payment_Out.QueryResponse.num_Payment;
    PAYID = Payment_Out.QueryResponse.Payment(p).Id;
    CSTID = Payment_Out.QueryResponse.Payment(p).CustomerRef.value;
    INVID = ' ';
    INVNUM = ' ';
    AMTPAID = Payment_Out.QueryResponse.Payment(p).TotalAmt;
    PAYDATE = %date(Payment_Out.QueryResponse.Payment(p).TxnDate:*ISO-);
    BTSTATE = ' ';
    STSTATE = ' ';
    COUNTRY = ' ';

    exsr $Customer;

    for pl = 1 to Payment_Out.QueryResponse.Payment(p).num_Line;

      if (Payment_Out.QueryResponse.Payment(p).Line(pl).LinkedTxn(1).TxnType = 'Invoice');
        INVID = Payment_Out.QueryResponse.Payment(p).Line(pl).LinkedTxn(1).TxnID;
        exsr $Invoice;
      endif;

    endfor;

    if (STSTATE = ' ') and (BTSTATE <> ' ');
      STSTATE = BTSTATE;
    endif;

    exec sql
      insert into SALESPF (
        PAYID,   
        CSTID,   
        INVID,   
        INVNUM,  
        AMTPAID, 
        PAYDATE, 
        BTSTATE, 
        STSTATE,
        COUNTRY)
      values(
        :PAYID,   
        :CSTID,   
        :INVID,   
        :INVNUM,  
        :AMTPAID, 
        :PAYDATE, 
        :BTSTATE, 
        :STSTATE,
        :COUNTRY);

  endfor;

endsr;

In the $Payment subroutine we first use the DATA-INTO function and the YAJL parser to place the data into a data structure (also created using YAJL) and iterate through each payment record. 

Now, I know that people say to use the allowextra and allowmissing options aren't the best, but in this case the JSON returned could be very different each time (don't ask me, I didn't write the APIs at QuickBooks) including some data, using different tags for different data (for example Bill To and Ship To address information) so in this case, unless I misunderstand what these options are used for, I sort of need them.  

Next we grab the customer data (mainly for the Bill To State).  We then process the invoices attached to the specific payment and look for a Ship To State.  Once we write that data we write it all to a SALESPF file.

The $Customer routine looks like this:

//***************************************************************
//* Get Customer Data
//***************************************************************
begsr $Customer;
  
  clear thisCustomerDS;

  gbooks_resetValues();
  gbooks_setValue('customerID':CSTID);
  rc = gbooks_readCustomer(thisCustomerDS:errorMsg);

  if (rc >= 0);
    BTSTATE = thisCustomerDS.Customer.BillAddr.CountrySubDivisionCode;
    COUNTRY = thisCustomerDS.Customer.BillAddr.Country;
  endif;


endsr;

The gbooks_readCustomer() function will read the customer info for CSTID and place it in thisCustomerDS data structure.  We then can get the Bill To State and Country for the customer.

The $Invoice subroutine which is called for each invoice that the payment is applied to is as follows:

//***************************************************************
//* Get Invoice Data
//***************************************************************
begsr $Invoice;

  clear thisInvoiceDS;

  gbooks_resetValues();
  gbooks_setValue('invoiceID':INVID);
  rc = gbooks_readInvoice(thisInvoiceDS:errorMsg);

  if (rc >= 0);
    INVNUM = thisInvoiceDS.Invoice.DocNumber;
    STSTATE = thisInvoiceDS.Invoice.ShipAddr.CountrySubDivisionCode;
  endif;
  
endsr;

Again, we use the invoice ID (INVID) and call the gbooks_readInvoice() function to return the invoice data to thisInvoiceDS data structure.  With this data we can retrieve the invoice number and the Ship To State (if it exists).

Both of the gbooks_read(Customer/Invoice) functions in the background call the APIs provided by QuickBooks using GETURI and then parse the JSON using DATA-INTO and YAJL.  Then we place the data in the data structure and it is returned to the calling program.

There are also functions available that you can return the JSON data instead of a data structure.  These functions are actually called as part of these read functions. 

Once we have the data it's simple enough to query the data, sort and subtotal by state and produce a report that so far has made my accountant happy.  I even ran tests for 2021 and 2022 to match the data to what I had sent in before using the data from QBD and they matched.

Now, QuickBooks and Intuit have some pretty strict rules for using their APIs, and that is totally understandable.  So, if you are looking for assistance with this type of project it would entail creating your own account, answering a questionnaire and getting set up and qualified to be able to use the Live endpoints and data for your specific company, all of what we are happy to help with.

As always, free free to contact us with any questions.

In QuickBooks Online, to generate a Sales by State report:

1. Navigate to the "Reports" tab.
2. Search for "Sales by State" or "Sales by Location."
3. Customize the report by selecting the date range and filters.
4. Click "Run Report" to generate and review state-wise sales figures.
5. Optionally, export or save the report in different formats.
 

for NetSuite ERP Services Visit at Ledgerlabs

Those options don't exist.  That's why I did the custom reporting.  It was fun.