QuickBooks Custom Reporting Part 2: Putting it all together
Welcome to part two of our post on custom reports training. If you haven’t read part one first, I’d recommend checking it out for an overview or refresher on the screens we’ll be going over in this post.
Today we’ll take what we learned from our look into to the report customization options and put it together into a functional report.
Real-World Reporting Customization
If you’d like to following along, you can substitute information from your own QuickBooks file and build a similar report or create some dummy data in a sample file to match our examples.
Let’s say you have a hot selling product recently, the WidgetMaster. You’re doing great business on these, and you’d like to take advantage of some of that momentum with some promotional deals.
You decide you need to get a clearer picture of how many units individual customers are purchasing; when they’re buying them; and what some of the most popular items are that your customers have been purchasing alongside the WidgetMaster, so you can put together some promotions and sales. To do this, you need to see the transactions where you sold this item, but it would be a lot of work to sort through and find them manually.
How does this real-world need for data translate into a QuickBooks report, though? Let’s start by opening a “Custom Transaction Detail Report.” As you’ll recall, we go straight into the report customization window on here, so let’s click on the Filters tab.
Reporting Customization – Filters
We’ll want to start by telling QuickBooks how to narrow down the data we want to see. We know we’re interested in transactions where customers purchased the WidgetMaster, so that gives us two of our filters right off the bat. Add one filter for “Transaction Type” of Invoice, and another for Item, selecting the WidgetMaster.
Here, we’ve eliminated any transactions that aren’t invoices, and of the invoices, we’ve told QuickBooks we only want to see those with that item. If your business uses Sales Receipts, choose those instead of invoices. If you use both, choose “Multiple Transaction Types” and select both.
Finally, add a filter for Account, set to “All ordinary income accounts”. This will prevent you from seeing the lines for the cost of goods and the inventory portions of the transactions, so all you see on the report is your sales.
You’ve carried the WidgetMaster for some time, but you know the huge boom in sales has only been recent. You suspect it’s related to the product being featured in that popular movie that came out a few months ago. You decide it’s only relevant for this to see the invoices for the item since the movie came out, so you’ll want to add a date filter.
You can either add one from the left list in the “Filters”tab, like any other filter, or you can set the dates you want to see in the top portion of the “Display” tab.
In the columns section of the “Display” tab, we’ll select what information we want to see from the transactions we’ve filtered down to. If we click the checkbox above the columns to sort by selected, we see: Type, Date, Num, Name, Memo, Account, Class, Clr, Split, Debit, Credit, and Balance.
In this specific reporting exercise
We know these will all be Invoices, so we can remove the “Type” column. Date, Num, and Name columns might be useful, as could the Memo column- if you’re using it for transaction notes.”Account” may not be needed. “Class” may be helpful depending on how your business uses it, or it may be irrelevant. Leave it if you think you need it.
The rest of the columns probably won’t help us too much here, so let’s remove some of them and add some that are more useful to us. Using the “search column” box or scrolling through the list, we’ll add “Qty” and “Amount,” to see how many the customer bought and what they paid.
If we hit “OK” here, we’ll get a simple list of every line on an invoice where we sold the WidgetMaster in this period.
Report Sorting After Customization and Filtering
We could drill into each one in order, checking every invoice, but if it’s such a popular item, there may be so many that that makes it a tedious task. Maybe you’d like to prioritize the invoices you’re manually examining. There are a few great ways to do that with the “Total By” function, available in the “Customize Report” window or the middle report toolbar.
Totaling by customer will group all the lines on this report that relate to the same customer together. This will give us a subtotal for each customer, allowing us to scroll through the report and focus on customers that have purchased more of the WidgetMasters.
Now you know who your best customers are for this product, and you may be able to use that info to keep those valuable customers happy, maybe by letting them know first about the new promotion you’re doing next month.
What You Can Do Further with Reporting
From here, you could examine individual invoices to see what other items they’re buying alongside it. Or, you could add a “Name” filter to this report for the top few WidgetMaster buyers and remove the item filter to see all the items they’ve purchased in the period, giving you further info on what else is popular with WidgetMaster fans.
You can also get an idea of how many are purchased on average by each of those customers.
Similarly, if you use customer types, you can total by that to see if you’re selling more to retail or wholesale customers, for example.
If we choose, instead, to total by time period – perhaps day or week – we can scroll through and check for spikes in sales during certain dates. This can let you know when your popular times and your lulls are, to know when you might want to put the item on sale.
If you’re using QuickBooks Enterprise with Advanced Inventory, and you make use of the multiple sites feature, you also have the option to total by “inventory site” to see where you’re selling more and less of the item.
Memorizing Your Customized Reporting
Now that you’ve put some work into this report, you think you may want to use it again later. You can use the “Memorize” button in the top report toolbar to save this custom report for later in the “Memorized Report list.”
You can choose to save it in the “Memorized Report list,” or in a “Memorized Report Group,” essentially a subfolder.
This is just one small, simple report, but it starts to show how report customization can open avenues to examine your data from different angles and find the real-world answers you need. This is just a small fraction of where you can take this, however. One way to further your custom report experience is exporting a report to Excel.
Microsoft Excel & QuickBooks Reporting
Exporting reports to Excel can be a powerful tool, allowing you to further manipulate data using all the same tools and flexibility you’re used to from Excel.
In many cases, you can even create formulas in your worksheets with a report run one day, and then update that worksheet with more current data run from the same report later.
You do need to have a compatible version of Excel installed to use this feature. If you really need to export a report on a computer without Excel, you can always create a CSV file. This file type has some limitations that Excel worksheets don’t, but it may help you out in a pinch.
QuickBooks Advanced Reporting
Another option for extending your reporting capabilities is QuickBooks Advanced Reporting, or QBAR. This is a powerful program available alongside QuickBooks Enterprise on many subscription plans.
Using the versatile QlikView software as a base, QBAR builds an advanced data model of your QuickBooks information, and allows you to create interactive reports as well as charts and graphs that aren’t possible in the standard QuickBooks reporting system. It’s every bit as complex as it is powerful, but there are resources available to learn the scripting required to create the reports. If you decide to invest the time into learning it well, or work with an expert who has, you may find that it’s well worth the investment for the reports of your dreams.
Lastly, as with many QuickBooks features you might like to expand upon, there are a plethora of third-party software solutions that can connect to QuickBooks, which will all have their own ways of allowing you to create more of the reports you need.
Whichever way you choose to delve more deeply into custom reports, you’ll find that having the ability to view your data from another angle can give you all sorts of new insight into your financials and the trends of your business. Discover what data you’d like to see, and which custom reporting options are right for you, and you’ll find it can go a long way towards empowering your business success.
Still Have Questions about Reporting in QuickBooks?
Call us at 503-885-0776 to speak with a team member about one-on-one training with one of our advanced certified QuickBooks ProAdvisors.