Reports In QuickBooks

Published on 23 December 2020 at 07:25

6 Steps to Building the Reports you want in QuickBooks

If you are an experienced QuickBooks® Desktop (Pro, Premier, Accountant or Enterprise) reports customizer, you understand how great QuickBooks is for building custom reports from the fly. However, there are some really important restrictions to learn about, including the things I think is most prevailing: QuickBooks can simply build a written report using data from 2 kinds of tables.

For More info visit:

The 2 tables include:

Transactions: they are the fields/data points that live inside each transaction, such as Invoices, Bills and Checks.
Names: they are the tables that store Customer, Vendor, Item and username and passwords.
So, the problem is which you cannot pull up a written report, such as for example “Sales by Preferred Vendor,” using QuickBooks custom reporting functions. That’s because Preferred Vendor is a data field that lives within the Item List table and Sales will likely 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 in all probability figured out how to do that. However, this is simply not a sustainable process since it will probably require the usage of a consultant every time the report has to be built.

Related Post:

When you yourself have QuickBooks Enterprise 14 and above, along side an energetic annual subscription, you can use the brand 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 such as this detail by detail, also to shed some light into how this in contrast to QuickBooks’ standard reporting capabilities.

Related Post:

Watch the video here, or click the box below.

In Summary, the best way to build the report is to follow these six steps:

1. Go to your Reports menu and click on Advanced Reporting to run QBAR. You really 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 will look like this:

3. Right-click on the light gray bar in addition to the “chart” (in QBAR, any table with data, whether it’s graphical or perhaps not, is known as a Chart), and click on properties.

4. First, we are going to ADD the most well-liked vendor through the Item table by clicking on the Dimensions Tab. It will seem like this:

Related Article:

The next thing listed here is to take into consideration the Item table within the drop-down, where it says Show Fields from Table. Then, when you look at the Available Fields/Groups above, look for Item.Preferred.Vendor.Full.Name and click on Add. When you view it inside the Used Dimensions box, click on it and promote all of it the way to the top. Which means this will be the main row (or pivoting row) that may organize the information – this is the “By Preferred Vendor” portion of the report that we are building. It must seem like this now:


Optionally, you are able to click on Suppress When Value is Null should you not wish to see any numbers for items that don't have a preferred vendor.

5. Next, into the Expression tab, we intend to click for each expression and uncheck the Enable checkbox off to the right, apart from Amount. We can't disable amount because we require a minumum of one expression of transaction data to show on the report (Of course, you are invited to keep QTY and SALES PRICE enabled, while they may be useful in this kind of report.). That should look like this:


6. And, the last setting is Presentation. We intend to tell QBAR that individuals don't want to see any detailed transaction information – just collapsed totals. To do that, we must select the second dimension Item Type and uncheck Always Show Fully Expanded. It will appear to be this:


When we are done, we could click on Apply and OK, while the report can look such as this:


You may need to right click the title and then click on properties to alter the title to the desired name, similar to this:


This really is only one illustration of the truly amazing potential that QBAR has for building those very specific reports your customers might need so as to make faster and better decisions. You will find currently a small number of learning resources on QBAR, but We have dedicated a full page back at my site to aggregating all of the resources i am aware on Advanced Reporting for QuickBooks Enterprise.

I am going to also keep updating the page as increasingly more resources become available!

Now, although I am writing this article, I will confess that I'm not a professional in this tool in the slightest. I experienced to blow a couple of hours to get familiar with it and my interview with Joseph was very useful. So, I caused it to be my goal this year in order to become increasingly more confortable with this tool in order that I am able to add a whole new dimension (no pun intended) of service to my current clients and potential new ones. The reality that this is not an easy tool to begin with with, and therefore there wasn’t a lot of consultants which have mastered this tool, presents an excellent opportunity for QuickBooks ProAdvisors!® Although the learning curve is long and there are lots of hours of trail-and-error experimentation until such time you build the coziness level to head out and offer Advanced Reporting customization services, I guarantee the rewards will likely to be worth it.

«   »

Add comment


There are no comments yet.