ORACLE Reports and Companion Applications

Upon engaging this client, it was discovered that a large division was running their business off manually tracked spreadsheets maintained by about 20 order processing groups and a handful of accounting/billing departments. Our first question after the initial analysis was "How do you know exactly/generally how the company is performing". The management team had a sense that they were failing but had absolutely no way to gauge most sectors of their performance.

We performed analysis on the reporting needs and established a priority based schedule for the implementation. We developed a suite of applications to provide report capabilities for managment teams and end users of a large ORACLE based application. Implemented business rules processing to help users request the correct information from the complex database. The reporting applications bridged a major gap as the standardized ORACLE reports did not fit the business model used by the client.

As the departments of the company were dramatically information starved, we took a multi-phased results oriented approach. Initially, we targeted about a dozen reports that would provide the management team with a pulse of how the organizations were performing to serve customers

  • The result of the first reports was an extreme shock to the entire management team. Over half of the incoming orders were being cancelled by the customers based upon non-delivery.
  • Efforts were refocused based on this information to improve the organization's ability to deliver what the customers ordered.
  • The majority of the orders were past the customer requested date. The ratio was roughly 80% past due and 20% due in the future.
  • Orders completed on time was around 15%.

    The company was actually warned that the manual system they were using was quite volatile. After the first set of reports was completed, a virus hit the company hard which wiped out a large portion of the manual tracking spreadsheet files. This turned out to be a blessing, as the IT department was working around the clock and unable to recover information fast enough. The entire organization was assembled to push forward the "commitment" of everyone to using the reporting system to run the business. This benefited the client as everyone began basing their assumptions off the same set of data.

    After the routine generation of the first round of reports, progress was made to enable better scheduling and prioritization of work. Following this period, new reports were developed focused on the following areas:

  • Exception reports to head off problems in the improved processes
  • Reports to ensure that the billing department was billing everything completed
  • Reports were now being designed to be triggered by system events to help billing and customer notification of order receipt and completion.
  • Employee performance metric applications were built to allow automatic evaluation of on-time and volume performance targets.

    Initially the reports were produced in printed form. A couple of weeks after that, we switched to producing Excel and Word based reports that could be emailed through out the organization. Automated processes were put in place to generate the reports on a routine schedule. The email activity was changed to producing the reports to a network location whereby web pages would provide downloadable copies of the reports updated on a periodic basis.

    Once we began producing reports, the management team began to better understand the problems which impeded productivity. In just a few short months steps were taken to change the methods and procedures of getting work done. This led to dramatic improvements in the company's performance:

  • The previously 50% cancellation rate was dropped down to less than 10%
  • Number of orders completed each month increased by 40%
  • The ratio of future due to past due orders was reversed to be 70% future and 30% past due
  • Number of orders completed on time increased to 60% and is still rising.

    We provided programmer/user configurable options to control the operations of the report generation. This included underlying calculation constants, lists of company holidays and parameters used to control the range of data requested. The tools used were Visual Basic 6.0 and Access/97. The reports can be generated on Windows by interfacing seamlessly to back-end hardware running ORACLE.