Share this content

Sales/Tax by shipping state in Excel – The power of pivot tables, Part 2

Apr 13th 2010
Share this content

By Chuck Vigeant

This is the second of two articles where we explore the ability to generate a Sales by Shipping State report in Excel. In the first article we created a simple example using only invoices, and showed you the mechanics of using the subtotal feature in Excel 2007 to calculate, both, total sales and sales tax by invoice.

In this article we will create a complete report that will give you the following: total sales, total taxable sales, total non-taxable sales, and total sales – all by shipping state. Also included in this example are filters for customer, customer taxable code, items, dates, etc. (For this example, we will not include total tax charged or collected.)

The steps are organized as follows:

  1. Give you a complete SQL statement that you can copy and paste into Excel – without having to understand a thing about tables, relationships, etc. I do all that work for you.
  2. Create a pivot table to get your report
  3. Demonstrate pivot table options to show you how to achieve several different views using the same query and data

Welcome to the power of pivot tables!


Connecting Excel to QuickBooks

From within Excel, invoke the Microsoft Query Wizard:

Choose the QuickBooks Data Source:

Make sure that the Query Wizard is off, as we will use a manual SQL query that I have generated for you.

You will see a list of tables. Click the Close Button to remove the dialog box.

From the Menu, click 'View' then choose 'SQL'

You will see the following window:

This is where you will paste in the SQL statement I have created for you.

Just cut and paste this whole thing into the SQL Statement Dialog Box:

SELECT "InvoiceLine"."CustomerRefFullName" AS CustomerRefFullName,

"InvoiceLine"."CustomerSalesTaxCodeRefFullName" AS CustomerTaxable,

"InvoiceLine"."TxnDate" As Date,

"InvoiceLine"."RefNumber" AS RefNumber,

"InvoiceLine"."ShipAddressState" AS ShippingState,

"InvoiceLine"."BillAddressState" AS BillingState,

{fn IFNULL("InvoiceLine"."ShipAddressState"",InvoiceLine"."BillAddressState")} AS State,

"Item"."FullName" As ItemName,

"Item"."Type" AS ItemType,

"InvoiceLine"."InvoiceLineSalesTaxCodeRefFullName" As ItemTaxable,

"InvoiceLine"."InvoiceLineAmount" As Amount,

'Invoice' AS Type

 FROM  "InvoiceLine" "InvoiceLine"",Item" "Item"

 WHERE "InvoiceLine"."InvoiceLineItemRefListID"="Item"."ListID" AND "InvoiceLine"."IsPending" = 0

 AND ("Item"."Type"='ItemDiscount' OR "Item"."Type"='ItemFixedAsset' OR "Item"."Type"='ItemInventory' OR "Item"."Type"='ItemInventoryAssembly' OR "Item"."Type"='ItemNonInventory' OR "Item"."Type"='ItemOtherCharge' OR "Item"."Type"='ItemService')

AND "InvoiceLine"."TxnDate" >= {d'2011-12-01'} AND "InvoiceLine"."TxnDate" <= {d'2011-12-15'}


 SELECT "SalesReceiptLine"."CustomerRefFullName" AS CustomerRefFullName,

"SalesReceiptLine"."CustomerSalesTaxCodeRefFullName" AS CustomerTaxable,

"SalesReceiptLine"."TxnDate" As Date,

"SalesReceiptLine"."RefNumber" AS RefNumber,

"SalesReceiptLine"."ShipAddressState" AS ShippingState,

"SalesReceiptLine"."BillAddressState" AS BillingState,

{fn IFNULL("SalesReceiptLine"."ShipAddressState"",SalesReceiptLine"."BillAddressState")} AS State,

"Item"."FullName" As ItemName,

"Item"."Type" AS ItemType,

"SalesReceiptLine"."SalesReceiptLineSalesTaxCodeRefFullName" As ItemTaxable,

"SalesReceiptLine"."SalesReceiptLineAmount" As Amount,

'SalesReceipt' AS Type

 FROM  "SalesReceiptLine" "SalesReceiptLine"",Item" "Item"

 WHERE "SalesReceiptLine"."SalesReceiptLineItemRefListID"="Item"."ListID" AND "SalesReceiptLine"."IsPending" = 0

 AND ("Item"."Type"='ItemDiscount' OR "Item"."Type"='ItemFixedAsset' OR "Item"."Type"='ItemInventory' OR "Item"."Type"='ItemInventoryAssembly' OR "Item"."Type"='ItemNonInventory' OR "Item"."Type"='ItemOtherCharge' OR "Item"."Type"='ItemService')

AND "SalesReceiptLine"."TxnDate" >= {d'2011-12-01'} AND "SalesReceiptLine"."TxnDate" <= {d'2011-12-15'}


 SELECT "CreditMemoLine"."CustomerRefFullName" AS CustomerRefFullName,

"CreditMemoLine"."CustomerSalesTaxCodeRefFullName" AS CustomerTaxable,

"CreditMemoLine"."TxnDate" As Date,

"CreditMemoLine"."RefNumber" AS RefNumber,

"CreditMemoLine"."ShipAddressState" AS ShippingState,

"CreditMemoLine"."BillAddressState" AS BillingState,

{fn IFNULL("CreditMemoLine"."ShipAddressState"",CreditMemoLine"."BillAddressState")} AS State,

"Item"."FullName" As ItemName,

"Item"."Type" AS ItemType,

"CreditMemoLine"."CreditMemoLineSalesTaxCodeRefFullName" As ItemTaxable,

("CreditMemoLine"."CreditMemoLineAmount")*-1 As Amount,

'Invoice' AS Type

 FROM  "CreditMemoLine" "CreditMemoLine"",Item" "Item"

 WHERE "CreditMemoLine"."CreditMemoLineItemRefListID"="Item"."ListID" AND "CreditMemoLine"."IsPending" = 0

 AND ("Item"."Type"='ItemDiscount' OR "Item"."Type"='ItemFixedAsset' OR "Item"."Type"='ItemInventory' OR "Item"."Type"='ItemInventoryAssembly' OR "Item"."Type"='ItemNonInventory' OR "Item"."Type"='ItemOtherCharge' OR "Item"."Type"='ItemService')

AND "CreditMemoLine"."TxnDate" >= {d'2011-12-01'} AND "CreditmemoLine"."TxnDate" <= {d'2011-12-15'}

(Notice that I have bolded the places where you can change the dates; and yes you must change it in all three places.)

Your box will look something like this:

Before we hit the OK button I am going to explain – in general terms what this Query does:

  • Combines the sales item transaction tables – Invoice, Sales Receipt, Credit Memo, Statement Charge as they were one transaction (This does not include journal entries.)
  • Included only sales items (e.g. omitted subtotal items and tax items and groups)
  • Created a condition, that if the shipping state is missing, we will use the billing state in its place
  • Omitted Pending Transactions
  • Allowed the ability to change the dates to limit the range of data you want to work with

For those people who are advanced database users or programmers, I have optimized this query to work with the QODBC driver; I could have used a much shorter statement using Oracle or Sybase. Besides, the important part of this exercise is to give practitioners a fairly simple way of (a) grabbing the information from QuickBooks, (b) manipulating it using pivot tables, and (c) refreshing the data without ever having to export, cut, and paste from QuickBooks.


Running the query

OK, let's run the query. Depending upon the file size, it could take anywhere from a couple of minutes to an hour. If your driver is setup properly, the first time you run the report it will take by far the longest time; each subsequent use should produce fairly quick results. (Setting up the QODBC driver is beyond the scope of this article, but the default settings should work just fine in most cases.)

The following message will appear. Click the OK button to continue.

Again, after a period of time, you will see results similar to the following:

From the Menu choose the 'File' option, and then choose 'Return Data to Microsoft Office Excel'.

On this dialogue box, choose 'PivotTable Report', and a cell/worksheet to place your data. Then click OK.

The query will go back to QuickBooks and retrieve the data, and then produce a worksheet as follows:

Now the magic begins. You simply drag and drop the fields you want into the boxes in the lower right-hand corner, and the data will instantaneously appear in the body of your spreadsheet.

In the following example, we will show sales by shipping state across the columns, and represent taxable vs. non-taxable sales in the rows.

NOTE: Remember that Discount items do have a tax code associated with them.

Next, let's add a filter. In this example we will filter by customers who are exempt. Just drag 'CustomerTaxable’ into the Report Filter Box.

Notice that Excel has placed a filter area for us at the top of the worksheet. Now all we have to do is filter by 'Non' to get the customers who were classified as non-taxable.

To get the following results:

Furthermore, we can find out who the nontaxable customer is. Let's add Customer to the rows.

We can see that Diarmuld Roche was classified as non-taxable. And if we wanted to find out what items fell under the 'Blank' designation, we can add items to rows as follows:

We see that finance charges had no taxable designation (In QuickBooks, sales tax is not added when entering transactions directly into the Customer or A/R register).


Data is always live

The last part of this exercise is to note that you can save the report and refresh the data into your spreadsheet at any time. Even when the spreadsheet is open – and you make a change in QB – the refresh operation will update your spreadsheet data in real time

To refresh your data you can do two things:

  • From anywhere within the data area, right click and choose 'Refresh'


  • On the 'Data' Tab select the 'Refresh All' Icon, and choose 'Refresh'. ('Refresh All' would refresh multiple queries within a single workbook).




In this exercise, we endeavored to give you several takeaways:

  • Pivot tables are easy to use, utilizing drag-and-drop technology
  • Using the QODBC driver for QuickBooks, you can retrieve live data and manipulate it in the way you see fit.
  • Pivot tables are dynamic, flexible, and very powerful – with a minimum of learning curve.

About the author:

Chuck Vigeant M.Ed., founder of ACCOUNTiGRATE, LP, is considered by many to be the foremost authority on QuickBooks integration, reporting, and Business Intelligence, and is the father of custom QuickBooks reporting using Crystal Reports and the QODBC driver now included in QuickBooks Enterprise Solutions. Vigeant also is a consultant for The Sleeter Group.

Related articles: