Enhance Inventory System Functionality Through Custom Reporting.

There are several truths that apply to business software packages regardless of whether you are using a $500 small business inventory tracking package or a multi-million dollar enterprise package.

They have bugs.
They don’t do everything you thought they would do.
The standard reports available are less than optimal for your business needs.

These truths do vary somewhat between the low-end and high-end systems in that the low-end systems are generally less buggy due to a much lower level of complexity and the high-end systems will certainly offer more functionality and more standard reporting. What is also true is that few companies take full advantage of the data available because they remain constrained by the standard reports or by the misconception that custom reporting falls under the responsibility of the IS department and requires a programmer or the software manufacturer to implement.

Executives and managers at larger companies have long known the value of having programmers create custom reports where data is accumulated, manipulated and summarized giving them the information to make the decisions necessary to remain competitive. However, this level of information utilization is rarely achieved at mid and lower levels of an organization and is a complete mystery to smaller businesses. The primary reasons for this are that end-users are unaware of the reporting tools available and/or are unaware that it does not take a programmer to use these tools. There is also the misconception that custom reporting is a tool used for high level analysis and does not apply to day to day operations.

Types of custom reports.

The fact is there are many applications for custom reporting, some of which I have listed below:

High-level analysis.

Certainly the most widely used application for custom reporting, high level analysis includes sales and marketing analyses, overall manufacturing utilization, inventory levels, fill rates, etc. These are generally reports run weekly, monthly, quarterly, or annually and are usually summarized reports showing very little detail.

Lower-level management reporting.

These reports help manage the day-to-day operational issues. Examples include production reports (may be based on departments, shifts, or individual workers), detailed inventory reports including analysis of on-hand balances, inbound and outbound inventory, work order and purchase order reporting including past due and quantity variances, and planning reports including forecast variance reports.

Exception reporting.

These reports are used to troubleshoot processes and identify errors. Exception reporting is invaluable when high numbers of transactions occur daily. Exception reporting can be applied to inventory transactions, production transactions, product data management, and sales transactions. The idea being that you identify common errors by designing reports that list those transactions by selecting on combinations of data characteristics that are common in those types of errors.

Functional reporting.

The most underutilized and possibly the most potentially beneficial type of reporting, functional reporting is used to make up for deficiencies in the functionality of your information systems. Examples of functional reporting would include reports designed to facilitate batch picking or wave picking in systems not designed to do so, reports designed to be used as the counting document in a cycle count program, reports designed to be used as the production schedule on the shop floor. Functional reporting may be used as part of a long-term process or in short-term testing of a new process prior to automating it.

Once you get started with custom reporting you will find that a little knowledge and a little imagination can go a long way in creating operational visibility as well as optimizing the processes within the operation.

While I have said that it does not take a programmer to create custom reports, it does take a certain level of computer proficiency. If your software experience is limited to entering data into blank spaces and pushing the enter key, you are probably not ready to jump into custom reporting. However, if you are regularly creating your own spreadsheets, using functions other than SUM, linking cells, and using data filtering and sorting tools you are well on your way.

Database basics.

Before we go any further I want to clarify some of the terminology used.

Database.: The highest level of a group of data. In a packaged software system all of your business information is generally stored in a single database. A database is a collection of files or tables.
File.: Also called tables, files are an organized group (list) of similar information. A Customer Master File would contain the names and addresses of all customers. Other examples of files include Item Master Files, Purchase Order Files (usually broken down into two files, a header and detail file), and Transaction History Files. A file or table is a collection of records.
Record.: A unique line of information within a File or Table. For example, there would be a separate record for each customer in the customer master file or for each item in the item master file. A record is a collection of fields within a file.
Field.: A field refers to the specific piece of data within a record. In the customer master file there would be a separate field for customer name, address, city, state, zip code, etc.
Record Selection.: Also called data selection or filtering. Record selection refers to the ability to select certain records based upon the data in specific fields. For example, selecting on the field STATE in the Customer Master File would allow you to select only records for customers within a specific state.
Sequencing.: Also called sorting refers to the ability to sort records by a specific field or fields. In a sales report of customers you may want to sort by Sales Territory and then Customer Type.

Reporting and analysis tools.

There is a broad range of functionality available in reporting tools and you can assume the more functional tools will also require I higher skill set to operate. The first thing you need to do is to figure out what reporting tools are available for your system. Some reporting tools are designed to work specifically with a particular software package, others work directly with the underlying database, and others require you to export the data from your system and then import it into the reporting tool. The best place to start is with your software documentation or contacting the software vendor. I have listed some examples of the levels of reporting tools below:

Standard Reports that allow some user configuration options.

These are the easiest to use and will often be provided with your business software package. Essentially they are a pre-defined report format that will allow you to specify which records to print and possibly the sequence and summary information. For example, you may have a pre-defined report called “Purchase Orders” that shows the fields PO#, Vendor, Due Date, Item, Quantity ordered, Quantity received, Quantity Due, Unit Cost, and Extended Cost. With this type of reporting tool you cannot change the fields printed on the report, however, you may be able to select specific records (filtering), such as only printing certain vendors and only purchase orders with a quantity due greater than zero. You may also be able to change the sequencing (sorting) of the report, such as sorting by vendor and then by due date. You may also find that this tool will allow you to perform record selection and sequencing functions based upon any field on the underlying file(s) and not just those printed on the report. For example, the underlying file may contain the user ID of the person placing the purchase order. While the user ID was not set up to print on the report, you may be able to use it as a filter and only report on those purchase orders created by a specific user. While this type of custom reporting is limited by the original pre-defined report format, it can still prove to be a very valuable tool, especially to those new to custom reporting.

Query Programs.

A query program is a database management tool that provides the functionality to specify a file or several files, specify fields, create calculated fields, specify the record selection criteria and sequencing criteria and create an output called a record set. Query programs also allow many options on how to use the record set. It can be output to create a new file or added to an existing file, exported for use by another program, used to change the data on the resulting record set, or used as a basis for a report. Since query programs have the functionality to actually change the data, end users will generally not have direct access to the query program unless this functionality can be secured.

Custom Reporting Software.

This type of software will have an underlying query program that will only allow the output to be used for reporting. It will also have specific report formatting capabilities that give extended functionality to report layouts, fonts, graphics, and summary information. Custom reporting software is generally designed to directly access the files in a specific type of database. Business Objects' Crystal Reports and Sybase® InfoMaker® are two of the most well known custom reporting software products available.dQuery is an interesting product (query/reporting tool) designed to facilitate queries across multiple database platforms at the same time (I haven't tried it but it looks pretty good).

Database Software.

Database software such as Microsoft Access is a combination of tools that allow you to create your own databases as well as giving you the tools to create forms, queries, and reports to access and manipulate the data. Products such as Microsoft Access can also provide direct access to many external databases.

Ideally you will want to use a reporting tool that directly accesses the live data in your system; this will prove to be the fastest and most convenient method for creating and running custom reports. If there is not a custom reporting tool available to directly access the live data you can probably export the data from your system into a text file and then import the text file into a database program. This can prove to be cumbersome and will likely reduce your reporting to periodic analysis type reports. If your business software does not have a custom reporting tool available and is not capable of exporting data, I suggest you consider looking for another business software vendor.

The best way to learn to use these custom reporting tools is to have someone more knowledgeable set up a report or two for you and then you can start to experiment by making small changes to them. When using reporting and query tools you will find that you are venturing in an area that exists in between that of an ordinary end user and that of a programmer. You will need to find the file locations, file names, and field names to define your reports. You will need to learn about linking files and using the proper syntax to create calculated fields (results fields). And, you will run into confusing results when find that the data in an amount field that displays in a program as $135.67 may actually exist in the files as 1356700. What you will also find is that you may have an enormous amount of useful data in your system that you have not been taking advantage of.

It’s important to note that although custom reports are not considered software modifications they will have to be revisited whenever you take a software upgrade. Changes to the underlying files in a software package are common in version upgrades and even seemingly minor changes can trash a whole series of custom reports.

Advanced Data Analysis and Reporting Tools.

In recent years you may have encountered terms such as "Business Intelligence," "Data Warehousing," and "OLAP Cubes". A data warehouse is a database designed specifically for data analysis and reporting. The difference between a data warehouse and the database used by your business software is structural and quantitative. The database used by your business software is (should be) designed for transactional efficiency. This involves breaking related data into numerous separate smaller tables. In addition, data is frequently archived (moved from the active table to an archive table) in tables that have a high volume of transactions. Though this works well for the efficiency of the transactional system, it makes data analysis and reporting more complicated. A data warehouse is created by copying the data from your transactional system(s) and reassembling it in a manner that makes data analysis and reporting easier. A data warehouse will generally maintain significantly more data than your transactional system (it is essentially an archive file). In addition, a data warehouse may be assembling data from many different sources. For example, your business may use multiple computer systems for different functional areas and may even have some activities tracked in spreadsheets. You can design a data warehouse to pull all this data into a single database.

OLAP Cubes are logical structures that can be used in conjunction with a data warehouse to increase the ease of accessing the data. OLAP stands for On-Line Analytical Processing and the term "cube" is used to attempt to describe the multi-dimensional nature of this data structure. I find the term "cube" just adds confusion since it implies a three-dimensional object even though the data structure actually exists in many more than three dimensions (more than can be represented by any simple physical object). Rather than trying to visualize a multi-dimensional data structure (which will surely give you a headache), I find it's easier to think of OLAP Cubes as a data structure where logical relationships between the various tables have been predefined. The reality is if you are reading this article to gain some insights into custom reporting, you almost certainly are not at the skill level required to design an OLAP Cube, therefore you do not need to completely understand the intimate details of the structure (I don't). In fact, the main purpose of this data structure is to present the data in a manner that does not require the user to understand data structures. Whereas with the database, query, and custom reporting tools discussed previously, the user needs to understand the data structures and relationships of the various tables within the transactional system database they are accessing, an OLAP Cube structure should allow the user to quickly access data without having to link tables, create calculated fields, or summary information. If the OLAP structure was properly designed, this work has already been done.

OLAP Cubes and Data Warehouses do not necessarily allow you to extract information that you could not have extracted using the database, query, and custom reporting tools previously discussed. They just make it much easier and quicker to do so. In fact, some of the tools previously discussed can also access the data in your OLAP Cube Data Warehouse.

Although software packages continue to get better it is unlikely that software manufacturers in the near future will be able to deliver adequate pre-defined reports to meet the diverse needs of their customers. I believe custom reporting will continue to migrate more and more to the responsibility of the end-users and you will likely see more reporting tools being supplied with future software packages. Once you cut out the middleman between you and your data there’s no going back.

More Info:

Looking for report software? Your best bet is to contact your business application software vendor, however you may also want to check HalloGram's Report Writers Page, this is the best single source of reporting software I have seen, they also have very good descriptions of the products offered. About.com's Database page also provides some good info on databases (mostly Microsoft Access) and utilities.