Information Reporting Systems: The Good, The Bad And The Buzzwords
You've just been to an inventory management workshop and are anxious to eliminate the dead stock that's rotting in your warehouse. You want to know if your best salesperson is building new business or just milking the old. You need one listing of all of the invoices posted to two different accounts for the last eight months.
There was a time when these needs could be satisfied only by a lot of manual labor - poring over foot-thick green bar reports, highlighting and paper clipping, then jotting things down on legal pads. Or you might have called your software vendor and requested some custom report programming, then waited six months or so.
Those days are long gone. Even if you got your software at the local office supply store, chances are it allows you to export your data to a spreadsheet format, where you can have your way with it. Your ability to crank out custom reports and colorful pie charts grows dramatically when you can use anything better than shrink-wrapped software. Somewhere you probably have a menu selection or icon labeled data export, data query, report writer, report generator, data warehouse or data miner. If buzzword fever has struck, it may be labeled "executive information system" or, my all-time favorite, "business intelligence." You may even have the use of a third party data utility like Crystal Reports or BusinessObjects.
After years of frustrating clients with programmed reports that only did it their way, distribution software vendors now understand that no amount of programming can anticipate the constantly changing information needs of the wholesale distributor. Cheap disk space has encouraged software designers to add data fields to their files with abandon. All of this information makes it impossible for a designer to create the thousands of possible reports a client might request. In fact, many vendors now spend little time developing new reports, referring clients to their reporting utilities instead.
When you bought your software, the salesperson demonstrated how you could easily create the report of your dreams with a few clicks of the mouse or simple typed commands. (Here's a tip: If there's a particular report that you need to run your business and a prospective software vendor says it's "no problem" with their report utilities, require them to prove it. If it's easy for you, it should be a snap for them.)
The ability to create ad hoc reports is a boon to the wholesaler. A rep walks in and says, "I got stuck with a carload of fixtures you can have for a song if you can take it all." No preprogrammed report is likely to tell you exactly what you need to decide whether this deal is good for you. You need a report that selects only specific products and gives you specific information. This is when you learn that getting anything beyond a simple listing of names and addresses out of your reporting system is kind of like programming.
Your poor dumb computer has no idea what you want. You will have to tell it what you want in exactly the right way. And don't think that pretty screen that you navigate with a few mouse clicks will do any more than save you a little typing. True, it's not Java or C++ but it does require a little effort to use well. Coffee shops (formerly known as bookstores) have shelves full of tomes on Excel, and you paid for something more sophisticated. Now you will probably pay some more to figure out how to use it. The more complex your report requirements and sophisticated your reporting system, the more time and money you should expect to spend learning to speak their language.
One of the first things you learn is that creating useful reports is more than just speaking the language. You can probably get everything you want on the report if you're prepared to read it with an electron microscope. Scrolling through a report when you haven't filtered out the extraneous items can preclude lunch. Your reporting system can do some calculations like a spreadsheet, but they're two different tools with different strengths and weaknesses, and you may end up using both to do the job right.
The most overwhelming thing you learn is how complex your information base is. You'll find that you have multiple files where you thought you had just one and your files contain hundreds of fields of information you never imagined existed. The names of these files and fields make up a data dictionary for your reporting language.
If your software vendor has done their documentation job, your data dictionary will include definitions. If they haven't, you're in for a world of unnecessary frustration. The names of many files and fields can be less than enlightening. What is AR_FLAG1? What does it mean when it's set to "3"?
Knowing the hows and whys of your files and fields is important. Knowing the hows and whys of the report you want is critical. More than one well-intentioned manager has made his company's plight worse using a report that lacks a completely thought-out design.
Consider the very first scenario of this article. You have returned from a workshop where you were exhorted by a consultant to eliminate dead stock. Most consultants consider any stocked non-seasonal item that has not sold in one year to be dead. If you use quality distribution software, you probably have a report on an inventory management menu that approximates this description. Hopefully, your software vendor has been around the block a few times and has designed around the pitfalls I'm about to list or noted them in the report's documentation. But if you don't have an existing report or want a different design than the preprogrammed one provided, your reporting system is just the tool for the job. You might create a report request that sounds something like, "show me the products with a last sales date earlier than one year ago and with an on-hand quantity greater than zero." That should get you a list of items for your next clearance sale or dump run, right?
Not necessarily. What if an item has been ordered by a customer and not delivered yet because the customer isn't ready for it? Does your software track "date of last order"? What if the item is a new product that was not in stock until recently? Do you flag new products in product maintenance? Or what if an item is a service or repair item that a contract requires you to keep on the shelf? What if the product is in a line to which you are shifting your sales emphasis? What if your software can't tell this is part of a kit? What if it's stocked only to supply another warehouse? What about products that don't appear on the report because they were returned by a customer and your software sees credits as sales and updates the last sales date regardless?
Your management team probably would have caught most of these, assuming everyone had a chance to review the report before action was taken. But that's most, not all. So you end up dumping product only to turn around and order it again. Meanwhile, some of the real moldy oldies remain on your shelf. Better to create a report that hits closer to the mark and save yourself time, money and embarrassment.
How do you use your reporting system to hit the mark?
1. Get Training - The best place to learn how to "program" your reports is from your software vendor. The vendor can teach you how to speak the language and show you the best ways to use your data dictionary to get what you want. If your applications software vendor doesn't offer training, check with your database vendor. And there are independent trainers for most popular database query languages and third party utilities as well.
2. Know Your Data - You should know the layout of your data like you know the layout of your warehouse. Your applications vendor should provide you with a file map and file layouts. The file layouts will contain the names of data fields, but for your reporting system there may be other dictionary names that are synonyms or combinations or calculations of data. If your vendor uses data warehousing, you'll need documentation of your data cubes as well, though data warehousing utilities usually provide this as a byproduct of the process. Most of all, you need those definitions for your dictionary.
3. Plan And Test - Plan any report that will be the basis for a business decision or project. Once something is on paper there's a tendency to think it must be true. If you have doubts about whether you're asking for the right information, seek out the advice of other managers and contact your software support representatives. Run the report on a small selection of data before you run the whole report and waste a lot of your time. Audit some of the report data to make sure it's what you thought it would be. And be suspicious. Even though your software support representative says that LAST_SALE isn't updated by returns, test it yourself if you have any doubts.
4. Be Creative - No matter how many fields of data your vendor has created, there's always something else you could use. Many vendors build in generic fields that their customers can use for their own purposes. Usually these are simple maintenance fields that can't be updated by transactions, but they can be very useful when you want to categorize records so that they can be flagged, selected or sorted in custom reports. Many reporting systems allow you to export reports to a spreadsheet, which makes raw data much more manageable and enables you to perform more sophisticated manipulations.
5. Know When To Punt - Not everyone wants to become an expert at custom reporting. Your software vendor may offer assistance through its support departments. Some vendors or user groups support a library of client-created reports, one of which might be exactly what you're looking for or close enough to act as a template. But there are limits. You might be able to produce a Trial Balance but not the Trial Balance your CPA requires. Some reports require custom programming. And it's possible your software simply isn't gathering the information you'd like to report. The most sophisticated business intelligence system in the world can't give you what isn't there. You might be due to move up the distribution software ladder.
Today's business systems offer a dazzling array of technologies to enhance your operations. But the simple ability to extract relevant information from your system is still the most powerful way to enhance your bottom line. With a little effort invested in learning how to produce good reports, you can realize a significant return on your investment.
Reporting JargonThe software industry is not shy about turning jargon into buzzwords to make products seem bigger and better. Over time, jargon tends to be abused to the point of making it meaningless. Here are some practical definitions:
- Report Writer/Generator - A report writer or generator could be anything that creates a report that is not preprogrammed. The terms became popular when custom reporting first became a competitive feature in applications software. Often they are created by the applications software vendor and the utility may not access all of the files and fields of the database. Some report writer/generators are powerful utilities. Some are good for producing name and address listings. "Caveat emptor."
- Query Languages - A query language is a semi-structured command language - programming without the black tie - used to extract information from a database. Most commercial databases include a query language, the most popular being SQL (Standard Query Language) and its derivatives. SQL is also the name of a popular database platform. Though it is powerful, it's not something you start using after 10 minutes' instruction. The query language of "Pick" or "multivalued" databases is somewhat easier to learn.
- Data Warehousing - Data warehousing was originally developed to take data from different systems and put it in one place in a form that makes whole enterprise reporting possible. Because wholesalers typically run their businesses with just one system, data warehousing is used to reorganize complex, fragmented file structures into data that makes more sense for reporting. Data warehouses are updated periodically and may provide summary data not created by the applications software.
- OLAP (Online Analytical Processing) - OLAP is the software used to analyze and report on your database or data warehouse.
- Data Mining - Data Mining is both a reporting and an inquiry concept. Generally the idea is to be able to pursue information wherever it takes you. The ability to "drill down" into data (e.g., click on a customer name in a report and have the option of viewing a maintenance screen or an account history for that customer without leaving the report screen) is a basic function of data mining. It facilitates finding trends or causes in business information.
- ODBC (Open Database Connectivity) - Though OLAP, data warehouse and mining functions may be written by the applications vendor or the database vendor, they're frequently provided by third party vendors. In order for these software packages to pull data from your application's database, they typically rely on ODBC. If an applications vendor says that its data is ODBC it means that popular third party reporting tools can access your data.
- EIS (Executive Information System) - EIS has replaced report writer/generator as the new buzzword jargon. It's supposed to mean reporting and inquiry specifically designed for management, but applications vendors call whatever their reporting system is an EIS.
- Business Intelligence - Like EIS, Business Intelligence is used to describe just about any reporting system. It's supposed to refer to high-end analytical software that's heavy on statistics and can spot trends and patterns. It can only be called an oxymoron on a case-by-case basis.