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 facts to consider, 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.
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.
Although the learning curve is long and there are many hours of trial-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.
Another way to go about creating stunning, in-depth reports with the click of your mouse is to adopt a financial analytics platform like DataRails. Their platform allows you to integrate with your existing software like Quickbooks and Excel Spreadsheets allowing you to consolidate all data into a unified platform and produce quality reports. What makes these reports even more special, is that you get to have a live and interactive connection with your reports at all times as well as share them with your management team!
DataRails Managment Dashboard
Regardless of your needs software today has the potential to make life easier. Keep posted for more tips and tricks to make you a Quickbooks professional in no time!