Creating Reports with Query Objects

This paper contains a collection of patterns for creating database-reporting applications. While there are many different aspects of reporting, this paper focuses on the ability to dynamically create formulas and queries so that new reports can be generated at runtime. It does not discuss user interface issues or good database design. These patterns pull data from the database and manipulate data after it has been extracted from the database. This is accomplished by converting both queries and formulas into objects. These objects are then assembled into reports through the high-level Report Objects pattern. Introduction These database-reporting patterns are based upon our research using VisualWorks Smalltalk to dynamically generate reports that map to a relational database. We discovered these patterns while we were building a Business Modeling tool for Caterpillar. Although these patterns are described from a Smalltalk perspective, we feel that they are applicable to anyone building reporting applications. Furthermore, even though our examples apply to relational database queries, these ideas could be extended to other types of queries. The Caterpillar Business Modeling project is a pilot to demonstrate how an appropriate tool can support financial analysis and business decision-making more effectively. This project translates legacy data into a relational database format where it is then mapped to and from objects. Reporting the financial aspects of the Business Model required building many reports based upon business logic and SQL queries. Earlier versions used predefined reports, but this solution was not dynamic enough since users kept requesting new reports. Although predefined reports initially produce fewer bugs by statically checking the queries and values, they required programmatic changes whenever a new report or query is needed. Such changes can introduce bugs and are time consuming since developer must meet with the users to understand their request. As a result, we researched making a flexible and user-configurable system. The difficulty here is to make a system that is both easy to understand and modify. If done incorrectly, you’ve just pushed off the task of programming to the end-users since they would have to specify everything the programmer would normally specify for them. Furthermore, such a system is more difficult to understand since everything is created at runtime and cannot be statically analyzed. This paper describes a collection of patterns, which avoids these two extremes by making objects such as query and formula objects that represent reports . These objects can be predefined so that they give the user reasonable defaults but can later be customized. Our pattern language comprises five patterns (see Figure 1). Report Objects is the top-level pattern for defining reports. It consists of Query Objects and Formula Objects. Query Objects pull data from the database and can be composed into more complex queries by applying Composable Query Object. Formula Objects process data returned from Query Objects and are maintained by Constraint Observers.