1 of 6
Xpanded Reports Interface
2 of 6
QQube with Excel Plug-in
3 of 6
Excel results from QQube
4 of 6
Crystal Reports
5 of 6
QQube Data Table Setup
6 of 6
QB-Enterprise Custom Advanced Reporting Tables
In the introductory article of the custom reporting series we started off by mentioning some of the tools that are typically used to produce customized reports for our clients. In this article we will dig a little deeper into some of these tools.
Let’s start by looking briefly at Xpanded Reports. This reporting tool is a monthly fee based reporting tool that pulls your data out of QuickBooks and stores it for reporting purposes. Many of the reports that are available in QuickBooks are also available as a starting point in Xpanded Reports. With these base reports you can rather quickly and easily add to them. You can add columns that you wouldn’t be able to add within QuickBooks and you can even add formula fields. (See image 1) Often times this is the only level of customization a user would need. I’m not a huge fan of the monthly fee format or the pull and store method of data access. However, due to the ease of use, QuickBooks and Excel look and feel, along with what I understand to be a rather small learning curve for the basic user, Xpanded Reports has its place in the custom reporting market.
Next I’d like to dig a little deeper into QQube. QQube also pulls data out of QuickBooks and stores the data in its own database. The nice thing about this is while pulling the data out of QuickBooks it is organizing the data in a way it can be read much easier. It creates a data warehouse for the data and in this format you don’t have to understand how all the pieces of data relate to one another, this is all taken care of for you. QQube organizes its data into subject areas and once you learn what data is stored in what subject area you will have an easier time achieving your end goal. The sample files that come with QQube are named in such a way that you shouldn’t have any issues finding your starting point.
QQube comes with an Excel plug-in (see image 2) that makes pulling data into Excel easy and with the Power Pivot in Excel you can create almost any report you can think of. The issue we have with this style of report is presentation. When you work in Excel you end up with a result that looks like it came from Excel (see image 3). This is when we step in with the work we do within Crystal Reports (see image 4). While we do some work in Excel there are a lot of Excel users that can accomplish what they set out to do, but there are fewer users familiar with Crystal Reports. QQube also comes with sample Crystal Reports and these are good starting points because if you start from scratch you do have to do some linking of tables. Although this linking is pretty self-explanatory when you understand the Data and Fact table setup (see image 5 for a simple example).
I think one of the biggest issues with this tool is the number of fields made available, however that’s not really a bad problem to have. One of the other items that can be an issue is the pull and store format. While there is a way to setup an automated sync process you still have to sync your data, this means that your data may not be 100% up-to-date. It can be close to real time when setup right, but it’s important to understand that it’s dependent on a sync process.
This leads us to QODBC and the Custom Advanced Reporting (CAR) tool. Both of these tools are included in QuickBooks Enterprise, and the QODBC tool can be purchased if you are running Pro or Premier. QODBC was created using the QuickBooks SDK and because of that it can be a bit slow when refreshing your reports, however it is a “live” connection to your data and gives you real time access. The CAR tool also gives you real time, direct access to your data and it’s FAST. This is an ODBC connection that Intuit created for folks like us to utilize. QQube uses this utility to pull in some of the data into their data warehouse. One small problem we have with this tool is the only data we have access to is data that Intuit has made available in the provided views. Examples of some things we don’t have access to are Serial Number info, Bank Account Numbers and Social Security Numbers. A couple of these are due to security issues, but since they are available in the SDK we don’t totally understand the reasoning, just know that these fields are not available.
These two tools are ODBC tools and that means we have to know how the tables, or views are related. The QODBC tables are presented in more layman's terms and therefore a little easier to figure out. The CAR tool is a rawer tool and it takes a lot of effort in the beginning to get all of your needed tables set up (see image 6 for a sample of table relationships using CAR). There are other “tricks” to working with the CAR tool and we are going to cover some of these intricacies in future articles.
In future installments of this series we will be looking at each of these products more deeply. Let us hear from you about your favorite 'custom reporting' tools for QuickBooks.