How to Modify a Report in QuickBooks Advanced Reporting

by

As I discussed in my last article Intuit has come out with a new reporting tool they call QuickBooks Advanced Reporting, or QBAR.  One quick note I would like to make is that QBAR is not just for QBES v15 users but also for QBES v14 R6 and newer users.

One of the big selling points for QBAR is that virtually all of the fields in QuickBooks are open to all reports, you are not just limited to the fields that Intuit thinks you might want to use on a specific report.  We recently ran into a perfect example of this and I’m going to cover that scenario and how to perform the changes in the report.

I was recently contacted by a fellow ProAdvisor asking a couple of custom reporting questions.  The request was for a Inventory report that displayed Item Name, Description, Qty on Hand, Qty on SO, Available Qty and Price.  The Stock Status by Item report got her very close, but the option to add the Price wasn’t there.  A month or so ago my go to answer would have been let’s use Custom Advanced Reporting, or CAR, to solve this problem. However, this is exactly what the goal was for QBAR, so I thought let’s give it a shot and see how easy it is.  My reply to the ProAdvisor was that I thought we could do this in QBAR and would they mind if we gave it a shot?  They didn’t so we proceeded and it found that it only took me 10 to 15 minutes with just the little experience I previously had in the new tool.

Here is how I created the report.  I opened QBAR (Reports > Advanced Reporting) once the list of starter reports open I selected the Inventory Stock Status by Item Detail report.  Much like the report found in QuickBooks there are fields we don’t want on the report but more importantly it was missing a critical field, the Price. 

The first thing I wanted to do was get rid of fields we didn’t want.  To do this I Right-Clicked on the darker gray header of the grid displaying the item info and then Clicked on Properties… (shown below). 

This will open the Chart Properties window (yes the grid is really a chart, we won’t go into that now), we want to be on the Expressions tab and we are going to focus on the box in the top left of this tab (see below). 

What you will see in this box is a list of fields on the report, we are going to remove the ones that aren’t wanted on this report.  To remove the field single-click on the field name, for example Pref Vendor, and then click the Delete button under the box.  Continue this process until you have removed all the fields you don’t want on your report.  I got my list down to just On Hand, On Sales Order and Available.  When I click OK my report updates and I’m closer to the report I am looking for (shown below),

but I’m still missing a couple of things.  I don’t have the Item Description or the Item Price. 

For those that are familiar with Excel you may notice this report looks like a Pivot Table.  The columns that have a bold font title are Dimensions in QBAR and the normal font titles are Expressions.  To add the Item Description we are going to need to add a new Dimension and the Price will be an Expression.  While it would be much easier to demonstrate how to do this in a webinar or personal training class let’s see what I can do to show you how to do this here.

The first step is to go back into the Properties of the grid (refer back to Image 1), just as we did to remove the unwanted fields.  This time instead of clicking Delete we are going to click Add.  This is going to open the Edit Expression window.  At the bottom of the window there are several tools available to us but for the purposes of this report we won’t be using many of them.  What we need to do is find the Price field since we aren’t completely sure what the field name is or what table it is stored in.  Our first step is to go down and see if we can figure out what table we might need.  I know the Price is associated with the Item and I see there is an Item table so I’m going to start there, select Item.  Now let’s see if we can find the field we need.  I know I’m looking for Price but I don’t see a field named Price; however, Price is often referred to as Sale Price so I scroll to the S section of the list find Sale Price and select it.  Next I click the Paste button and the program will put this value in my Expression box (see the image below). 

At this point I could click OK and move on but in my learning I have noticed that this isn’t how Intuit did the other fields.  For example the Expression for the Quantity on Hand is this:

We won’t get into the details on this at this point but I thought that if Intuit did it then maybe I should try it on the new field I’m adding. I made my Expression match but I replaced the ItemHistory.QuantityOnHand with Item.SalesPrice and I clicked OK.  Back in my Chart Properties window I can now see my Price added but instead of showing the word “Price” it shows the Expression.  If you go over to the right side of the window and enter a value into the Label field you can correct this.  So I entered “Price” into this field (see the image below). 

Now after we click on we can see that our Price has been added to the grid.

Now let’s add the Item Description.  We start by going back into the Properties window and this time we go to the Dimensions tab.  In the top left box is a list of every field we can select from.  To make the list a little more manageable let’s go to the Show Fields from Table dropdown list and select Item, since we know the Description is stored in with the Item.  Now scroll through the list and find the Description.  Just like with the price, there is no field called Description but we know there is a Purchase Description and a Sales Description in QuickBooks. We will want the Sales Description so we scroll down to the S section to find SalesDesc (shown below),

click on the Add button to include this into our Used Dimensions list.  You can also go down to the Label field and call it whatever you’d like, I entered “Description”.  When we click OK we are taken back to our report and we now see the Description on our report.

We’re Done!  (See the finished report below)

It was as easy as that.  This was a simple and quick modification but I hope you can begin to see how the options are endless with this tool.  I followed this report up with another report that the user wanted removing the Qty on Hand and Qty on SO, giving them a list they could share with their customers.  I went in and removed those two Expressions and I was done.  All the user has to do is click the little “XL” text in the top corner of the grid and the report results will open in Excel where they can then save the spreadsheet and send it off to the customer.

QBAR is more advanced than just adding and removing fields in QuickBooks and therefore will require a bit more learning to really take full advantage of the program.  It is more like working in Excel with a different interface.  One nice thing in QBAR is we can create fields that are formulas, for example the Available field is not a field directly out of the QuickBooks database it is an Expression that looks like this:

This is not something we can do inside QuickBooks but now we have the tools to create these formulas and save them in our report so they are there for the next time we need to run it.  As you can now begin to see, there is indeed a lot of potential with this tool and we have just started tapping into it!