The present invention relates to information processing and more particularly to database access and reporting systems and methods related to information processing.
Data access and reporting have long played an essential role in enterprise management. Without the ability to adequately access, summarize, and manipulate raw data the efficiency of an enterprise suffers. Typically reporting systems perform specific data access and reporting functions designed to provide enterprises meaningful access to data.
However, many reporting systems lack the functionality to provide users with the ability to look behind the data contained in a report. Others have limited functionality to permit users to drill down into data provided in a report. For example, some reporting systems allow a user to see that a global sales number is comprised of national sales numbers, which are comprised of regional sales numbers, which are comprised of city sales numbers. Still other reporting systems allow users to drill through data in a report in a limited manner. For example, a reporting system may allow a user looking at customer data to drill through into orders placed by that customer and see all of the orders placed by the customer.
However, reporting systems that provide the ability to drill down or drill through data typically rely on static links predefined by a person familiar with the data. Thus, such reporting systems lack the ability to allow a user to see data behind data provided in a report for which a predefined drill link has not been defined. Some reporting systems attempt to overcome this limitation by creating super reports, reports that include predefined links for a large number of data items. Notwithstanding, these super reports are limited by the access needs perceived by the report designers and further can overwhelm users with too much data, making them unhelpful.
Additionally, because super reports and reports that include predefined drill links require extensive knowledge of underlying data structures, they are often prohibitively expensive to create, maintain and deploy. With prices in the hundreds or thousands of dollars per copy, and tens of thousands of dollars for an enterprise license, data access and reporting may be prohibitively expensive. This is especially true in the case of a small or start-up business.
Thus, there is a general need in the art for a data access method and system that frees report designers from the necessity of building predefined drill links in reports and from developing super reports that are overwhelming and confusing to users. There is also a general need for reports that include dynamically generated drill links based upon an entity relationship of data. There is also a need for a data access method and system that is inexpensive and affordable by individuals or small companies.
According to the present invention, a method and system for creating and following drill links in a report are provided. The method and system empower information technology professionals and report designers to efficiently and inexpensively provide reports that novice or casual computer users can use to easily follow relations inherent in data and see how data is related to other data.
According to one embodiment, the present invention is implemented through a distributed application that runs on multiple computers but is displayed on a graphical user interface (GUI). This GUI, combined with common input devices such as a mouse and keyboard, minimizes the learning curve for use of the present invention. Thus, even a novice or casual user may quickly and easily understand and apply the present invention to access and build reports from a data store.
The embodiment provides a simple-to-use application that displays fields associated with a data store. The fields associated with a data store are defined in a relational abstraction of the data store. The embodiment provides an easily comprehended means of interactively and iteratively selecting fields defined in the relational abstraction, according to the user's desires in response to simple and efficient input commands. Using the invention, a user selects an initial view associated with the relational abstraction. This view, referred to herein as the base view, becomes the entry point into the relational abstraction and is used to constrain which fields are displayed for selection by the user and which relations of the relational abstraction can be followed to select the fields. Selecting the base view is inherently understandable by users because the base view comprises the answer to what the user selects as the basis for a report.
Based upon the base view and an embodiment of the invention, a user may select fields associated with any view of the relational abstraction, and may follow relations within the relational abstraction to select additional fields. As the user follows relations within the relational abstraction, the fields displayed are constrained by the base view and the relation path from the base view. Users thus have fields displayed for selection that are appropriate to answer the question associated with the base view. As the user selects fields, a report is generated which includes the selected fields and drill links associated with the fields.
The present invention defines a data store in terms of a relational abstraction. The relational abstraction generally parallels the entity-relationship inherent in a well designed transactional relational database. Doing so preserves the business logic associated with such transactional systems for use by users of the invention. However, one skilled in the art will readily recognize that an entity-relationship abstraction may also be applied to data storage systems that are not in the genre of traditional relational database management systems and do not have a traditional entity relationship structure.
In an embodiment of the invention, the relational abstraction is maintained in one or more extensible Markup Language (XML) files which comprise metadata which describes a data store. The relational abstraction includes views associated with the data store, scalar or aggregate fields associated with views and relations between views. View definitions identify tabular structures of rows and columns in the data store. Field definitions describe columns of data accessible in a particular view. Relation definitions describe associations between various views. Typically such definitions are associated with one or more tables and columns of a conventional relational database management system. However, one skilled in the art will recognize that any means of providing an entity-relationship view on data may be used as part of the invention and more than one data store may be represented in the relational abstraction.
A powerful benefit of the present invention comes from automatically and dynamically generating drills links within reports. As users build reports using the invention by selecting fields or following relations of the relational abstraction, the system maintains a relation path of all objects relative to a base view. The relation path maintains not only the relation of the objects to the base view but also the sequence of relations followed by the user relative to the base view. Only those fields that are logically available based upon the base view selected by the user and the relation path sequence followed by the user from the base view are available to include in a report. If a relation path sequence contains only to-one relations, scalar fields can be included. If a relation path sequence contains a to-many relation, aggregate fields can be included. If a relation path sequence contains a to-many relation followed by a one-to-one relation, distinct aggregates can be included.
Based upon the cardinality constraints imposed by the base view, the relation path, including the relation path sequence, and the relational abstraction, the embodiment determines if selected objects can be included in reports as drill links. Selected scalar fields whose relational abstraction definitions explicitly indicate that they can be included as drill links are automatically included as drill links. Selected aggregate fields are included as drill links because they inherently aggregate data. In an embodiment of the invention, even objects that are not explicitly defined in the relational abstraction as drillable links, can be defined by a user to be included as drill links in a report.
According to the invention, drill links can be included in reports of various formats. Such report formats may include well known formats such as HyperText Markup Language (HTML), Dynamic HyperText Markup Language (DHTML), eXtensible Markup Language (XML), Portable Document Format (PDF) and Scalable Vector Graphics (SVG). One skilled in the art will recognize that any report format that provides a facility to describe hyperlinks may be used by the invention.
According to the invention, a drill link may include information that provides additional powerful benefits to report designers and users. A drill link may include a reference to a second report. A drill link may include a reference to a web page that includes a second report. A drill link may include a reference to an object not associated with the relational abstraction, such as a web page, a graphic generation program or communications program. A drill link may include a reference to information about the report containing the link, including the location of the report, the location or context of the drill link within the report, or key values associated with other data contained in the report.
Another benefit of the invention comes from associating a drill link with another object of the relational abstraction and using the destination view associated with the last sequence in the relation path as a base view for a new report. The object associated with the drill link might be any object of the relational abstraction, including a scalar field, an aggregate field, an expression or a template. The template might include references to other objects in the relational abstraction. A destination view is a view derived by following a relation from a base view to another view within the relational abstraction. Another powerful benefit of the invention derives from using the extracted information to apply a filter or restrict the data returned in the second report. If the drill link includes information about a scalar field in the first report, the second report might return only data of the second report containing the contents of the scalar field of the first report. According to the invention, many pieces of information may be included in the drill link and used to apply filters or restrict data returned in the second report.
That the invention improves over the drawbacks of prior database access and report applications and accomplishes the advantages described above will become apparent from the following detailed description of preferred embodiments and the appended drawings and claims.
Other features and advantages of the present invention will be apparent from the following Detailed Description taken in conjunction with the accompanying Drawings, in which:
The present invention may be embodied in a computer database access and reporting system that displays selected database data based upon base views, and the fields and relations associated with those base views and generates reports using selected fields. Selected database data is displayed on a display surface according to row, column, summary and group criteria chosen by a user. The display surface is typically an active window on a display device of a simple application program, but the display surface may alternately be a window of a web browser or any application program operable for displaying and manipulating data. The display surface is typically a monitor, but may alternately be a printer, flatscreen LCD display, television, and so on.
In one embodiment of the invention, a computer application includes a Query Construction Window 130 as depicted in
Alternative embodiments may also use more drop areas, or a single drop area, or any other means of displaying fields and relations. Alternate embodiments may also change the shape of the drop area display items to fit various displays; for example, the drop areas may be round, square, triangular, or a custom shape as needed, or may be located in a pull-down menu or in some other type of user interface configuration. For example, the drop areas may be located in combined windows on the display screen, or may be represented by icons or buttons rather than blank fields.
A user may also add columns to a report by selecting fields. Referring to
A user may also add row groupings to a report. To add row groupings, a user selects a group field from the list of fields in the Recursive Tree Structure 146 and drops the field in the Group Drop Area 142 by initiating a drag-and-drop command, or, provided the Group Drop Area 142 is active, by double-clicking the desired field, or by clicking the Arrow-Transfer-Button 156. The system captures this action by adding the field to the list of row groupings and by displaying the selected field name in the Group Drop Area 142.
A user may also add numeric summary or aggregation measures to a report. To add measures, a user selects an aggregation or measures field from the list of fields in the Recursive Tree Structure 146 and drops the field in the measures drop area by initiating a drag-and-drop command, or, provided the measures drop area is active, by double-clicking the desired field, or by clicking the Arrow-Transfer-Button 156. The system captures this action by adding the field to the list of measures and by displaying the selected field name in the Measures Drop Area 144.
Likewise, a user may add fields from related database views to a report. Typically, the Recursive Tree Structure 146 will include a list of relations defined in the relational abstraction. To add fields from a related view, a user initiates a double-click command on a relation. The system will respond by replacing the previously existing list of fields and relations in the Recursive Tree Structure 146 with a new list based upon the selected relation and the cardinality existing between the base view and the destination view of the relation. The user may then add fields from the Recursive Tree Structure 146 to the Column Group Area 143, Group Drop Area 142 or Measures Drop Area 144, as noted above.
As a user select the desired fields to be grouped and displayed in a report, a system implementing the invention determines if the fields should be included in the report as drill links. If a definition of a selected field in the relational abstraction flags the field as drillable, a drill link is included in the report definition. If the field is an aggregate field, a drill link is included in the report definition.
Once a user has selected the desired fields to be grouped and displayed in a report, the user may choose to view a corresponding report. In the present invention, the list of fields and relations displayed for selection by a user is based upon a base view. Typically, the system will retrieve a list of tables and views from a database server and display them on a display surface. A user may then select one of the tables or views. Based upon the user's selection, the system will display a relational abstraction of all tables, views, fields and relations of the selected database table using the base view as a starting point.
In an embodiment of the invention, when a drill link in a report containing drill links is selected, the drill request is handled by loading the report definition from which the link originated, determining the destination view of the drill link that was selected, creating a new report based upon a template for the destination view, constructing a filter for the new report that incorporates the grouping keys or the primary filter of the first or originating report and displaying the second report.
As used herein, a “user” refers not only to a person using the present invention, but also to a program, application, operating system, function call, or any other entity that may make use of the present invention. Thus, an operating system that manipulates or otherwise employs the present invention is classified as a user.
With reference to
Typically, the Desktop Computer 1 includes a Processing Unit 6, System Memory 7, and a System Bus 8 that couples the System Memory 7 to the Processing Unit 6. The System Memory 7 includes Read Only Memory (ROM) 9 and Random Access Memory (RAM) 10, and a Basic Input/Output System (BIOS) 11 that contains the basic routines that help to transfer information between elements within the Desktop Computer 1, such as during start-up, and the ROM 9. The Desktop Computer 1 further typically includes a Hard Disk Drive 12. The Hard Disk Drive 12 is connected to the System Bus 8. The Hard Disk Drive 12 and its associated computer-readable media provide nonvolatile storage for the Desktop Computer 1. Although the description of computer-readable media above refers to a hard disk, it will be appreciated by those skilled in the art that other types of storage devices and media that are readable by a computer, such as a removable magnetic disk, a CD-ROM disk, a magnetic cassette, a flash memory card, a digital video disk, Bernoulli cartridge, and the like, may also be used included in, or attached to, the Desktop Computer 1.
A number of program modules may be stored in the Hard Disk Drive 12 and the RAM 10, including an Operating System 13, one or more Application Programs 14, a Web Browser Program 15, and Program Data 16. These program modules include a Data Query And Reporting User Application (DQR Application) 100 configured for implementing an embodiment of the present invention. A user may enter commands and information into the Desktop Computer 1 through conventional input devices such as a Keyboard 17 or a pointing device such as a Mouse 18. Other input devices (not shown) may include a pen, touch-operated device, microphone, joystick, game pad, satellite dish, scanner, or the like. A Display Device 19, such as a display screen, is also connected to the System Bus 8 via an interface. In addition to the Display Device 19, desktop computers typically include other peripheral output devices (not shown), such as speakers, scanners or printers.
Application Server 2 and a Database Server 3 may be personal computers, minicomputers or mainframe computers, or another common application platform, and may also include many or all of the elements described relative to the Desktop Computer 1. Typically, the logical connections depicted in
When used in a typical networking environment, the Desktop Computer 1 is connected to the LAN 22 through a Network Interface Card 25. When used in a WAN networking environment, the Desktop Computer 1 typically includes a Modem 26 or other means for establishing communications over the WAN 24, such as the Internet. The Modem 26, which may be internal or external, is connected to the System Bus 8. In a networked environment, Application Programs 20, or portions thereof, may be executed on Application Server 3 and stored in the server memory and storage devices. These application programs include a Data Query And Reporting Query Generation And Database Interface Application (Query Engine) 200 configured for implementing an embodiment of the present invention. Typically, the Query Engine 200 also includes an intermediate mapping or metadata layer that is used when communicating with a database server. It will be appreciated that the network connections shown are exemplary and other means of establishing a communications link between the computers may be used.
In the present example metadata, a “Customer View” Table 40 depicts a mapping between the sample Northwind database described in
According to one embodiment of the present invention, instances of the objects described in
As shown in
Table 72 of
As shown in
A Table 83 of
The present invention provides means to interactively and iteratively display fields for selection.
In the depicted embodiment, views may be organized into subfolders, such as a Lookup Folder 122. This facility is provided for databases having a large number of defined views. In the depicted embodiment of the present invention, the base view is set by selecting a view from a List 123 and either selecting a Finish Button 124 or double-clicking on the selected view. This base view, in conjunction with the associated metadata described in
A relational abstraction of the present invention may be exposed through a display system and according to rules of the invention enforcing proper display and element selection.
A Group By Box 139, a Details Box 140 and a Measures Box 141 are standard label boxes. The boxes below the Group By, Details and Measures areas of the display window, numbered respectively 130, 131 and 132, are drop box areas where a user may drag or locate fields when building queries. Note that in the depicted embodiment, a Details Drop Box Area 143 has been selected by default, as denoted by the darker gray colored background surrounding the Details Label 140. With such selection, a user may select from a Selection Area 146 one or more fields from a Field Group 147 that pertains to the Employee View 49, which is the view based upon the Employees Table 31 of the
Once a relational abstraction of a data store has been created, fields may be displayed for selection. In Step 184, a view is selected as the base view of a report. In step 185, scalar fields and relations of a view are displayed. In step 186, the relation path of the view is analyzed and a relation path sequence is extracted. According to step 187, if the relation path is empty, the scalar fields and relations associated with the base view are displayed. If the relation path is not empty, processing continues to step 188, where it is determined if the relation path sequence contains a to-many relationship. According to step 188, if the relation path sequence does not contain a to-many relationship, the scalar fields and relations associated with the destination view are displayed. Also according to step 188, if the relation path contains a to-many relationship, processing proceeds to step 189.
In step 189, the system determines if another relationship follows the to-many relationship of step 188 in the relation path sequence. If another relationship does not follow the to-many relationship in the relation path sequence, aggregate fields and relations associated with the destination view are displayed according to step 190 and processing returns to step 186. If another relationship follows the to-many relationship of step 188, processing proceeds to step 191 where a determination is made about what type of relation follows.
In step 191, if a to-many relation follows a to-many relation according to step 188, processing proceeds to step 192 where the aggregate fields and relations associated with the destination view are displayed. According to step 192, processing proceeds recursively to step 193 where the relation path sequence is again determined and processing returns back to step 191. If in step 191 a to-one relation follows a to-many relation according to step 188, processing proceeds to step 194. According to step 194, if the relation path sequence includes a to-many relation followed by a one-to-one relationship, distinct aggregate fields and relations associated with the destination view are displayed according to step 195 and all additional relation path sequence additions will display distinct aggregate fields.
If in step 194 the relation path sequence does not include a to-many relation followed by a one-to-one relation, the aggregate fields and relations associated with the destination view are displayed according to step 192 and processing proceeds recursively for each addition to the relation path.
In step 202, the DQR Application 100 displays the name of the Northwind database described in
In Step 203, a user sequentially selects the Employee View 49 from the View Group List 123 and the Finish Button 124, which causes the YES branch of Step 203 to be followed. If a user does not select a view, the NO branch of Step 203 is followed and the DQR Application 100 continues to display the Window 96B of
In Step 204, the DQR Application 100 requests the fields and relations listed in the Employee View 49 from the Query Engine 200. In Step 205, the DQR Application 100 then displays the list of fields and relations of the Employee View 49 on the Display Device 19, displaying the Window 130 described in
In Step 206, the DQR Application 100 awaits user input in the form of selecting fields, such as those highlighted by the Balloon 147, or relations, such as those highlighted by Balloon 149, in
In Step 208, the DQR Application 100 monitors detail field selections. If a user does not select a detail field, the DQR Application 100 continues through the NO branch to Step 209. If a user selects a detail field, the DQR Application 100 proceeds through the YES branch to Step 212. In Step 212, the DQR Application 100 adds the name of the selected field to the Details Drop Box Area 143, and continues to Step 205 to display the fields and relations associated with the selected view.
In Step 209, the DQR Application 100 monitors group field selections. If a user selects a group field, the DQR Application 100 proceeds through the YES branch to Step 213. If a user does not select a group field, the DQR Application 100 continues through the NO branch to step 210.
In Step 210, the DQR Application 100 monitors measure field selections. If a user selects a measure field, the DQR Application 100 proceeds through the YES branch to Step 214. If a user does not select a measure field, the DQR Application 100 continues through the NO branch to Step 211.
In Step 211, the DQR Application 100 monitors the selection of relations. If a user selects a relation, the DQR Application 100 proceeds through the YES branch to Step 215. If a user does not select a relation, the DQR Application 100 continues through the NO branch to Step 205.
In Step 215, if the cardinality of the relation path ending with the selected relation is to-one, the DQR Application 100 follows the NO branch of Step 215 to Step 216. In Step 216, the DQR Application 100 retrieves the fields and relations associated with the followed relation and processing passes to Step 205. If the cardinality is to-many, the YES branch of Step 215 is followed to Step 217 where the DQR Application 100 limits retrieval of the fields associated with destination view to those fields that have a Field Type Property F07 (see
In Step 218, if at least one field has been added to the report, the YES branch is followed to Step 220, where the DQR Application 100 verifies and generates a suitable database query and displays the report on the Display Device 19. In Step 221, if the Fields Button 134 is selected, the YES branch is followed to step 205. Otherwise the process terminates.
Steps 250 through 253 display the steps of defining a relational abstraction of a data store. In step 251, views of the relational abstraction are defined. View definitions typically include the objects described in Table 51 of
These default fields are used in an embodiment of the invention, together with the relational abstraction definitions for other objects to provide a default drill template report when elements of Customer view 40 are included in a report.
In step 252, fields of the relational abstraction are defined according to certain object properties such as those described in Table 52 of
In step 253, relations of the relational abstraction are defined according to the detailed discussion provided above for
Steps 255 through 261 display the steps of generating a report that includes drill links. In step 256, a view is selected. The selected view could be any view defined in the relational abstraction. In step 257, the relationship between the selected view and the base view of the report is determined. According to an embodiment, the relationship is maintained as part of a relation path. The relation path maintains all relations traversed in building a report, and includes the sequence of relations followed. Although typically selecting a view occurs through user-program interaction through a display device, one skilled in the art will recognize that any means of selecting a view might be used according to the invention.
In step 258, objects associated with the view are selected or relations associated with the view are followed. As objects are selected, a decision is made whether to build a drill link for each selected object. In step 259, if the object definition contained in the relational abstraction is flagged as drillable, processing proceeds to step 260 where information about the drill link is included in the report. Alternatively, the selected object may be flagged at runtime as drillable by a user. If a selected object is not flagged as drillable in the relational abstraction or by user input, processing proceeds to step 261 where the object type of the selected object is determined. If the object type is inherently drillable, such as an aggregate field, processing proceeds to step 260. Otherwise, a drill link is not included for the selected object and processing returns to step 256.
Steps 275 through 281 display the steps of following drill links in a report. A drill link is processed starting at step 276. In step 276, a report containing a drill link is selected. This selection will typically be made through displaying a report name on a display device and selecting the report using keyboard or mouse interaction. However, one skilled in the art will recognize that any means of selecting a report containing a drill link may be used according to the invention. In step 277, a drill link contained in the report is selected. Again, one skilled in the art will recognize that any means of selecting a drill link may be used according to the invention. In response to selecting a drill link, the report definition containing the drill link is loaded.
In step 279, processing continues where the relation path associated with the view of the selected object, or destination view, is determined. The relation path will contain a sequence of one or more relations from the base view that was selected when the originating report was created. The relation path sequence may be empty if the destination view is the same as the base view.
In step 280, a new report is created using the destination view and the drill link information. According to the invention, a new report is created, meaning that the destination view becomes the base view for the new report. The new base view and the drill link information are combined to create the new report, the new report containing objects defined in the relational abstraction for the base view. Typically, the new report is also filtered using the filters applied to the originating report and the object underlying the drill link.
The rows below the #Orders Column Heading 307 are based upon the Orders Field 53 depicted in the Orders View 47 of
As discussed above, the Mycustomerreport Report 301 includes five columns, which represent fields in the Northwind database. Of the five fields, two of them, the company name field, as identified under the Company Name Heading 303, and the postal code field, as identified under the Postal Code Heading 306, have Field Object Property F06 of Table 52 (see
In the present embodiment, the information contained in the report definition for the Mycustomerreport Report 301 for the Count of Orders Drill Link 308 is as follows:
The link information describes if the link can be drilled, identifies the ID of the order count field and includes grouping keys for customers and country. According to the invention, a drill link may contain various pieces of information that may be useful in generating a report.
However, the Allow Drill Checkbox 341 of
It will be obvious to those of skill in the art that the invention described in this specification and depicted in the FIGURES may be modified to produce different embodiments of the present invention. Thus, the present invention has several advantages over the prior art without sacrificing any of the advantages of the prior art. Although two embodiments of the invention have been illustrated and described, various modifications and changes may be made by those skilled in the art without departing from the spirit and scope of the invention.
This application is a continuation-in-part of co-pending U.S. patent application Ser. No. 10/627,180 filed on Jul. 25, 2003 entitled “Method and System for Building a Report for Execution against a Data Store.” This prior application is incorporated herein by reference.
Number | Date | Country | |
---|---|---|---|
Parent | 10627180 | Jul 2003 | US |
Child | 10765232 | Jan 2004 | US |