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.
To launch Advanced Reporting from within QuickBooks Enterprise, choose Reports > 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.
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.
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.
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.
To get back to this information, click on the Reports pull down and choose 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.
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.
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.
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.
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.
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.
Right click on the gray bar at the top of the report and choose Properties.
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.)
Next, click on the Expressions Tab and then the Add button.
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.
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.
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.
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.
Now, when you want to generate that report in future, you will see it on the Report List at the top.