Chuck's Reporting and Technology Corner - November 2010
Using Crystal Reports to reverse-engineer the database schema….
The most recent Sleeter Group Conference in Las Vegas was another resounding success. Doug’s membership consistently asks for deeper material, and this year was no exception – and from my view, was even more so. It was three days of jam packed information – with little time to breath or digest, until after the conference had ended.
It is ironic that my session on ODBC is probably one of the more difficult to digest because of the material, and it is in the last slot of the conference – when people are worn down, and ready to give their brains a break.
However, I give the audience credit for hanging in there, and doing their best to refrain from ‘glazed eye syndrome’.
We covered three main topics that you could easily spend a day per topic, and it became a personal challenge to keep everyone engaged and interested.
Although I am well known more for my involvement in all things QODBC/ODBC and QuickBooks, I was happy to carve out a small portion of the session to discuss Peachtree custom reporting using SAP Crystal Reports®. What many people may not know, is that before there was any SDK and QODBC driver for QuickBooks®, I created a ton of Crystal Reports for Peachtree, Great Plains, and SBT for many of my clients.
Why Crystal Reports?
Many have asked me why I am so ‘tied’ to Crystal Reports. Aside from the years of investment I have in the product since the mid 1990’s, and the fact that there are over 15 million users worldwide – the answer is simple: it has been part of the accounting community, for just about every accounting package ever written.
Did you know that Crystal Reports was originally created by Crystal Services Inc. as Quik Reports when they couldn’t find a suitable commercial report writer for their accounting software? Seagate acquired Crystal Services in 1994, and rebranded it as Crystal Decisions. Seagate created versions 4 thru 9, after which Business Objects acquired the product in December 2003. Business Objects created versions 10, 11, XI, 2008 and was eventually acquired by SAP on October, 2007.
No matter what part of the accounting technology industry I was involved in, Crystal Reports was just a natural part of the equation – and income for the various business entities I have been involved in.
There was also another ‘secret’ to my madness. Using Crystal allowed me to view the details of the data, and enabled me to reverse engineer the reports which the various accounting systems generated. Now, there are a ton of query tools that you can use to extract data, but most are confusing and inefficient, because they pre-suppose that you know ‘something’ about SQL statements.
I still would recommend Crystal Reports for the reverse engineering process.
I have given dozens of sessions on the use of QODBC, ODBC, Crystal Reports, etc. and I have always left with a frustrated feeling – one that stemmed from my own perceived inability to teach people the ins and outs of custom reporting. I tried new approaches, reworked my presentations, and despite improvements in my techniques, it was always clear – this is a tough subject, period. Over time I realized that I can only teach people how to fish – not fish for them.
This, of course, was one of the main reasons why we created our QQube technology – to eliminate the need to know tables, relationships, or having to reverse engineer. But there are still people who want to learn the traditional methods of:
- Determining what data exists in which tables
- Ascertaining how to link them together
- Matching back to the data origins of the application
And they wanted to know how I learned all of this.
Aside from years of sweat and hard work, Crystal was one of the reasons I was able to learn so much about data schemas, and how applications built their existing reporting mechanisms. (I also learned WHY many of these applications didn’t have the reports that people asked for, but that is for another day.)
You can open up Crystal Reports, open up a table, and display whatever information you want. You can add another table to see if it relates as you might expect. You can even have Crystal Reports generate the SQL statement for you.
Today, I can write complex SQL statements on the fly, but it wasn’t always that way. I started with Crystal Reports, and learned along the way. During the time I worked on the Business Analysis project for Intuit, I used Crystal Reports to give me a sense of what was in the raw tables, and how the data was used and aggregated. It was only AFTER I performed that step, that I was able to generate the SQL for the cloud engineers to do their thing.
So how do I learn what you know?
My problem over the years of producing and delivering teaching content, is that I have been trying to find a ‘simple’ way of showing people how to build something.
I remember the very first time I introduced QODBC at an Intuit conference, and asked for a show of hands from those who had even heard of ODBC. Out of 250 people, only 5 raised their hands. Think I was in trouble?
However, it was a question I received that was a premonition of things to come: “I want to create a job cost report with these columns in it”. Heck, it took our company several months to create hardfast logic for creating job cost reports, and then anywhere from 5 to 20 hours to create what people wanted. That poor lady was disappointed that I couldn’t give her a quick answer – and she probably left empty – and with good reason.
If you look at a third party application like XPanded Reports, KPI, QVinci to see if they can provide a fix for simple requests, and you are still stuck, there are only two choices:
- Hire a knowledgeable developer, or third party company like ours to create complex reporting needs.
- Dig in yourself to see if you can do it.
I think that many people opt to farm it out, but for those that want to invest the time in learning about the process – use Crystal Reports. Connect to the data, and look at the data in each of the tables. Connect one list table to a transaction table to see how it works.
Crystal Reports is not the real barrier to creating reports for your clients. Knowing the data is – no matter what the application.
Yes, I have a greater vision that will eliminate the need for all of that. But I know that some of you will still want to stick to tradition and learn it the old fashion way. Crystal Reports was my key – and my secret.