QuickBooks

What QuickBooks Advanced Reporting Can Do for You

Written by Bonnie Nagayama

Reporting and QuickBooks is always a topic of discussion. Clients and accountants alike spend countless hours capturing data, and then spend countless hours more trying to get out the reports they need. To address the limitations of its reporting feature, Intuit released Advanced Reporting, which is included with QuickBooks Enterprise 14 R6P and higher for those with an active Full Service Plan. After a change in policy, Advanced Reporting is also included for QuickBooks Desktop ProAdvisors.

With this tool you can add multiple levels of subtotals and groupings, add calculated columns, have access to system data not available within QuickBooks itself, and much more. In addition, you can build dashboards that include reports as well as charts and graphs.

A few notes before we get started:

  • Charlie Russell first wrote about Advanced Reporting in September 2014 when it was released.
  • If you have SQL and/or advanced Excel skills, this will be easier for you.
  • Even for those who don’t, by investing some time you too can learn the basics.
  • The Intuit provided Learning Center can be accessed via http://qbar.intuitlabs.com/.
  • It is built using QlikView, so there are resources you can find via an Internet search.
  • Advanced Reporting has its own database, which means that using this alternative will not have any effect on the QuickBooks company file performance.

To begin, the user will need to have Advanced Reporting permission. Note that at the time of this writing the permission is all or nothing. Which means that if the user has access to Advanced Reporting, they have access to all the data in the QuickBooks file. It is not currently possible, for example, to limit a user to only sales data via Advanced Reporting.

The Admin user has access to everything, but for any other user, permission is granted by editing a role. QuickBooks Advanced Reporting is located in the File section of the role access.

QuickBooks Advanced Reporting

To launch Advanced Reporting from within QuickBooks Enterprise, choose Reports > Advanced Reporting.

Launch QuickBooks Advanced Reporting

You will be prompted to install Advanced Reporting if you have not done so already. Then the process of preparing the data will begin. In the future, there will be a pop up box that will allow you to choose to update the data or to skip the step and use the data you already have.

Preparing Data

Because Advanced Reporting is a separate yet integrated product, you will notice two icons in the task bar. The one on the left is the QuickBooks software, the one on the right is Advanced Reporting.

QuickBooks Software and Advanced Reporting

In Advanced Reporting, the first thing you will notice is the welcome screen. This screen shows the Library, which is one way to add “objects” to the report. These are pre-built alternatives, but you can also build your own by right clicking on the white space.

QuickBooks Advanced Reporting

As you can tell from this very first comment, Advanced Reporting does have terminology that may be unfamiliar. Objects in this case refers to parts of the report. For example, if you double click on an option under the list box, it will add a filter box like you see for state and city. If you add a table, this is what we would think of as the report itself, such as the customer list in the screen shot example. Charts are graphical representations of the data. And the Text & Utility are additional fields, such as a comments box, the Report Title, etc.

There is also a customize tab.

QuickBooks Customize Report

To get back to this information, click on the Reports pull down and choose Open Getting Started Guide.

Open Getting Started Guide

To help you become familiar with Advanced Reporting, there are starter reports included. By clicking on Report List, you will see the choices. When you first begin, all the reports will have an “S” next to them, which means they are starter reports that have been included. Once you begin to make changes and save your report templates, you will see additional reports, and they will not have the “S”—indicating that they are the reports you have created or modified.

QuickBooks Report List

To see a wide variety of what Advanced Reporting can do, click on the first report, “AR Aging Dashboard,” and then click on the Open button at the bottom of the screen.

AR Aging Dashboard

If you are working with the sample company, like I am in the example above, you will see there is not any data. The reason is that there is a date filter that has been saved with the starter report. To remove that filter, click on the eraser in the Current Selections box.

Data Filter

Removing the filter will now display data in the report. You will notice that the Overdue AR section is still empty since there are not any invoices which are overdue in this file.

AR Aging Dashboard

From this quick example, you can see the power of having graphs/charts, reports, filters, etc. all on one screen.

To filter for specific customers only, you can click on a customer in the report and see that the name appears in the Current Selections box and the entire report is filtered for that customer. Again, to remove the filter, click on the eraser in the Current Selections Box. You can also choose to filter by multiple customer names by clicking on the name in the Customer box and then holding down the Ctrl key to choose additional customers.

The last few things I would like to have you try are:

  • Moving around the boxes on the report – This will help you to create a design that is most effective for your clients. Place you cursor on the gray title bar of a section of the report and hold down the mouse button. Now you can drag it to where you would like it to go. You can do this with the various sections of the report, such as the Overdue AR chart or the Transaction Summary report. You can also do it with the filter boxes that are currently on the left, such as the customer list.
  • Printing or Exporting – You can print the entire report, which will include the filter boxes, from the printer at the top left of the screen next to the Library button. Or, you can print an area of the report by clicking on the printer on the title bar for that specific section. “XL” also appears on that title bar, which is the way you would export that specific section to Excel.

AR Aging Dashboard

Now that you have the basics down, let’s do a quick commission report.

To begin, choose the Sales by Item Detail report from the Report List.

Sales by Item Detail Report

Right click on the gray bar at the top of the report and choose Properties.

Sales by Item Detail Report

On the Dimensions Tab, choose Transactions.Sales Rep Entity Full Name and click on the Add button to have it appear in the Used Dimensions box. At this point you can choose to Promote it so it will be the highest grouping on the report. Or if you don’t want the other groupings you can simply remove them. (Note that if you remove them, but still want them on the report—just not as a grouping—you will need to add them on the Expressions tab in the next step.)

Dimensions Tab

Next, click on the Expressions Tab and then the Add button.

Expressions Tab

At the bottom of the Edit Expression screen that appears, choose the Transactions Table and the Transactions.Amount With Sign field, then click on Paste.

Transactions Table

You can then add the commission calculation. In our case we will do a 10% commission to keep the math easy, but you can make this expression as easy or complicated as you like.

AdvRep17

Then click on OK at the bottom of the screen. This will add the Expression to the list. You can edit the label—for example, Commission. If you choose Apply at the bottom of the screen, you will see the report change behind the Properties box. Or simply click on OK to close the box and return to the report.

Sales by Item Detail

Make any other changes you desire and then click on OK to close the Properties pop up box.

At the bottom of the report you can Save or Save As. Since we started with the Starter Report, even if you click on Save it will automatically switch to Save As and ask you for the report name. Then click on OK.

QuickBooks Save As

Now, when you want to generate that report in future, you will see it on the Report List at the top.

QuickBooks Advanced Reporting List


Save pagePDF pageEmail pagePrint page

About the author

Bonnie Nagayama

Bonnie J Nagayama, CPA focused on supporting other professionals and their QuickBooks® Accounting Software clients with practical solutions for financial and day-to-day operational issues since 1992. Her Controller background and CPA experience were a perfect blend to bridge the gap between what the client wants and what the Accountant needs. She understood both sides and effectively used QuickBooks to provide a solution that worked for each party, often including QuickBooks and integrated applications. Bonnie was a member of the QuickBooks Professional Advisor Program and an Intuit Solutions Provider since each program began, as well as a member of the Intuit Trainers & Writers Network.

9 Comments

  • This is good stuff. Looking forward to exploring through Advanced Reporting. On the surface capability looks similar to PowerView in Excel 2013. Anxious to tear into this.

    • That is one reason why we asked Bonnie to write about the topic, to show people how they can use it. And, this is a great opportunity for accounting professionals to step in and help their clients.

  • Great guides here! I love it when you guys show in-depth looks into using Quickbooks. There’s definitely a ton of features I could use now!

  • I’m looking forward to playing around with this tool. Thanks, Bonnie, for the in depth review.

  • This is a good start. I’m not sure if it has the ability to combine reports from multiple companies. Until then, QQube (which you have written about) is very powerful for reporting – i use it daily.

    AccessBooks RealTime is another powerful data export tool for those a little more tech savvy and need direct SQL access to their data.

    • Personally, I prefer QQube as well. However, note that there is a significant additional fee for that, while Advanced Reporting is more affordable for many users. So it is important that we understand what the tool can do, and when to use it.