6 Steps to Building the Reports You Need in QuickBooks
If you are an experienced QuickBooks® Desktop (Pro, Premier, Accountant or Enterprise) reports customizer, you know how great QuickBooks is for building custom reports on the fly. However, there are some really important restrictions to know about, including what I think is most prevailing: QuickBooks can only build a report using data from two types of tables.
The two tables include:
- Transactions: These are the fields/data points that live inside each transaction, such as Invoices, Bills and Checks.
- Names: These are the tables that store Customer, Vendor, Item and Account Information.
So, the issue is that you cannot pull up a report, such as “Sales by Preferred Vendor,” using QuickBooks custom reporting functions. That’s because Preferred Vendor is a data field that lives in the Item List table and Sales is going to be part of the Transactions Table (such as invoices and sales receipts). Therefore, you would have to pull two different reports and then use some sort of VLOOKUP formula to put it together … I know some of you Excel gurus have probably figured out how to do this. However, this is not a sustainable process because it will likely require the use of a consultant every time the report needs to be built.
If you have QuickBooks Enterprise 14 and above, along with an active annual subscription, you can use the new Advanced Reporting add-on called QBAR (included with your subscription) to create this report.
There are quite a few videos in the QuickBooks Enterprise Learning Center on QBAR. I interviewed Joseph Lasee, senior product manager for QBAR, asking him how to create a report like this step by step, and to shed some light into how this compared with QuickBooks’ standard reporting capabilities. Watch the video here, or click on the box below.
In Summary, the best way to build the report is to follow these six steps:
1. Go to the Reports menu and click on Advanced Reporting to run QBAR. You must be in multi-user mode and have user permissions to run QBAR if you are not the Admin.
2. Open a Starter Report, such as Sales by Item Detail. It should look like this:
3. Right-click on the light gray bar on top of the “chart” (in QBAR, any table with data, whether it’s graphical or not, is called a Chart), and click on properties.
4. First, we are going to ADD the preferred vendor from the Item table by clicking on the Dimensions Tab. It should look like this:
The next step here is to look for the Item table in the drop-down, where it says Show Fields from Table. Then, in the Available Fields/Groups above, look for Item.Preferred.Vendor.Full.Name and click on Add. After you see it inside the Used Dimensions box, click on it and promote it all the way to the top. This means that this will be the principal row (or pivoting row) that will organize the information – this is the “By Preferred Vendor” portion of the report that we are building. It should look like this now:
Optionally, you can click on Suppress When Value is Null if you do not want to see any numbers for items that do not have a preferred vendor.
5. Next, in the Expression tab, we are going to click on each expression and uncheck the Enable checkbox to the right, with the exception of Amount. We cannot disable amount because we need at least one expression of transaction data to show on the report (Of course, you are welcome to keep QTY and SALES PRICE enabled, as they could also be useful in this type of report.). That should look like this:
6. And, the final setting is Presentation. We are going to tell QBAR that we do not want to see any detailed transaction information – just collapsed totals. In order to do that, we have to select the second dimension Item Type and uncheck Always Show Fully Expanded. It will look like this:
When we are done, we can click on Apply and OK, and the report will look like this:
You may need to right click on the title and click on properties to change the title to the desired name, like this:
This is just one example of the great potential that QBAR has for building those very specific reports your clients may need in order to make faster and better decisions. There are currently a limited amount of learning resources on QBAR, but I have dedicated a page on my site to aggregating all the resources I know on Advanced Reporting for QuickBooks Enterprise.
I will also keep updating the page as more and more resources become available!
Now, although I am writing this article, I will confess that I am not an expert in this tool by any means. I had to spend a few hours to get familiar with it and my interview with Joseph was very helpful. So, I made it my goal this year to become more and more confortable with this tool so that I can add a whole new dimension (no pun intended) of service to my current clients and potential new ones. The fact that this is not an easy tool to get started with, and that there isn’t a whole lot of consultants that have mastered this tool, presents a great opportunity for QuickBooks ProAdvisors!® Although the learning curve is long and there are many hours of trail-and-error experimentation until you build the comfort level to go out and offer Advanced Reporting customization services, I guarantee the rewards will be worth it.