1 of 5
Image 1 - QuickBooks Enterprise Custom Advance Reporting tool
2 of 5
Source: Intuit - Custom Reporting ODBC Implementation Guide
Image 2 - QuickBooks Enterprise Custom Advanced Reporting tool
3 of 5
Image 3 - QuickBooks Enterprise Custom Advanced Reporting tool
4 of 5
Image 4 - QuickBooks Enterprise Custom Advanced Reporting tool
5 of 5
Image 5 - QuickBooks Enterprise Custom Advanced Reporting tool
In the first couple of articles we touched on several applications and utilities that can be used for doing custom reporting work; and those were rather high level views of those products. In this article I am going to dive a little deeper into the Custom Advanced Reporting (CAR) tool that can be found in QuickBooks Enterprise. As I mentioned in the other articles this is probably the most difficult to learn but it is probably my favorite. It gives us access to the QuickBooks data in the rawest form and while Intuit doesn’t give us access to every field we could possibly ever need most of them are there. We can produce most reports that are requested and when we need additional fields then we go to some of the other tools that we have available to us.
What makes CAR so difficult is that you really need to have the understanding of relational databases and how they work. Relational databases store data in multiple tables, if they didn’t we would call them spreadsheets. For example, the data for invoice activity is stored in a number of tables. The primary tables are the header and detail (transaction line) tables and from there we have to shoot over to several list tables to get the supporting info, such as the customer table to get the customer details and the item table to get info about the items. You even have to go to the ship via table if you want to get the name of the ship via option that was selected on the transaction because all that is in the transaction table is the ID that references the ship via value that you see on the screen (see image 1 for a sample of this).
Another issue with CAR is the limited documentation. Understanding relational databases is key to using this tool but understanding how the specific database is setup is probably the second most important thing and this is usually where we turn to the documentation. Unfortunately many companies don't do the best at documenting their databases and I hate to say that Intuit has not done a very good job at providing documentation for this tool or database. There is a guide (QuickBooks Custom Reporting ODBC Implementation Guide)to help you get started but I don’t find it to be very helpful. There is some helpful information in the guide but the one section that I found to be most helpful can be found on page 13 of the guide. What you will find there is an image of a transaction and more specifically some of the key fields in the line, table (see image 2). One of the very helpful things I learned from this illustration was that most of the information needed for a transaction can be found in the line table and there is very little that we need from the header table. In most databases that I have worked with you find things like the invoice total in the header table as it is typically thought of as something related to the entire transaction and therefore stored with the rest of the header information, but that is not so in the case of this tool. In CAR you will find totals in the line table and you can isolate this record from the other details by looking at the field is_source. If the value is a 1 then that record contains the total information for the transaction, if the value is a 0 then you are looking at a detail line from the transaction. Are you confused yet?
Another very tricky thing to try and do is report on related transactions; for example if a user creates an estimate and then from that estimate they create a sales order. What if you need to create a report that compares the total on the estimate to the total on the sales order, or maybe the sales order to the invoice? Or maybe you need to compare invoices and their related payments. Well this can be done and the magic lies in the transaction link table (the actual table name is txn_link). This table is used to get you from one transaction to the other. Image 3 shows the link types for each transaction type that has a relationship within the table. This is not found in the getting started guide; in fact I'm not sure this is documented anywhere. So now you ask, how do I use this? Well, you link the transaction_id from the transaction table that appears first in image 3 to transaction_1_id in the link table and target_id to target_1_id . Then you link the transaction_id from the second table to transaction_2_id and target_id to target_2_id (see image 4 for a sample). Finally, you would want to do a WHERE clause on this relationship and you would want that to be on the link_type. You could do this in Crystal with a record select or if you are writing your own SQL statement you could do it with a WHERE clause (see image 5 for an example).
Hopefully those of you that are looking for some more "meat" to this series have found this article helpful, and for those who are not database "experts" you have read along enough to have at least garnered some new insight into what goes into custom reporting development. The items I covered here can be used in both Crystal Reports as well as in Excel, and once you begin to understand how the data is stored in the database you can begin to unleash the power of this tool. We have even used this tool to pull QuickBooks data into some of our other applications because it's so much faster than the SDK.
Keep an eye out for the next article in this series; I plan to provide some additional comparison between reporting data in Excel vs. Crystal Reports and I may even toss in some more detail like I have in this article.