Overall Project Mission
The overall project mission was to develop a reporting application as an extension of an existing upward feedback system. For approximately six months, the client had been capturing upward feedback data from surveys maintained within a Lotus Notes database. During this time, simple reports were created using Lotus Notes views, which allowed the users to monitor the delivery and submission of surveys throughout the company. However, the client required a means of viewing survey responses to facilitate the analysis of data.
The survey data displayed in the new reports would provide valuable insight into the company and the strengths/development opportunities of its engagement leaders within a particular office, practice, engagement or across the firm. Additionally, the reports could be viewed during the evaluation process in order to determine a person’s rating and whether or not the person should be considered for advancement.
The Reporting Challenge
The project team encountered multiple challenges during the development of the reporting application:
- The Cognos Impromptu reporting tool required a relational database back-end as its data source. However, the initial survey application was developed using a non-relational database with minimal front-end logic to enforce data integrity.
- The reporting tool could not fulfill specific formatting requirements required for the proper analysis of data critical to the principal evaluation process.
- In accordance with the firm policy, the confidentiality of the respondents had to be maintained while still allowing evaluators and feedback recipients to view the appropriate survey data within the reports.
- Due to the use of the application globally across the firm, general reports needed to be constantly accessible worldwide to allow for ad hoc analysis.
The Upward Feedback Reporting Application
Various functional requirements were required in order to develop a reporting application that would meet the business needs of the client. The following outlines the major components of the reporting application.
The survey data within Lotus Notes was migrated to an Oracle database to be utilized by the Cognos reporting tool. A combination of Lotus Notes agents and Oracle stored procedures were developed to verify that the survey data adhered to defined business rules. In addition, a process to clean up invalid data was established to ensure the migration of all survey data.
A web-based interface was developed to allow administrators to sanitize comment responses within the reporting database to ensure that any details, which would compromise the respondent’s identity, were removed. Respondent confidentiality was further guaranteed by omitting all personal information such as name, position, and project role from the appropriate reports. For exclusive reports in which respondent-specific information was displayed, data access to survey data for specific offices/practices was restricted according to the user’s security profile.
The majority of reports was developed using Cognos Impromptu. As a workaround for reports with complex formatting requirements, a supplementary VBA macro was developed to generate the appropriate reports using survey data extracted by a Lotus Notes agent into Microsoft Excel spreadsheets.
Report Generation Interface
A web-based user interface for generating reports was developed using Cognos IWR (Impromptu Web Reports). Users were able generate reports real-time, subscribe to reports, and export report data to Microsoft Excel as needed.