6 Steps to Building the Reports You Need in QuickBooks
If you should be a seasoned QuickBooks® Desktop (Pro, Premier, Accountant or Enterprise) reports customizer, you know how great QuickBooks is actually for building custom reports on the fly. However, there are many really important restrictions to know about, including what I think is most prevailing: QuickBooks can only build a written report using data from 2 kinds of tables. If You want to know about Reports In QuickBooks then call our experts.
The two tables include:
Transactions: These 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 that you cannot pull up a study, 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 likely to be area of the Transactions Table (such as for example invoices and sales receipts). Therefore, you would need to pull two different reports and then use some sort of VLOOKUP formula to place it together … I know some of you Excel gurus have probably figured out just how to repeat this. However, this is not a sustainable process since it will probably require the employment of a consultant each time the report needs to be built.
When you yourself have QuickBooks Enterprise 14 and above, along with a working annual subscription, you can make use of the latest Advanced Reporting add-on called QBAR (included with your subscription) to generate this report.
You can find quite a few videos in the QuickBooks Enterprise Learning Center on QBAR. I interviewed Joseph Lasee, senior product manager for QBAR, asking him simple tips to create a report like this step by step, also to shed some light into how this in contrast to QuickBooks’ standard reporting capabilities. Watch the video here, or click the box below.
To sum up, how to build the report is to follow these six steps:
1. Go to your Reports menu and then click on Advanced Reporting to operate QBAR. You need to be in multi-user mode and have user permissions to operate QBAR if you're not the Admin.
2. Open a Starter Report, such as Sales by Item Detail. It will seem like this:
3. Right-click on the light gray bar together with the “chart” (in QBAR, any table with data, whether it’s graphical or not, is named a Chart), and click on properties.
4. First, we are going to ADD the most well-liked vendor from the Item table by clicking on the Dimensions Tab. It must look like this:
The next thing the following is to take into consideration the Item table when you look at 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. Once you notice it in the Used Dimensions box, click about it and promote it all the way to the most notable. Which means that this will be the main row (or pivoting row) which will organize the information and knowledge – this is the “By Preferred Vendor” part of the report that we are building. It should appear to be 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 do not have a preferred vendor.
5. Next, when you look at the Expression tab, we intend to click on each expression and uncheck the Enable checkbox off to the right, except for Amount. We cannot disable amount because we want at least one expression of transaction data to demonstrate in the report (Of course, you are welcome to keep QTY and SALES PRICE enabled, while they could also be beneficial in this particular report.). That will look like this:
6. And, the last setting is Presentation. We will tell QBAR that we do not want to see any detailed transaction information – just collapsed totals. In order to do that, we must choose the second dimension Item Type and uncheck Always Show Fully Expanded. It will seem like this:
Once we are done, we can click on Apply and OK, while the report will appear similar to this:
You may have to right click the title and click on properties to improve the title to your desired name, like this:
This will be just one illustration of the truly amazing potential that QBAR has for building those very specific reports your customers may need so as to make faster and better decisions. You will find currently a finite quantity of learning resources on QBAR, but I have dedicated a web page to my site to aggregating all of the resources i am aware on Advanced Reporting for QuickBooks Enterprise.
I shall also keep updating the page as increasingly more resources become available!
Now, although i will be writing this informative article, i shall confess that I'm not a professional in this tool at all. I had to spend a few hours to get familiar with it and my interview with Joseph was very helpful. So, I managed to get my goal this year to become more and more confortable with this tool in order that I'm able to add an entire new dimension (no pun intended) of service to my current clients and potential new ones. The fact this is not a simple tool to begin with with, and therefore there wasn’t a whole lot of consultants which have mastered this tool, presents a great chance for QuickBooks ProAdvisors!® Even though learning curve is long and there are numerous hours of trail-and-error experimentation before you build the comfort level to head out and offer Advanced Reporting customization services, I guarantee the rewards will undoubtedly be worth it.