Prophet21 Item Usage

Background

Air Power is a distributor of industrial tools primarily pneumatic. Like many distributors, we use an ERP from Epicor named "Prophet21." This software was initially named "Commerce Center" created by the company "Prophet21" before they were acquired by Activant (which was later acquired by Epicor).

When I started at Air Power in 2000 they were on a character-based system from Prophet21 named "Acclaim." In late 2005, the decision was made to upgrade to a Windows-based solution and logically we decided to move to Prophet21's Commerce Center. After a short 6 month conversion we went live in early July 2006.

Very soon after going live we discovered the way the software displayed an item's usage was not going to work for us. In Prophet21, usage is displayed by sales location. To determine the company-wide usage you have to record the usage for a location, then click another tab to select another location. Then click back on the Usage tab to see that location's usage for the item being displayed. Repeat this for all locations then add up the figure for each location to get the total usage. With several locations this is tedious and when reviewing several items is laborious.

The Challenge

Develop a way to view an item's usage that displays a company-wide total. The solution should be no more difficult to use than displaying usage for just one location in Prophet21. Furthermore, the solution must display the usage in real-time - not relying on a "data dump" of the previous day.

The Solution

In an attempt to find a solution quickly we hired a consultant from Prophet21 to work on-site for one week. At the same time I went to work on a web-based report that would be accessed from our company intranet. Having experience with Crystal Reports, I also started work on a custom report that I thought may be integrated into Commerce Center itself somehow.

Our on-site consultant proposed a Crystal Reports solution as well and began work on creating a custom report. After learning some of the requirements that would be needed and how the end-user was expected to use the report, I abandoned the idea of using Crystal Reports and focused on the web-based report. Both the Crystal Reports report and the web-based report would be presented to a steering committee to make the final decision.

After a few days both the consultant and I had working prototypes and presented them to the steering committee. Both options looked promising and the next two days were spent making design changes at the request of the steering committee. One concern over the web-based reports was acceptance by end-users, as this was to many the first time the Internet was relied on to produce a report. Everyone had heard of Crystal Reports and the canned reports within Commerce Center were written in Crystal as well.

Fortunately, the steps required by the end-user to use the custom report and the limitations to updating the report outweighed the perceived limitations of the web-based report and the steering committee selected the web-based report.

The Result

The web-based report was an instant hit with all end-users. Instead of resistence most users offered suggestions and requests for more information to be displayed. Although it is difficult to know how much time it has saved, nearly everyone agrees that this report is a timesaver.

The original report was written using Microsoft's Active Server Pages (ASP) technology. In 2010 the report was re-written in PHP and included features such as auto-complete, uploading of files, and displaying information specific to the location of the employee viewing the report.

What was learned

The most obvious learning opportunity was querying a Microsoft SQL Server database and displaying the data using web scripting techniques. At the time, innovative HTML techniques like Cascading Style Sheets (CSS) and Asynchronous JavaScript (AJAX) were just emerging. The 2010 re-write relies heavily on the jQuery Javascript framework to provide a rich and interactive interface. This reliance on jQuery has required learning to write better and stricter HTML.

July 2006, revised October 2010