WO1991012582A2

WO1991012582A2 - Method and apparatus for processing tabular responses to computer database queries

Info

Publication number: WO1991012582A2
Application number: PCT/GB1991/000199
Authority: WO
Inventors: Andrew Nelson
Original assignee: Hewlett-Packard Company
Priority date: 1990-02-08
Filing date: 1991-02-08
Publication date: 1991-08-22
Also published as: WO1991012582A3; GB9002877D0; EP0472682A1

Abstract

A computer based data query system produces numerical answers in tabular form. The invention provides that the table can be converted to a calculator pad with calculator functions enabled to be applied to the table columns and rows to add, subtract or multiply, etc. table values.

Description

Method and Apparatus for Processing Tabular Responses to Computer Database Queries

The invention relates to a method and apparatus for processing tabular responses to computer database queries. The implementation of mouse-driven computer applications in a windows environment has materially simplified the general use of computers. Facility with a keyboard and knowledge of computer languages are no longer necessary. These benefits are felt particularly at managerial level and here it is important to be able to derive database information for monitoring and decision making purposes. Formulation of queries is an important managerial function.

Database interrogation depends heavily on the logical formulation of queries and special text-based languages have been developed as a consequence. An alternative graphical system of interrogation is desirable. We have developed a graphical system of database interrogation where the database has a number of records and a number of dimensions in which each record is represented, the dimensions include headings and the method consists in providing a blank elementary select table, adding user-determined row and column headings for the table from those available in the dimensions, thereby defining rows and columns and empty cells at the crossings of the rows and columns, and executing a program which fills in the cells with data corresponding to the set intersections of the row and column headings . The records may be records of quantities and the cells are then filled with figures representing perhaps total values for the various intersections. Alternatively the answers in the cells may be the total numbers of the records for each intersection. The present invention proposes an improvement in operating on a tabular numerical query answer.

According to one aspect of the invention there is provided a method of obtaining information from the answer of a computer database query, which answer is displayed in the form of a table of numbers, the table having rows and columns, the method consisting in executing a conversion program which converts the table display to the form of a calculator and pad display where arithmetical calculator functions are designated and the rows and columns of the table each have an identifier, the method further consisting in executing a calculator program which allows selection of column and row identifiers and calculator functions and produces one or more further rows and columns in the pad display which represent the execution of the selected function on the selected rows and columns. While it is possible to enter calculator functions and row and column selectors textually by keyboard, it is preferred that the computer operates in a windows environment and input is derived from a mouse, wherein a calculator representation is displayed to include function buttons on which the mouse may be clicked to select functions.

Preferably the calculator program allows the further rows and columns added by virtue of calculations to be themselves selected as the subject of further calculations.

According to another aspect of the invention there is provided apparatus for performing the above method comprising a computer programmed (a) to derive an answer to a database query in the form of a table display of numbers having rows and columns; (b) to convert the table display to a calculator and pad display where arithmetic calculator functions are designated and the rows and columns of the table each have an identifier, and (c) to allow selection of column and row identifiers and calculation functions and to produce one or more further rows and columns in the pad display which represent the execution of the selected function on the selected rows and columns.

The invention will further be described with reference to the accompanying drawings, of which:-

Figure 1 is a schematic view of apparatus in accordance with the invention comprising a programmed computer with a database;

Figure 2 is a diagram of the screen presentation for implementing the method of the invention with the apparatus of Figure 1 ;

Figure 3 is a diagram of a calculator and pad display derived in accordance with the present invention from the select tables of Figure 2;

Figure 4 is a diagram illustrating the application of the calculator program to the pad display of Figure 3 Figure 5 is a diagram illustrating the contents of a record in the database of the apparatus of Figure 1; and

Figure 6 is a diagram illustrating the bit string comparison technique employed in the preferred method in accordance with the invention.

Referring to Figure 1 there is shown a general purpose computer conveniently of the personal computer (PC) kind and preferably part of a network of similar machines, although this is not illustrated. The computer 1 has a screen 2, and a mouse 3, a keyboard 4 and a hard disk 5. The computer runs under a Windows environment and the mouse is used in conventional manner to select items from pull-down menus and by reference to icons. A database is stored on the hard disk 5. In this illustration the database is for the domain of financial planning over a given time period in an organisation which has several projects running and which incurs expenses of several kinds. Thus, the database has a set 6 of records, each of which has a record number, an expense value and fields which give location and other information. A more detailed description of a record will be given later.

In addition, the database stored on the hard disk 5 has five dimensions into which each record falls. Each domain has a tree structure with nodes and branches. The five dimensions are: STATUS; ORGANISATION; EXPENSE TYPE; TEMPORAL; and LOCATION.

STATUS DIMENSION The status dimension has two primary nodes;

DYNAMIC and FIXED. The DYNAMIC node branches to two further nodes: COMMITTED and ACCRUED. The FIXED node branches to nodes TARGET and GL. Each node represents a status of an expense (record). The status of an expense will change as it passes through the system.

ORGANISATION DIMENSION

The organisation dimension has a node AIM which branches to project nodes CROMWELL; TOCCATA; HARPO and DM. These nodes represent projects to which the expenses are assigned.

EXPENSE TYPE DIMENSION

The TYPE node branches to SOFTWARE; CONSULTANCY; RENT and TRAVEL. These are the types of the expenses. TEMPORAL DIMENSION

This is a calendar dimension having a node FY90 (Financial Year 1990) which branches to "quarter" nodes Ql; Q2; Q3 and Q4. The quarter nodes branch to respective "month" nodes: J; F; M; etc. LOCATION DIMENSION The LOCATION node branches to system location nodes to where the expense has been assigned: MAILBOX; MYDATA; and BIN.

Referring now to Figure 2 there is shown a screen presentation typical in the execution of the invention. The screen has multiple windows. At the left-hand side is a set of windows representing the dimensions described above: window 7 for the STATUS dimension; window 8 for the ORGANISATION dimension; window 9 for the EXPENSE TYPE dimension; window 10 for the TEMPORAL dimension and window 11 for the LOCATION dimension.

At the right hand side is a window 12 which shows a typical expense record with the name TRAVEL 1. At the top part of the record are shown the nodes of the dimension which the record occupies, namely TEMPORAL; JAN 90; STATUS; COMMITTED; ORGANISATION; HARPO; EXPENSE TYPE, TRAVEL and LOCATION, MYDATE. This information is stored in the record on the database. Other fields in the record gives the illustrated details of the expense and its value.

At the centre of the screen is a window 13 which has a free form query select table. The table in this illustration has been set up to interrogate the database by filling in column headings and sub¬ headings with LOCATION, MYDATA; TEMPORAL, Ql-09; STATUS; COMMITTED, ACCRUED and TARGET and by filling in the row headings and sub-headings with ORGANISATION, AIM, CROMWELL, TOCCATA, HARPO and DM. The table created is thus a 3 x 4 table with twelve cells at the crossings of the rows and columns. By clicking the mouse on EVALUATE, the program is triggered to fill in the cells with the totals of the values of the records which are the intersections of the sets dictated by the table. The column and row headings and sub-headings are selected from the node names of the dimensions by means of clicking and dragging with the mouse. Any combination can be selected and it is immaterial whether the headings are in rows or columns. From the illustrated column headings it will be seen that headings may be stacked - e.g. "Mydata"; "Ql-09" and the three status headings. The program evaluates the intersection of the sets thus defined and adds the values of the expenses to display them in the cells. If the database were essentially a non- numeral database then the cell would display the number of records in the intersection. The records could be listed by expanding the cell into another window or by scrolling in the cell, for example.

It will be seen that the display of Figure 2 represents a numerical tabular database query answer. Furthermore, there is a "Spreadsheet" button. If the mouse is clicked a "spreadsheet" then a conversion program is executed which converts the select table display of Figure 2 to a calculator and pad display of the kind shown in Figure 3. Here the table is displayed as a pad with distinct row and column identifier regions 60 and 61. Also there is a display 62 of a calculator keyboard. By clicking the mouse on particular row and column identifiers 60, 61 and appropriate buttons of the calculator display then a calculator program can be executed which performs the selected arithmetic function for example add, subtract, multiply, divide on the selected rows and columns . This produces additional rows and columns as appropriate where the answer is represented. For example, Figure 4(a) shows the display where the "Accrued" column is subtracted from the "Target" column to produce an answer column 65. Figure 4(b) shows the display where the "Cromwell" row is added to the "Toccata" row to produce an answer row 66. Input of Function

The function for a new row (or column) is entered by selecting a sequence of buttons from the calculator numerals and function and the row (or column respectively) heading identifiers. As each such button is pressed, the new component of the function is added to a stored list of previous selections. Selection of a row (or column) heading is stored as a reference to the number of that row (or column). Successive selection of components of an integer or floating point number are composed and stored as the number entered so far. Symbolic arithmetic functions are stored directly.

As each button is pressed, the syntax of the function previously entered together with the new selection may be checked. If the syntex is incorrect the new selection may be rejected and an error reported to the user. As the function is entered, the current complete functions may be displayed to the user as a text string, where the string is derived from the stored list of entries with the references to rows (or columns) replaced by their respective identifying labels. This text string may be presented in a scrolling text area with the most recent entry visible on the right of the area, and the left hand end of the string clipped to a fixed size display area. The entry of a function is terminated when either the 'equals' or 'clear' buttons are pressed. Referring now to Figure 5 there is shown the format of a record. The record has a number according to its address in the record file. This is not explicitly stored in the record itself. At the beginning of the record is a text field 16 for record type, a number field 17 for record value, and then follow a locator field 18 and free fields 19. The locator field has five address corresponding to the nodes which the record occupies in the five dimensions. Thus, for the example of the record at 12 in Figure 2, the locator addresses would be for Ql-09 (TEMPORAL); Committed (STATUS); Harpo (ORGANISATION); Travel (EXPENSE TYPE) and Mydata (LOCATION). The free fields 19 carry the detailed information including, specifically the numerical values on which arithmetical operations may be required.

As the appropriate data is entered so the dimensions leaf nodes are updated. Each dimensions leaf node carries a record of the record numbers appropriate to that node. This information is stored in a particularly useful way, as illustrated in Figure 6. Here there is a node list which is a field of successive bytes having a total number of bits corresponding to the total number of records in the database. If a particular record is appropriate to the node then the respective bit number of the node list bit string is turned on (status 1). If not it has status 0.

As successive headings and sub-headings are selected for the query table, the respective node bit strings are operated upon to produce further bit strings. Thus, if a higher level heading such as Ql is selected, the appropriate leaf node bit strings (JFM) are ORed to produce an intermediate union bit string. Any such intermediate bit strings are finally ANDed with each other and with leaf node bit strings as appropriate to produce further bit strings which represent the records belonging to the intersection of the node sets. This process is repeated for successive selections and it will be seen that in this way bit strings representative of the record sets for the cells of the table can be rapidly derived. It is then a simple matter to scan the values of the records for insertion of the totals in the cells. As an illustration, Figure 6 shows a first node bit string 20 with records 1, 3, 6, 7 and 8 present; a second node bit string 21 with records 2, 3, 5, 6 and 9 present and the intersection bit string 22 of these with records 3 and 6 present. The numerical values on which arithmetical operations are performed are derived directly from the record bit strings by the computation program. The mechanisms for operation on these numerical values and storing the new values will be understood. The calculator program is such that the answer rows and columns themselves may be the subject of selection to allow forth arithmetic functions to be performed and further answer rows and/or columns produced.

Claims

1. A method of obtaining information from the answer of a computer database query, which answer is displayed in the form of a table of numbers, the table having rows and columns, the method consisting in executing a conversion program which converts the table display to the form of a calculator and pad display where arithmetic calculator functions are designated and the rows and columns of the table each have an identifier, the method further consisting in executing a calculator program which allows selection of column and row identifiers and calculator functions and produces one or more further rows and columns in the pad display which represents the execution of the selected function on the selected rows and columns.

2. A method as claimed in Claim 1 which operates on a computer in a windows environment and input is derived from a mouse, wherein a calculator representation is displayed to include function buttons on which the mouse may be clicked to select functions.

3. A method as claimed in Claim 1 or Claim 2 wherein the calculator program allows the further rows and columns added by virtue of calculations to be themselves selected as the subject of further calculations .

4. Apparatus for performing the method of any of claims 1 to 3 comprising a computer programmed (a) to derive an answer to a database query in the form of a table display of numbers having rows and columns; (b) to convert the table display to a calculator and pad display where arithmetic calculator functions are designated and the rows and columns of the table each have an identifier, and (c) to allow selection of column and row identifiers and calculation functions and to produce one or more further rows and columns in the pad display which represent the execution of the selected function on the selected rows and columns.