Intranet Customer Profitability System

The Requirements

A major organization need to provide its Customer Relationship Managers (CRMs) with the ability to analyze the income that the client received from its customer across all product lines. The data was available in a data warehouse on an IBM mainframe and the client wanted to use their intranet to make the information available to its CRMs across five states.

This system needed to...

• Provide the ability for CRMs to combine multiple related customers (such as subsidiaries and other businesses with common ownership) into a single reporting entity.

• Incorporate requirements from the client’s accounting team to include numerous reports, including trend reports, for various time periods.

• Provide reports at various levels, including a single customer, all customers for a CRM, region, division, etc.

• Publish professional looking reports suitable for present to the client's chairman when needed.

The Solution

Working as lead analysts and web developers on the project team, we developed an intranet application that used Microsoft Excel in a unique way to produce outstanding reports. This especially made the accounting team feel comfortable and at home with the data.

The application was so well received that the project team received a special reward at the client's annual awards meeting. 

Technology, Tools and Innovations

We used Visual Basic to create an ActiveX component for database access. We invoked this from Active Server Pages. 

To get to the data warehouse on the IBM mainframe, we used DB2 Connect, which provided an ODBC connection to the DB2 databases.

To generate the reports we wrote Active Server Pages that generated the report data in an HTML table format, then used the Web Query feature of Excel as well as Macros in Excel to format and present the repots in a professional-looking manner.