I am trying to generate a report in Excel pulling data via ODBC from a Quickbooks file. All I want to do is subtract (2) source columns and show the difference in a column in the middle of my Pivot Report.
I haven't worked with connections to Quickbooks, but you should be able to create a calculated field.
It's not clear what you mean by "the middle of my Pivot Report"....Calculated fields need to be placed in the Data (or Values) area the Pivot Report, but there is no restriction on the position you place the Calculated Field within that area.
There are some scenarios in which Calculated Fields cannot be used, such as when also using Grouping.
Have you used Calculated Fields before with PivotTables that reference simple workbook ranges?
If so, what happens when you try to create a Calculated Field using the same process?
If you need some more help, please identify what version of Excel you are using.
I want the report to have columns displayed in the following order:
Customer, Item, Quantity, Unit Amount, Total Amount
Quickbooks fails to provide a field for the open quantity remaining on an open order. Instead, I have to calculate by subtracting the order's original order quantity was minus the quantity invoiced. So Quantity is my calculated field; but I can't figure out how to add this column into the middle of my Pivot Table. I have tried adding a formula column to the Excel table that my Pivot Report pulls from; but on refresh, the formula does not carry over to the newly added cells.
Here are the steps you would typically take to add a calculated field. It assumes that your existing pivot table field list already contains two fields:
"Original Order Quantity" and "Quantity Invoiced". (modify the instructions to match your field names).
Click on one of your header caption cells in the PivotTable (like Unit Amount).
In the Ribbon > Options > Fields Items & Sets > Calculated Field...
In the Insert Calculated Field Dialog Box:
Formula: ='Original Order Quantity'- 'Quantity Invoiced'
(use your actual field names. It's easier and more accurate to pick them from the fields off the list instead of typing them).
Click the Add button
Click the OK button
This should place the Quantity field in the PivotTable. You can change its position in the PivotTable Field List pane.
Well, the steps that you gave me, still wouldn't let me arrange the columns the way I wanted to; however, it did occur to me to move all of my number fields to the values section of the table so that I could arrange everything in that section. And it totally works!
So I have learned. I just assumed wherever that the values area was only for custom fields. \
My next frustration is that, upon refresh, some of my data lose their alignment and I have to re-align and reassign currency symbols to the currency fields. I have a macro set up to do this; but didn't know if there was an easier step that I might be missing?
Apply the number formatting through the value field settings instead of applying it to the actual cells of the PivotTable.
To do that, Right-click on the field header in the PivotTable > Value Field Settings....
In the dialog that pops up, there should be "Number Format" button at the bottom next to OK and Cancel.
Use that to set the number format.
If you don't see a "Number Format" button, it means Excel doesn't consider the field numeric.
This can happen if you have a mix of numbers, text and blanks in your data field.