Reporting Item Usage in Epicor's Prophet21
Background
Figure 1: Prophet21 Item Usage screen
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
Figure 2: Web-based Item Usage on our intranet
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.
July 2006, revised October 2010
