Technology Articles

QReportBuilder: You want QuickBooks to Tell you What?

Intuit ProConnection Really Simple
You can bring these articles to your desktop as soon as they are published.


Already a ProConnection subscriber?
Update your profile and personalize your ProConnection experience.
Update your profile

Not a subscriber yet?
Sign up now for free and start receiving customized newsletters directly to your inbox.
Sign up now

Certified QuickBooks ProAdvisor Esther Friedberg Karp shows how the third party QReportBuilder allows you to respond to the increasingly complex demands of entrepreneurs to do more with QuickBooks Financial Software.

How much do business owners want reports outside the ability of QuickBooks?

Here some true stories that I'll use to count the ways.

True Stories from the Esther Files

Here's a true story from the Esther files: a client called me up and asked me if we could persuade QuickBooks to report on their sales, month by month, compared to the same month the year before, similar to the format shown below in Excel.

They wanted it customer-by-customer, for a selected group of customers (here I've chosen only two for illustration purposes), and they wanted the same report showing sales in dollars as well. They had been manually inputting figures into Excel and it was killing their admin staff.

I did the best I could; really, I did. But all I could manage was the report they wanted, filtered for one customer at a time. So we memorized the report, and kept generating it with different customer name filters, and we repeatedly exported the differently-filtered report to Excel. They had to do this every month in time for the sales meeting, and trust me, they had way more than two customers. The company's controller was extremely busy in the week leading up to the monthly sales meeting.

I knew the information was sitting there in QuickBooks, ripe for the picking. I just didn't have the right picking implement.

File 2: Images

Here's another one: I recently had the privilege of moderating the QuickBooks "Ask the Expert" forum. One questioner asked, "Is there a way to insert pictures into my estimates?" Unfortunately, the answer was no, because QuickBooks does not house item images, and the forms such as invoices and estimates cannot reference any outside image database.

File 3: A Common Request

Here's something I've been asked countless times. "What if I want to compare reports from two or more QuickBooks data files on one report?" My answer would always start with "Sorry..."

File 4: Excluding Names from a Report

Finally, here's one more example: QuickBooks allows you to filter to include certain names, but what if you want to exclude certain names from a report? No can do.

The Moral of the Story

Even QuickBooks has limitations when it comes to reporting. As the entrepreneurial sector grows, businesses are adopting QuickBooks by the truckload, and I see a trend. Companies are requiring reports in greater variety, depth and sophistication.

You can only do so much with the workaround of exporting to Excel and then performing various manipulations and format changes.

But what do you want for an entry price of a couple of hundred bucks? And that's what I kept telling myself.

Accessing QuickBooks Data

That was before I discovered a whole new world called QODBC (QuickBooks Open Database Connectivity). Holy cow. Here is an outside driver that treats my QuickBooks data purely as a database. It does what it is instructed, extracting the combination of fields requested regardless of whether a QuickBooks report allows for it.

QODBC is included with QuickBooks Enterprise Solutions, or you can buy it separately. With this solution, you can extract plenty of information - but you need to be able to organize the results in an organized, esthetically pleasing fashion that you don't have to keep setting up and tweaking every time you want to produce updated information. We need a driver to drive the QODBC driver!

Enter QReportBuilder

FLEXquarters.com LLC and Alpha Software, Inc. have joined forces to come up with QReportBuilder, which uses the power of QODBC behind a graphical user interface that enables users to get the output they want from QuickBooks in the format they need. You don't need Enterprise to use it.

International Note: QReportBuilder is compatible with many versions of QuickBooks from many countries.

How It Works

Your first option is to link up QReportBuilder to your QuickBooks data, and so you have to "Refresh Data" and follow the prompts in both QReportBuilder and QuickBooks to let QReportBuilder access your company file, much like any third-party add-on.

Once the data is refreshed, QReportBuilder will be working from a set of tables it has created, based on the QuickBooks data that existed at the time the data was refreshed. In other words, there is no ongoing link to your live QuickBooks data file unless you wish to refresh data again. The initial refreshing of the data allows QReportBuilder to work very quickly to produce reports.

Tip: You can choose which tables to refresh (if you don't wish to refresh them all), so that in the case of large data files you don't wait unnecessarily long to update a bunch of information which you're not interested in querying.

Editing Reports

There is a powerful report editor for formatting one's output, and you can make use of Excel-like formulas to create totals, subtotals, and groupings. You can include overall graphics such as a company logo, and you can create highlighted areas, underlines, bold fonts, and many other ways to customize the output.

QReportBuilder reports can start with QuickBooks reports that exist already (such as the reports on the left side of the screen shot above with the green icons), or they can be created from scratch, querying the fields of interest.

Notice on the right side of the screen shot above, the Credit Memo screen has a "legend" of all the fields that are used in that type of transaction. This will help the newbies acclimatize themselves to the strange field names that are lurking behind the QuickBooks interface. This is a great embedded help feature, because otherwise the field names are downright puzzling.

There is even a guide to the list and transaction names in the current data file, along with the fields that reside in them. This will help the user locate common fields such as the Customer Name in two sources (e.g. the Customer List and an Invoice) to produce an A/R aging report with a customer address, which is normally impossible to produce in QuickBooks:

Databases for Images and More

I can include outside databases too. For instance, there is no room in QuickBooks for pictures of the items you sell. But you can create a database, using the same item names as those you use in QuickBooks, with associated images. You can then run a sales report that picks up the sales figures from QuickBooks and also picks up a picture of each item to insert in the report next to the relevant figures.

You can insert these pictures into estimates, sales receipts, invoices or credit memos. So the question from the "Ask the Expert" forum about inserting pictures into the estimates (or invoices or credit memos) is answered, and catalogs can be created by QReportBuilder as well.

Negative Options: Reporting on What Didn't Happen

QuickBooks lets you report on transactions that did happen, but what about transactions that didn't happen? If, for example, you need a report by sales rep listing all the customers who haven't bought anything in the last 60 days, QReportBuilder can produce it. In fact, the report can be set up as an interactive exercise: it can ask you, the user, to fill in the number of days during which sales did not occur during the generation of the report.

Reporting on Ranges

Here's one that always gets me: what if I want QuickBooks to give me a report regarding transactions that are between two amounts? QuickBooks itself lets me do a report for matching exactly an amount, or a report for greater than or less than an amount, but not anything between two figures. QReportBuilder's formulas allow you to choose these and many other Excel-type of functions and calculations, including "less than or equal to" and "greater than or equal to."

Back to Esther File 1

How about my client with the requirement to show this January 2006 vs. January 2007 and so on? (See "File 1" above.) The screen shot below shows revenue dollars for items, and QReportBuilder managed a similar one for quantities as well. Note the company logo and the highlighting.

In the spirit of QuickBooks customized reports, QReportBuilder allows us to take a saved report definition, export it, send it to another user, and they can import it and use it on another company file. Furthermore, the team at QReportBuilder is getting custom report templates ready to put up for sale on the website.

Working with Two QuickBooks Data Files

How about information from two QuickBooks data files? You can't refresh data from two different company files at the same time, but you can save one data file's information as an external database, and open the second data file and use its refreshed data as the current database to link to the external one.

Other Options

If you sit down and think about it, you'll come up with countless ways to use this add-on, for example:

• Report by Customer of what they ordered during a certain date range, including customer contact name and phone number;
• Report by item of what customers ordered it during a certain date range such as last December;
• Generate a mail merge documents saying "Dear (Customer Name): As you purchased (Qty) of (Item name) last (Time period), we'd like you to know that we have a large shipment coming in this Christmas";
• Customized invoice with embedded statement;
• Paid invoices report based on date paid for sales rep commissions, along with an included commission calculation based on the sales figures;
• And many more...

At a price of $299 for single user, this adds a lot of oomph to QuickBooks for very little moolah.

Notes and Comments

QReportBuilder is so cutting edge in putting an easy-to-use interface onto QODBC that it is a work in progress. The help files, getting started manual and video tutorials are being honed as I write this. The team is constantly endeavoring to make it a better product. This is a program for serious users only, who are willing to ensconce themselves in an entirely new mindset, being that of the database miner.

There is a serious learning curve, especially for those who do not have a lot of database experience, so the question is not whether it's worth the money (it is), but whether you have the time to devote to it (you'd better). The ROI will be beyond measure, though.

My personal main issue is that the field names, which are the actual QuickBooks coding names for the fields and tables in QuickBooks, are not (pardon the pun) intuitive. (Do you know what InvoiceLinkedTxn is? Neither did I.) I think it would be great if QReportBuilder made use of more user-friendly names that we could all understand. This would be a huge undertaking, so in the meantime they have created the screens identifying the field names and where they are located in either transactions or list names.

International Note: If you're installing more than one country's version of QuickBooks, you'd better use another computer or something like Virtual PC. Otherwise, you'll get an API (Application Programming Interface) error. But this is true in general of third-party add-ons that depend on the SDK (Software Developers' Kit). Nevertheless, be careful.

In pursuing QODBC and QReportBuilder as solutions for my clients, I realize that I am not Columbus discovering America. I just feel as if I am. This is a whole new world.

Editor's Note: Some screenshots in the preceding article have been edited for space.

Esther Friedberg Karp is a Certified QuickBooks ProAdvisor in both Canada and the United States. She is President and Owner of CompuBooks Business Services and a frequent contributor to both the U.S. and Canadian editions of Intuit® ProConnection®.

Last Updated: 01/09/2008