Reporting tools

[0001] This application claims priority from U.S. Provisional Patent Application Ser. No. 60/621,322, filed Oct. 22, 2004, the entire content of which is incorporated herein by reference.

[0002] This invention relates generally to software reporting tools and, in particular, to reporting tools having improved filtering, display and alerting methodologies offering enhanced versatility.

[0003] Broadly, reporting tools retrieve information about something from a database, and create “reports” allowing a user to organize data so that it can be more easily understood by a layperson. For example, raw or unformatted data may be presented as a graph or a chart.

[0004] Currently, most reporting tools use various filtering methods to query a database, run outputs on the resulting data set, and display the information. One approach asks the user to manually enter a range of data to query in the database. This typically requires the user to have understanding of Boolean, SQL, and databases. An alternate approach limits the user to pre-existing choices for the setup of filter-relationships, outputs, and display types through a “wizard” type setup process. Though seemingly more automated, this may limit power or flexibility.

[0005] This invention relates to reporting tools having improved filtering, display and alerting methodologies offering enhanced versatility. According to one aspect of the invention, the method has an independent data filtering mechanism, an independent output function that populates an n-dimensional array, and an independent display function.

[0006] According to a different embodiment, a set of parameterized query relationships organizes a series of data filters into a graphical representation of a parent/child hierarchy in order to execute a series of hierarchical queries where each simple query builds a final query that is complex.

[0007] According to yet a further alternative embodiment, Boolean logic is used in a series of n-number hierarchical filters, and a combination of n-number outputs, to generate notification in the form of emails/messages/etc. based on data that is dynamically entered into the system.

[0013] This invention is directed to software reporting tools having improved filtering methodologies offering enhanced versatility. At least three patentably distinct aspects of the invention are described in this specification. These aspects, which may be used independently or in any combination, are as follows:

• [0014] 1. A method having an independent data filtering mechanism, an independent output function that populates an n-dimensional array, and an independent display function;
• [0015] 2. A method of parameterized query relationships that organizes a series of data filters into a graphical representation of a parent/child hierarchy in order to execute a series of hierarchical queries where each simple query builds a final query that is complex; and
• [0016] 3. A method that uses Boolean logic in a series of n-number hierarchical filters and a combination of n-number outputs in order to generate notification in the form of emails/messages/etc. based on data that is dynamically entered into the system.

[0017] The following headings correspond to these individual components.

[0018] In existing reporting tool packages, the concept of filters, outputs, and display are the same. Current packages typically execute a parameterized query on the database to get the useful information. The output and display of the query are generally hard-coded into the program in order to display the data according to what is useful at the time the program was coded.

[0019] In contrast, according to this aspect of the invention, a filter limits the domain population to a subset of the entire data set. An output is an n-dimensional range result with a given domain input, and the output report is a combination of a filter and an output used to generate a desired display, such as the pie chart of FIG. 1. Other outputs are certainly possible, including two-and three-dimensional bar charts, graphs, and so forth.

[0020] Within this application, a filter controls the scope and scale of data returned through a database query. The filters are user-definable and user-configurable. Users create filters into a hierarchical structure to optimize and refine data query returns. This structure creates filters through user-defined parent-child relationships. These relationships enable data queries to pass through one or many filters, producing outputs refined by the user's selections. Each child filter limits the domain based on the parent filter's output. The Filter tree can grow and expand as required, resulting in a dynamic and flexible reporting system.

[0021] By definition, an output is an n-dimensional range result with a given domain input. Within this application, an output analyzes and quantifies the results returned from a filtered data query. A significant feature of this application is the fact that the output generated from the filtered data is completely separate from the filters themselves. When a report is created, users select the filter through which the data query will pass. Users must also select the Output Type, which specifies how the data returned from the query will be analyzed and reported.

[0022] According to this embodiment of the invention, a user first defines the subset of data that the report should be created based on (1 to n filters). Second, they define how that subset of data should be analyzed/calculated, and third, they define the type of display that should be used to display the calculation/analyzed result set (output). The output is saved in an n-dimensional array, which will be used by the display object.

[0023] This methodology has been implemented by architecting the database tables to store and update the data created by filters in a subset of tables. The software based off of these set of filter tables performs the selected calculations to create the output. The output result set is stored in a set of output tables and is sent to the display object to the display the final report. Each filter is derived from its parent filter and has a its own subset of data stored in the database, which enables the user to associate any output type to that filter.

[0024] The process according to this aspect of the invention is unique in making the outputs generated from the filter data completely separate from the filters as well as the display function. For each parameterized filter that is executed, the user can choose an output AND a display. The user can make changes to the filter, the output, and/or the display at any time during program execution. Stated differently, a user can join any output with any filter into any display they choose.

[0025] There are many advantages to this approach. Having completely independent filter, output and display functions allow for greater data-mining flexibility. The design allows the user to mix and match filters, outputs, and displays without having to create a new build of the software.

[0026] FIG. 2 is a diagram that depicts a specific example according to this aspect of the application. Database 202 contains the test results for vehicles tested over the past month at a particular assembly plant. User-configurable filter query 204 in this case applies 4 filters of the data as follows:

• • [0027] Tested last month (time range filter applied)
  • [0028] Having all wheel drive (option content filter applied)
  • [0029] Performed testing at Station 1 (test station information filter applied)
  • [0030] Testing resulted in failure (test station information filter applied)

[0031] At 220, the results are analyzed as a function of output type which, in this case, is defined as “First Time Through by Operator.” At 230 the output is organized into a display type, also selected by the user, resulting in a vertical bar graph for Operator 1, Operator 2, and Operator 3. The numbers in the bars indicate particular individuals. Note that at any time, the user may change any item in this example and have the report dated to their liking (i.e. changing the display to a pie chart).

[0032] In many existing reporting tools, the querying of the database is executed as one large parameterized query. Usually the user will have to step through a wizard and pick certain options to get the query they desire. In contrast, this aspect of the invention uses parent-child relationships to organize the filters into a hierarchical series of simple queries (Hierarchical Graphical Querying). Each new child filter limits the domain based on the parent filter's output. This design is very ergonomic to a user having little knowledge of how to write SQL query statements. The data relationships are inherent to the design. FIG. 3 illustrates one example of the how the filters relate with each other.

[0033] In terms of advantages, this design allows for better management of data with less hardware (i.e. Web servers). The queries are smaller, and therefore the load on the server that houses the data set is lowered.

[0034] This embodiment uses a hierarchical filter structure to enable and optimize data query flexibility. This structure could be comprised of a combination of administrative and user-defined filters, which combine to form parent-child relationships. Such relationships enable data queries to pass through one or many filters, producing outputs refined by the user's specifications. Each child filter limits the domain based on the parent filter's output. This structure can grow and expand as required, resulting in a dynamic and flexible reporting system.

[0035] This concept is presented to users via a graphical interface. As such, users can create a desired structure by selecting one to n filters in a parent-child relationship to establish the preferred subset of data. This graphical interface enables users to dynamically build complex database query statements without knowing SQL or without having to modify the software.

[0036] This methodology is implemented by architecting the database to store data for each specified filter; this filtered data is queried off the data belonging to its parent filter. The software queries the database and displays the saved filter data in a graphical format. Using this graphical interface, users add/modify desired filter(s). Based on the chosen filter criteria and the data belonging to the parent filter, the software creates a new subset of data for the new filter to be stored in the database. Since each filter has its own subset of data and a specified parent, users can add new filters to any existing filters or remove any unwanted filters.

[0037] A further aspect of the invention utilizes a unique alerting system that the user can set up to send alert message via a chosen means of communication such as an e-mail, buzzer, pager, light, etc. The type of alert may result from the analysis of a set of “n” outputs and filters using macros and Boolean logic to produce a desired indication when true.

[0038] A typical approach would have the alert be a separate data query that executes on an output or filter with a particular frequency. The user would generally input this query by answering hard-coded questions provided by the software (i.e., a wizard). The method used in this application further utilizes the outputs that are already created to inform the alerting system. Therefore, users are able to select one or a combination of existing outputs to generate an alert. When creating a new alert, users select the desired output(s) and select a Boolean equation to be executed on the output(s). If the result set of the Boolean equation is true, an alert will be sent. When a filter and an output are combined, an n-dimensional array result set is created. This array will be analyzed using a Boolean String, which will generate a true/false result. The Boolean logic can be executed against one or many n dimensional arrays. This result of the Boolean expression will determine if the alert should be issued.

[0039] To implement the methodology the following items will be stored in the database to save the user specified alerts: the Boolean equation, frequency interval, and the selected outputs. Since the output data is already stored in the database, the equation will be evaluated at the interval inputted by the user, once the equation becomes true the alert is sent. An example of an alert based off of one output is represented in FIG. 4.

[0040] One advantage to this aspect of the invention is that a user has a very flexible approach to setting up alerts. The advantages to using the outputs/reports to generate the alerts is that it enables the user to execute very complex SQL statements on the database without having to know SQL commands or anything about databases.

[0041] The example illustrated in FIG. 5 shows a process involved in creating an alert associated with an output of “First Time Through by Operator” for the filtered data for all wheel drive vehicles that failed at Station 1 in the past month. The user creates an analysis statement for the output that directs an alert to be sent whenever two operators (i.e., Ed AND Bob) fail more than 2 cars.