CA2151654A1

CA2151654A1 - Graphical display method and apparatus

Info

Publication number: CA2151654A1
Application number: CA002151654A
Authority: CA
Inventors: William D. Devlin; Louis Mau
Original assignee: IBM Canada Ltd
Current assignee: IBM Canada Ltd
Priority date: 1995-06-13
Filing date: 1995-06-13
Publication date: 1996-12-14

Abstract

The invention herein provides report generating apparatus for interactively constructing a graphic image on a data processing display system. It includes display generating means for displaying to an operator an information entry form into which the operator can insert information including a number of data fields for data entry, identification for said fields, and data in the fields. It also can provide storage for storing the information in a table in relational database storage. Report request display apparatus is provided for displaying a report request form and allowing the operator to select information categories from said data fields for display, as well as selection means for selecting data from said database storage means corresponding to the selected information categories.
Report display means may be provided for displaying a report in response to a request by the operator depicting the relationship of data in the selected categories, wherein the report displayed can be a graphical image having distinguishable portion(s) therein corresponding to the selected data. Means are provided for responding to a detected request for a selected portion of the image from an operator for further detail for a selected portion of said report, and for displaying to the operator a report refinement menu of selectable categories related to the data fields. Display refinement selecting means upon detecting the selection of a selected category from the report refinement menu, is adapted to select further data from the corresponding field of the table in the database storage related to the data in the selected portion of the report for displaying the further or additional data in a second report, where the second report may include a graphical image derived form said further data.
The report displays the further data in relation to the selected categories.

Description

.6 5 ~

GRAPHICAL DISPLAY METHOD AND APPARATUS
Field of the Invention This invention relates to the display and use of data stored in a database and a method and apparatus for refining the display of corresponding data using graphical techniques.

Background of the Invention In previous database products which allow a user to present information in forms like a chart, generally at least two things have to be done to create a chart from raw data in a database.
The first thing is to access the database file or table containing the data with a query. A query is really a question put in a technical specification recognized by a database system to retrieve data from a database. For example, a question might be "Find all the wines in the database or find all the wines for the Burgundy region or find all the wines from Burgundy region and the quality 5. The query is a question formulated in a very technical specification which is input to the computer operating the database system. The computer processes the query and sends back raw data to the user, as specified in the query.
There are different ways of making a query. It can be coded much like you would code a computer program as a textual specification in a query language like structured query language (S~L) or it can be formulated in some graphical or menu driven way through a computer program, prompting the user by menus to construct query language instructions.
The query is used to select data and specify how to present it so a lot of raw data present in the database can be reduced for presentation to the user. Frequently this data is presented to the user in a table. The second step in using a database product to construct a graph in a database would be to define a chart to graph that filtered data from the result of the query and in general it is done in two independent steps: Step 1, The user defines the query then receives some data; Step 2 graphing that data. So if the user wants to look at that chart and to refine the chart down a bit, perhaps because in graphing a pie CA9-9~-009 chart it is seen that one sector is huge and is overwhelming the other data points, while one is particularly small and would normally beg one's curiosity as to what's behind the data. Then what the user would do is basically start again, and go back and change the query specification, going right back to the raw data to perform a third step of formulating a new specification with additional or new characteristics or added filters, with the prospects of obtaining yet another set of filtered raw data. The user ends up doing something that has no relation to the chart from a user's point of view and senses that he is really just creating a new query rather than building on prior experience.
The prospects are quite time consuming and possibly uncertain from a user's point of view.

Prior Art U.S. Patent 4,611,306 relates to relational database access during the context of word processing in which a user can toggle between the display of a chart an a menu constructing it. The chart does not represent a query or provide the ability to refine the query in a systematic way.
The Microsoft Access Database software provides a natural language construction provision for guiding a user to produce a detailed query of a database in order to subsequently produce a graph of that information. The chart displayed does not represent a query or provide the user the ability to refine data.

Summary of the Invention It would be advantageous if a user could use a first database query to act as the basis for the development of query specifications on a subset of data being presented, and do it all without using query or computer language.
One aspect of the invention is using the chart itself as a way to define some of those specifications for the database query and present the data to the user with what would appear as a more natural interface.

In effect the chart presented to the user is a place holder for the user's previous selection that set up the original query, and if the user now wants to filter the results rather than going back to the original data he can do so by selecting a portion of the chart by clicking on a segment of the chart (or using an appropriate menu) to reveal what choices are present for the user to obtain additional data. The user's action is translated into steps technically manipulating the query specifications used to access the database information.
This invention provides a method and apparatus for the generation and display of graphical data representing numerical data, for instance stored in a database, and for refining the display of that data using graphical techniques in which the graphical display information is used as a metaphor for, or instead of, displaying a query for the purpose of refinement or display of additional data (which is referred to as "data drill down") to be displayed in graphically on a display, or other visual means such as printout. The drill down aspect of this invention can also be used to generate written reports in addition to or in lieu of the graphical output as will be recognized by those skilled in the art upon reviewing this specification.
The invention herein provides a simple systematic processing procedure for displaying and using a graphic image (a chart for instance), which allows a user to simply refine a database query (the one producing the graphic on display to the user) without knowledge of query construction.
The drill down method of the present invention provides a method of refining a query by mapping the user's interaction with a graphic chart display to the query used to construct the chart to obtain the objective of producing a subsequent chart displaying additional or more detailed data.
One aspect of the invention provides a process for interactively constructing a graphic image on a data processing display system comprising:
displaying to an operator an information entry form into which said operator can insert information including a number of data fields for data entry, identification for said fields, and data in said fields;
storing said information in a table in relational database storage means;
displaying a report request form and allowing the operator to select information categories from said data fields for display;
selecting data from said database storage means corresponding to said selected information categories;
displaying a report in response to a request by said operator depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
upon detecting a request from an operator for further detail for a selected portion of said report, displaying to said operator a report refinement menu of selectable categories related to said data fields;
upon detecting the selection of a selected category from said report refinement menu(by said operator), selecting further data from said corresponding field of said table (in said database storage means) related to (as limited by) the data in said selected portion of said report;
displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.
Another aspect of the invention herein provides report generating apparatus for interactively constructing a graphic image on a data processing display system comprising:
display generating means for displaying to an operator an information entry form into which said operator can insert information including a number of data fields for data entry, identification for said fields, and data in said fields;
storing means for storing said information in a table in relational database storage means;
report request display means for displaying a report re~uest form and allowing the operator to select information categories from said data fields for display;
selection means for selecting data from said database storage means corresponding to said selected information categories;
report display means for displaying a report in response to a request by said operator depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said 5 selected data;means for detecting a request for a selected portion of said image upon detecting a request from an operator for further detail for a selected portion of said report, for displaying to said operator a report refinement menu of 0 selectable categories related to said data fields;
display refinement selecting means upon detecting the selection of a selected category from said report refinement menu(by said operator), selecting further data from said corresponding field of 5 said table (in said database storage means) related to(as limited by) the data in said selected por-tion of said report;
for displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.
Yet another aspect of the invention provides report generating apparatus for interactively constructing a graphic image on a data processing display system from information stored in a relational database, said information including data fields, identification for said fields, and data for stored in 215165~

relation to said fields;
means for allowing the selection of information categories from said data fields;
selection means for selecting data from said database storage means corresponding to said selected information categories;
report display means for displaying a report depicting the - relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
means for selecting a portion of said image for further detail, means for selecting refinement categories of information related to said data fields;
display refinement selecting means upon detecting the selection of a selected refinement category, for selecting further data from said corresponding field of said table related to(as limited by) the data in said selected portion of said report;
for displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.
Still another aspect of the invention provides a process for interactively constructing a graphic image on a data processing display system from information stored in a table in a relational database storage means; said information including a number of data fields, identification for said fields, and data in said fields, said process comprising:
displaying a report in response to a request by said operator depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said 21~165l selected data;
upon detecting a request from an operator for further detail for a selected portion of said report, displaying to said operator selectable categories related to said data fields;
upon detecting the selection of a selected category selecting further data from said corresponding field of said table related to (as limited by) the data in said selected portion of said report;
displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.
Another aspect of the invention provides report generating apparatus of a database system adapted to display a first graphical report of data of data table comprising:
drill down means for responding to a system user's selection of a portion of the graphical report to generate a subsequent graphical report of additional data related to the portion of the graphical report selected.
Advantageously the drill down means for responding to the user's selection is adapted to modify the query used to generate said first graphical report, using information for the portion selected to filter said additional data.
The drill down means may modify the specification used to form the initial query by filtering using the information for the portion selected to filter said additional data and apply a replacement selection attribute for the attribute used for the initial chart for the generation of a subsequent query for retrieval of selected data for display in a subsequent report.
The subsequent report may be a graphical report.
The being modified may be an SQL selection statement in which the filter applied corresponds to the portion of the first chart selected by the user and in which data grouping is specified as selected by the user from a data field of the 2151 6~4 -database table.
The invention systematically maps elements of the chart to the elements of the data processing instruction (a select statement in the case where SQL is used as the query language}
used to construct the original chart when the user selects a portion of the chart. The user selects that portion of the chart (in the case of a pie chart, a sector) for which to obtain more data, and the invention herein changes the grouping of data by the select statement because it has the mapping between the chart and the select statement. When the user want to change the chart or the display of the chart, the invention changes, the fetching, sorting, grouping and filtering instructions used to develop a display of the data for the user.
The chart is a very familiar metaphor to the user and graphically shows the relationship between the data under review by the user. In the situation where the user can understand what the chart means, since it was developed at the request of the user; the user can use the chart to formulate a completely new set of fetching, grouping, filtering instructions for the data processing system to construct a new chart displaying refined or additional data relating to the first chart without ever having to deal with those instructions directly. The user essentially, goes directly from chart to chart. The invention expresses a manipulation of the query in chart terms. The chart is a metaphor of the fetching instructions without the user actually writing them. This can be understood as a mapping relation between areas of the chart and an SQL select statement or query needed to access the database in order to fetch the data for plotting. The program of the invention helps the user express the terms in the way the user wants and these are translated to the instructions used by the program to select the data. Known database products make the user enter the instructions in computer language or a simulated language. The mapping of a chart to the SQL select statement in order to produce the chart is known but not addressing the chart to improve the selection in the manner of the invention.

215165~

One of the particular advantages of this invention is that the software of the invention does not require any information on the user's data before the data is inserted, it does not have to be customized for the user's information.

Brief Description of the Drawings The foregoing and other objects and advantages of the invention will be better understood from the following detailed description of a preferred embodiment of the invention with reference to the accompanying drawings, in which:
Fig. 1 is a diagram depicting a processing system for charting relational database data;
Fig. 1-2 is a table used by a person using the invention depicting data categories and fields for entering data;
Fig. 2 is diagram depicting a processing system for charting database data with data refinement (drill down) capability;
Fig. 2-2 is a table into which data has been entered in the fields;
Fig. 3 is a flow chart of the process of the invention;
Fig. 3-1 is a table presented to a user for which a report is being requested;
Fig. 3-2 is a panel presented to a user on requesting the generation of a report;
Fig. 3-3 is a panel presented to a user for the choice of details of the report;
Fig. 3-4 is a panel presented to a user for the choice of style of report;
Fig. 3-5 is a panel presented to a user for the choice of field to use as a category for the report;
Fig. 3-6 is a panel presented to user for the choice of fields to be included in the detailed section of the report;
Fig. 3-7 is a panel presented to user for the choice of which fields the report is to be grouped by;
Fig. 3-8 is a panel presented to user depicting the graphical chart constructed in accordance with requests made by ~ JSI ~S~f the user in response to choices made in previous panels;
Fig. 4-1 is a panel presented to the user after making a mouse gesture on a portion of the graphical chart;
Fig. 4-2 is a panel presented to the user including a menu of fields selectable by the user for obtaining more detailed data on the portion of the chart selected (drill down data);
Fig. 4-3 is a panel presented to the user for the choice of type of graphical report to be displayed for the drill down data report;
lo Fig. 4-4 is a panel presented to the user for the choice of chart title and creation choices for the chart;
Fig. 4-5 is a panel depicting the resulting chart depicting the drill down data for the region selected by the user from the original chart;
Fig. 5 is a flow chart describing the operation of the Drill Down Module;
Fig. 6 is a flow chart describing the operation of the SQL
Generation Module; and, Fig. 7 is a block diagram of a data processing system employing the invention herein.

Description of a Preferred Embodiment of the Invention The present invention is particularly useful in relational database systems; however, it can be applied to other database systems, including, for instance, object oriented databases.
Referring to Figure 7, for background, it is contemplated that the database application software of the invention is operating on a computer system 12 which has a display 13, a cpu 22, memory 21, storage 23, and a mouse 11, with a mouse button 10, and keyboard 24. As will be readily understood, the computer portrayed is merely representative of a data processing system, which could be a multi-computer, multi-storage computer system such as a distributed or parallel processing system.
Referring to Fig. 1, which shows a general system for charting relational database data, with which the invention disclosed herein can be employed, the system can be thought of 215165~

in three distinct interrelated divisions:
1. a user interface which can include a display of a database data processing or computer system for display of a report or chart to the user;
52. charting software operating in the computer system for preparing information for display as a chart for the user;
3. relational database management software which manages the storage and retrieval of data.
Chart specification dialogue panel 1, which is typically displayed to the user on a monitor 13 of a computer system 12 employing the database system of the invention, is the panel for the user to specify the initial parameters of the chart, for instance which data is to be plotted, and what fields of information are to be used for the purpose of plotting.
15Charting component 2 may be a conventional software program for converting user specified data into a chart for display to the user as shown in the representation of a user's chart 3.
The charting component 2 composes in this case an SQL query (the SQL language is commonly used for access to relational databases) which it sends to the database manager 4 in order to access the necessary data to produce a chart. The database manager 4 accesses the data from its store 5 as requested by the charting component 2 in response to the user's request and the database manager 4 returns that data to the charting component 2 for use in composing the desired chart 3 requested by the user and drawing it on the screen or monitor of the computer system being used.
Referring to Figure 2 which represents a full charting system having drill down capability in accordance with the invention herein; in the implementation of the invention shown, the initial chart specification dialogue 1 is presented to the user and assists the user in selecting the fields of data that is desired by the user for display as a chart. The user selects the fields that he desires to display forming a specification for use by the data processing system of the invention. The specification entered by the user is encoded in the charting ~15165~

component 2 to form an initial chart data specification 6 which is used as input to the SQL generating module 7 which constructs the necessary SQL select statement 8 for producing the initial chart 3 in accordance with the fields selected by the user.
Then the SQL Generating Module 7 passes the SQL select statement 8 to the database manager 4 which extracts data necessary to produce the chart from its store 5 and sends it to the plotting module 9 for the preparation and display of the initial chart 3 desired by the user. In order to obtain more detail concerning data present in the table stored in the database that the user was accessing by the using the chart 3, the user, clicks mouse button lO of the mouse 11 of the computer system being used to position a cursor 14 on a selected portion 15 of the chart for which the user wants to obtain additional information.
In many computer systems to which the invention herein would apply, the underlying operating system, on top of which the database system operates, responds to the user click and notifies the drill down module 16 where the user clicked on the screen.
The drill down module 16 then causes the display of menu panel 17 which presents to the user a dri]l down specification dialogue in the form of a menu or list of selections or categories (drill down fields) which user can pick to further refine the chart or produce a new more detailed chart for data associated with the selected portion of the chart 15.
The drill down module 16 then takes the chart data specification 6 that was used to generate the initial chart 3 and uses the information from the specification dialogue selection selected by the user and the information on which data area of the chart (portion 15) was selected to create a new chart data specification 18 by modifying the previous specification 6.
The drill down module 16 then sends the new chart data specification 18 to the SQL generation module 7 to generate the new SQL select statement 19 and then passes it to the database manager 4 in order to obtain the necessary information to - 21~165~

prepare the additional chart. The database manager 4 accesses its store 5 to obtain the information and conveys it to the plotting module 9 which then prepares the new drill down chart 20 which displays the refined or additional data required by the user. Nowhere in this process is the user required to know or use data processing language.
Although the ultimate user of the invention herein( as encoded in a computer program product or operating in a database system) would not need to know any data processing language, a review of some aspects of SQL statements and the extraction of data from a relational database are in order at this time.
A database table can be visualized as a matrix having a series of category fields, eg. columns, that usually carry selected names, and a series of rows of data entered in those columns under the appropriate category field names. When classifying items, eg. wines one category field is used for each classifiable value (e.g wine name, variety, colour, quality..).
The category field is specified as a grouping field in an SQL
select statement.
A value expression is an expression (eg. a mathematical expression such as SUM, COUNT, AVERAGE, MEAN, MAXIMUM, MINIMUM) which yields a single numeric value for each category, eg.
COUNT(FIELD 3) yields the number of unique values in Field 3 for each category.
The SQL select statement extracts raw data from the database table, groups the raw data by category and calculates a value for each category in accordance with the value expression.
An example of the extraction of chart data from a relational database table may be obtained by referring to Figure 3-1 which illustrates a database table wine list listing information on wines in accordance with a number of characteristics or fields(see also Figure 1-2) eg. Wine Name, Variety, Sweet or Dry, Country of Origin, Region, Colour, and quality; and Figure 3-8 the chart generated from this data depicting the distribution of quality in the wine list.

21$I 6S~

In this example we've used a single table of wines.
"Table" is a relational database term for a collection of related data. A relational database table consists of rows and columns in which columns can be thought of as characteristics of data and rows as instances of those characteristics so for example in the table of wines each row refers to one wine.
The invention could operate with any relational database table including a result table (result of a seperatly defined query) commonly called a view. The invention could also operate in any non-relational database system which supports grouping and filtering of data.
To generate a pie chart plotting the number of wines at each quality level the category field will be selected as quality and the value expression used (a mathematical expression in this example) will be count(Name). The SQL statement generated for the purposes of data retrieval will be "Select Quality, Count(Name) From Wines Group by Quality". This produces the chart of Figure 3-8 which illustrates a pie chart in which the segment size is determined by the number of named wines of each quality from qualities 1 to 5(the range present in the table).
The concept of filtering is also important to the invention as it is used in the process of drilling down through the initial chart or the chart being viewed to access and display additional or refinement data on a subsequent (the drill down chart). In order to perform an effective refinement by data drill down the user picks a different category(field) from the categories of the original chart. The value expression being used for this example will remain the same as in the original chart. A filter expression will be developed for use by the SQL
statement sent to the database. The filter expression is an expression that is used to limit the number of rows considered to those that meet the criteria in the expression used, eg.
FIELD 1 = 'Value". This is used to specify which of the initial chart categories to focus on. The SQL select statement focuses on data which matches the filter expression and groups by a 2I 51 6~4 different category than the original chart to obtain the data to plot the new chart, the drill down chart.
To illustrate the use of a filter in the SQL statement consider the example where it is desired to obtain additional data using the original chart illustrated in Figure 3-8 to obtain the chart depicted in Figure 4-5, which shows the distribution by region of wines of quality 5. From chart 3-8 the user requests further data on wines of quality 5 by clicking the mouse while the corresponding cursor is on the sector portion representing quality 5. When considered from the point of view of the generation of an SQL statement the following wi]l form the criteria for the new or modified SQL statement:
New Category Field = Region Value Expression = Count(Wine Name) Filter Expression = Quality = 5 The SQL statement becomes; "SELECT Region, COUNT(Wine Name) FROM Wines WHERE Quality = 5 GROUP by Region".

This statement is used to retrieve the data from the database table to construct the chart depicted in Figure 4-5 which shows the distribution by region of wines having a quality of 5.
Referring to Fig. 3, which represents the complete drill down process in accordance with the invention, we will start our 2S consideration of the process at the point where the invention herein, the database application used by the user, has presented the user with initial chart 3 that was requested. For convenience the embodiment of the invention described allows the user to initiate the drill down process by using a mouse button 10 to click on a data region 15 of the chart 3 which we shall refer to as a category. The mouse click is mapped to a category using coordinates provided by the operating system in response to the mouse event(the click or as sometimes is called, the mouse gesture). A copy of the initial SQL select statement 8 that was used for the initial chart 3 is taken for the basis of the new chart, the drill down chart 20, which is to be 21~165~

constructed. The original SQL select statement 8 is then modified using the category derived from the step of the mouse being clicked. A WHERE clause expression is built referring to the chart specifications 6 (which are used for the creation of the initial chart 3) as the category. This usually is taken from one column of the database table. A new WHERE expression is built in which the category coLumn is that category that was obtained from the mouse click step and this is used as the drill down category. The expression appears as a WHERE clause to constrain the amount of data (the number of rows obtained from the table used from the source) to that from the chart region 15 selected by the user to drill down for additional data.
The software of the invention appends the WHERE clause expression to the initial chart WHERE clause and applies the Boolean operator "and" between the preexisting WHERE clause and the drill down WHERE clause that has been constructed. If there were no existing WHERE clause in the initial chart then the new WHERE clause would be inserted in the SQL statement as the full WHERE clause to derive the new chart. The convenience of the use of APPENDS permits the drill down process to be repeated down several levels until the desired degree of data refinement can be displayed.
After the WHERE clause has been added to the select statement the program displays the drill down dialogue in the Z5 form of a menu 17 such as depicted in Fig. 4-2 which presents the user with a list of names of fields. In the specific example this is a list containing descriptors of wines, eg.
region, alcohol content, country of origin, etc., which were derived from the original table of data stored in the database store 5.
In the construction of the original chart 3, fields need to be selected in order to construct the chart 3. In the case of the example of p]otting the number of wines against their quality a mathematical operator will be needed to count the number of wines at each selected quality value. SQL language is capable of carrying out a number of functions such as summation, averaging, counting, etc. In the case of the chart on wine values, Quality is selected as the category for display and the counting function is used to compute the number of wines present in the chosen category, Quality.
In order to describe the operation of the invention in the simplified example we are going to keep the original SQL
function performed namely Counting and the original field that has been counted is also maintained.
The software of the invention is adapted to respond to the user's selection of a specific category for which additional related data is desired to be displayed. The user having selected the category that is of interest for obtaining more data now uses the software of the invention to select an additional category, containing data having a relationship to the data presented in the previous category under display (for the particular example, the number of wines quality 5). In this example the user desires to display the number of wines in each region having the preselected quality level 5, so that in effect the software of the invention is permitting the user to simply display the relationship of data in one category to data in another category at a preselected value.
Responding to the user's instruction, for instance, by a mouse gesture, such as a click on the category Field Region as shown in Fig. 4-2, the software of the invention groups the data by the category field and replaces the GROUP BY instruction present in the original SQL select statement 8 that produced the original chart, replacing the original S~L select statement.
The category field is added to the original Select Clause of the select statement replaciny the previous select clause. The software of the invention then executes the new select clause 19 causing the data to be selected from the database and displayed in a new chart 20 which depicts the number of wines from each region having quality 5. Similarly, if additional data is present in the database, additional categories can be selected by the user for presenting in further charts having further refinements and continuing until the appropriate level of detail 21516~4 is presented to the user as desired.

Pseudocode Representation of Aspects of the Invention The following is a dictionary of terms which are used in the pseudocode listings of the Drill Down Module 16 and the SQL
Generating Module 7 of the invention:

CHART_DATA_SPECIFICATION DATA STRUCTURE comprises the following: 0 TABLE_NAME: NAME OF THE RELATIONAL DATABASE TABLE CONTAINING
DATA TO BE PLOTTED

CATEGORY_FIELD_NAME: NAME OF THE FIELD IN THE TABLE
'TABLE_NAME' THAT CONTAINS THE CHART CATEGORIES
VALUE_EXPRESSION: SQL EXPRESSION WHICH SUMMARIZES CONTENTS OF
A FIELD IN TABLE 'TABLE_NAME' RESULTING IN A
SINGLE NUMERIC VALUE FOR EACH CATEGORY
0 FILTER_EXPRESSION: SQL EXPRESSION WHICH LIMITS THE ROWS TO BE
CONSIDERED IN GENERATING THE CHART..MAY BE EMPTY
IF ALL ROWS ARE TO BE CONSIDERED

Pseudocode for Drill Down Module Inputs: CHART_DATA_SPECIFICATION 1 (TABLE_NAME
CATEGORY_FIELD_NAME 1 VALUE_EXPRESSION 1 FILTER_EXPRESSION 1), Coordinates of mouse click on initial chart 3.

Logic SELECTED_CATEGORY = MAP MOUSE CLICK COORDINATES TO UNDERLYING
CATEGORY
Comments: "+" is a string concatenation operator 5 DRILL_DOWN_FILTER = CATEGORY_FIELD_NAME 1 + "=" +
SELECTED_CATEGORY

~ /S)(oS7~

IF FILTER_EXPRESSION 1 IS BLANK
FILTER_EXPRESSION 2 = DRILL_DOWN_FILTER
ELSE
FILTER_EXPRESSION 2 = FILTER EXPRESSION 1 + " AND " +
DRILL_DOWN_FILTER
AVAILABLE_FIELD_LIST = RETRIEVE LIST OF FIELDS IN TABLE_NAME 1 FROM DATABASE MANAGER
DISPLAY AVAILABLE_FIELD_LIST FOR USER SELECTION
CATEGORY_FIELD_NAME 2 = SELECTED FIELD FROM AVAILABLE_FIELD_LIST
TABLE_NAME 2 = TABLE_NAME 1 VALUE_EXPRESSION 2 = VALUE_EXPRESSION 1 Outputs:

TABLE_NAME 2 CATEGORY_FIELD_NAME 2 VALUE_EXPRESSION 2 FILTER_EXPRESSION 2 Referring to Fig. 5 which represents a flow chart of the 20 process of operation of drill down module 16 as represented by the above pseudocode, it can be seen that given the original chart data specification for chart 3 and the user's mouse click, the mouse click is mapped to the category selected for further detail. The module builds a drill down filter expression 25 DRILL_DOWN_FILTER_EXPRESSION with the category field name from the chart data specification (in the example, Quality) and the category chosen (5). If the filter expression form the chart data specification was blank (ie. the original chart was a first level chart) the filter expression for the new chart data 30 specification is the DRILL_DOWN_FILTER EXPRESSION (in the example, Quality = 5). If the filter expression from the previous chart was not blank, ie. it was obtained from drilling down from a previous level then the DRILL_DOWN_FILTER_EXPRESSION
is obtained by concatenation. The module then causes the 35 display of possible field names (DISPLAY AVAILABLE_FIELD_LIST) for the drill down chart (the field names are obtained from the ~' 21516~4 table stored in the database). The category field name (CATEGORY_FIELD_NAME) for the new chart data specification is set to the field name selected by the user from the list displayed in the previous step (in the example, regions) . The module then copies the TABLE_NAME and VALUE_EXPRESSION (eg.
count (wine name)) from the previous chart data specification to the new chart data specification and outputs the new chart data specification in which the new category field is regions, the filter expression is wines with Quality = 5, and the value expression is Count (wine name).

Pseudocode for SOL Generating Module Inputs:
CHART DATA SPECIFICATION
Outputs:
SQL SELECT STATEMENT
Logic:
A.
SQL_SELECT_STATEMENT =
" SELECT " +
CATEGORY_FIELD_NAME
" " +
VALUE_EXPRESSION +
" FROM " +
TABLE_NAME +
B.
IF FILTER_EXPRESSION IS NOT BLANK
SQL_SELECT_STATEMENT = SQL_SELECT_STATEMENT + " WHERE " +
FILTER EXPRESSION
ENDIF
C.
SQL_SELECT_STATEMENT = SQL_SELECT_STATEMENT + " GROUP BY " +
CATEGORY_FIELD_NAME
This statement is output to the database manager to select the necessary data for display as a chart.
Referring to Figure 8 of the drawings which depicts a flow 215165~

chart of the process carried out by the S~L generating module 7 of the invention to generate SQL statements to pass to the database manager it can be seen that the input to the generating module is the chart data specification, and that the module adds the category field name (eg. Region) and value expression (eg.
count (wine name)) to the SELECT clause of the SQL select statement and adds the table name to the FROM clause (eg. from wines) of the SQL statement. If the filter expression is not blank it is added to the WHERE clause of the SQL statement (eg.
WHERE Quality = 5 in the example). The category field name is added to the GROUP BY by clause of the SQL statement (eg. GROUP
BY region); and the final output of the module is the entire SQL
statement, which in the example being followed would be:

lS SELECT ~egion, COUNT(wine name) FROM Wines WHERE Quality = 5 GROUP BY Region The initial chart ~ is obtained in a manner similar to that shown in the drill down assistant panels where the user picks a field region, a chart type, chart title, etc. An assistant which is a series of panels can be provided to the user. The assistant can provide a series of questions that asked of the user and which are used to create the initial chart 3. A panel can ask what table the user wants to work with.
Referring to Figure 1-2 the user has selected the Wines table, and then they proceeds to enter information on the wines, possibly adding or deleting categories(fields) as desired. The assistant panels can advantageously be structured to ask the following questions in order to select data for forming a chart or table: What do you want to plot by (for instance, quality)?
What do you want to plot? Choices would be presented such as totalling or averaging, for instance. There could be other mathematical or statistical functions which can be supplied by the database system. Select statements can be made available to the user by the database system to select many different types of mathematical operations. This is well known in databases.

21~16~

Preferably these type of operations can be presented to the user in natural language, rather than as SQL statements.
After entering data in the fields of interest the table presented to the user could appear as depicted in Figure 2-2 which shows a portion of the table "Wines". Portions of the table have run off the left and right margins as may be seen.
The contents of the table are still present, but restricted temporarily from view due to display limitations.
One of the uses of the database system of the invention is to create reports. In the example that follows the user is creating a report that groups wines by their quality rating and produces a pie chart that shows the proportion of wine at each quality level. Referring to Fig. 3-1 it can be seen that the user has selected the report function of the product which pulls down the menu from which the user has selected NEW to initiate the creation of a report. Referring to Figure 3-2 the user is asked to select a name for the report and a base table for the report. The report is named RFPORT 1 and the table selected is the Wine List. The next step depicted in Figure 3-3 allows the user to select a presentation style(default in this case) and a layout style, in which Summary with Details is chosen. Figure 3-4 asks the user to select the type of report summary desired, from which the user has selected a pie chart and specified the title "Distribution of Quality in Wine List". Figure 3-5 offers a choice of fields, obtained from the database fields, for the user to have charted. Figure 3-6 asks the user what fields are to be included in the detail section of the report. Wine Name, Region, Country of Origin, Colour, and Quality have been chosen.
Figure 3-7 asks the user which fields the report is to be grouped by. The user has chosen Quality in this case. When the user indicates that he has finished his choices by pressing the Done button(using the mouse) the software of the invention sends the query that has been composed reflecting the users choices to the database and causes the chart of Figure 3-8 to be displayed, in which the upper section comprises a pie chart showing the distribution of wine by quality in the Wine List, and also a 21~16S4 lower section containing a detailed report using the categories or fields previously selected by the user.
What that chart represents is the quality rating from 1 to 5 for the wines and a sector has been generated for each one of the possible ratings. The relative size of the sector indicates the relative number of wines of that particular quality level.
Note that the largest sector is 18 corresponding to 18 wines at that particular level. The legend shows the colours or patterns of the sectors and the quality represented.
The next task desired by the user is to drill down into the data by using the chart to obtain more information. The chart has answered the question,"What is the big picture of the quality of the wines I have in my database? How many good ones do I have, how may rotten ones. Now the user wants to refine that question a little bit more, for instance, for the highest quality wines that are present, quality 5, show what regions they come from.
The user wants to go down into the data that's used to create this chart to answer more specific questions or to reveal other characteristics of the data. The invention can provide the user with a more detailed level of data from which the chart was derived and also access data that may not be reflected in the chart, for instance, where the wine came from, ie. auxiliary data. The embodiment of the invention described herein can do two things with the chart, one is to obtain more detail which is behind the actual data of the chart, or to obtain other data, related to the information displayed, which data is present in the database.
Recapping for the moment, the display of the computer system has produced a pie chart in which each sector present on the chart represents a quality level, and the software of the invention, as it constructed the select statement, to retrieve the necessary data from the raw data of the database in which it was stored, has mapped each sector to each quality level, so that if a user were to select a sector the software would associate it with the appropriate quality level.

215165~

Referring to Figure 4-1, to create a subsequent pie chart that shows the distribution of wine by region for all wines for the quality level of 5, the user has selected a portion or sector of the chart corresponding to quality level 5 by means of moving a cursor onto the sector and making a mouse gesture, such as pressing the right mouse button. This could also be done by using a keyboard or selecting an appropriate selection from a pull down menu. The software has displayed a menu from which ~the user can select "Drill Down". This task assumes that we have a wine list database with the necessary data entered in the database. The user has used the software of the invention to filter the data so that only high quality wine is selected where the quality is 5 on the chart of wine quality.
Referring to Figure 4-2, which depicts an assistant panel which is displayed by the software embodiment of the invention, when the user has requested the software to look in more detail at the wine information; the user has selected the sector that represents wines that have that quality 5. The assistant panel asks the user which further detail should be displayed about those wines. The user has selected "Region", the region from which those wines came. The software has provided the user with a menu of fields of additional or auxiliary data for which the user can obtain information. The fields or characteristics displayed are those contained by the database for the table.
The next step is to select the type of chart to display from the panel depicted in Figure 4-3. The result of this process is that the software will plot a second chart. The software can provide this automatically, as desired.
It will be realized by those skilled in the art to which the invention relates that there are many different ways of specifying instructions by the user, different menus could be used. The user could just type in a field name instead of using an entry panel.
A number of different chart types are illustrated for the choice of the user. The user could press the "Done" push button to go directly the new chart. However, for this example we 215165~

continue to the next assistant panel shown in Figure 4-4 which permits the user to create a new chart or replace the existing chart and also to select a name for the new chart. A new chart, Chart 2, is selected, as is a new title, Distribution of Region for Quality = 5.
What occurred in producing the second chart was: when the user selected "Drill Down" applying to a particular sector, the software of the invention applied a filter (in SQL terms) to the specification that accessed the raw data that was plotted by noting or detecting which sector was selected, what the value was, and added criteria to the SQL selection statement that was used to retrieve data from the database.
By selecting the sector, Quality = 5, all of the other wines not having a quality of 5 have been eliminated from the necessity of retrieval, and by selecting the Region field, information is added to the chart, which information is requested for retrieval from data present in the database.
From a user selecting the sector Quality = 5, the first part of the drill down process refines the SQL query to add a filter for quality = 5; and then the second part of the query is modified to select an attribute to provide additional data from a different column (Region) than was previously used to summarize the data for the initial chart, so that only the rows representing wines of quality 5 are now considered. The Region characteristic is added and the results are to be grouped by regions, accordingly, rather than by Quality as was the case for the initial query used to obtain the first chart.
In order to change the grouping the embodiment of the invention descxibed will allow the user to pick from any of the fields in the original table. As stated previously, those fields came from the raw data that was originally provided to the database. The user could also optionally specify a different aggregation be done and an additional panel could be offered as to enquiring of the user how data is to be aggregated.
Drill down operations can be done a number of times, ~516S i depending on the relevant fields and data available in the database. In other words, the process can be applied repeatedly from one chart to the next to drill down further to obtain additional charts or reports. The software can also provide to the user a method of drilling back up, ie. returning to a previous chart or charts. An Undo command would achieve this by reversing the last selection modifications. The Undo command could be provided in one of the overhead action menus. The user just selects the menu and activates the undo function by clicking the cursor on it using the mouse, as will be well appreciated.
Many variations on the above described embodiment of the invention are possible without departing from the scope of the invention. The assistant screens presented to the user are optional, as other means of making options and fields are possible. Entries can be entered by keyboard, or selected from pull down menus. A portion of chart can be selected from a pull down menu, rather than being selected by a cursor / mouse gesture approach.

Claims

1. A process for interactively constructing a graphic image on display of a data processing system said system including database storage means for storing information including a number of data fields for data identification for said fields, and data in said fields comprising:
allowing an operator to select information categories from said data fields for display;
selecting data from said database storage means corresponding to said selected information categories;
displaying a report depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
upon detecting a request for further detail on a selected portion of said report, displaying to said operator selectable categories related to said data fields;
upon detecting the selection of a category, selecting further data from said corresponding field related to the data in said selected portion of said report;
displaying said further data in a second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.

2. A process for interactively constructing a graphic image on a data processing display system comprising:
displaying to an operator an information entry form into which said operator can insert information including a number of data fields for data entry, identification for said fields, and data in said fields;
storing said information in a table in database storage means;
displaying a report request form and allowing the operator to select information categories from said data fields for display;
selecting data from said database storage means corresponding to said selected information categories;
displaying a report in response to a request by said operator depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
upon detecting a request from an operator for further detail for a selected portion of said report, displaying to said operator a report refinement menu of selectable categories related to said data fields;
upon detecting the selection of a selected category from said report refinement menu (by said operator), selecting further data from said corresponding field of said table (in said database storage means) related to(as limited by) the data in said selected portion of said report;
displaying said further data in a second report;
said second report comprising a graphical image derived from said further data;
said report displaying said further data in relation to said selected categories.

3. Report generating apparatus for interactively constructing a graphic image on a display of a data processing system, said database system including database storage means for storing information including a number of data fields for data identification for said fields, and data in said fields, comprising:
report request means for allowing an operator to select information categories from said data fields for display;
selection means for selecting data from said database storage means corresponding to said selected information categories;
report display means for displaying a report in response to a request by said operator depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
means for detecting a request on a selected portion of said image from an operator for further detail for said selected portion of said report, for allowing said operator to select categories related to said data fields;
display refinement selecting means upon detecting the selection of a selected category by said operator, selecting further data from said corresponding field in said database storage means related to the data in said selected portion of said report;
for displaying said further data in a second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.

4. Report generating apparatus for interactively constructing a graphic image on a data processing display system comprising:
display generating means for displaying to an operator an information entry form into which said operator can insert information including a number of data fields for data entry, identification for said fields, and data in said fields;
storing means for storing said information in a table in relational database storage means;
report request display means for displaying a report request form and allowing the operator to select information categories from said data fields for display;
selection means for selecting data from said database storage means corresponding to said selected information categories;
report display means for displaying a report in response to a request by said operator depicting the relationship of data in said selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
means for detecting a request for a selected portion of said image upon detecting a request from an operator for further detail for a selected portion of said report, for displaying to said operator a report refinement menu of selectable categories related to said data fields;
display refinement selecting means upon detecting the selection of a selected category from said report refinement menu(by said operator), selecting further data from said corresponding field of said table (in said database storage means) related to(as limited by) the data in said selected portion of said report;
for displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.

5. Report generating apparatus for interactively displaying a graphic image on a data processing display system from information stored in a database, said information including data fields, identification for said fields, and data stored in relation to said fields;
means for allowing the selection of information categories from said data fields;
selection means for selecting data from said database storage means corresponding to said selected information categories;
report display means for displaying a report depicting the relationship of data in said selected categories;

wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
means for responding to the selecting of a portion of said image for further detail, means for selecting refinement or additional categories of information related to said data fields;
display refinement selecting means upon detecting the selection of a selected refinement category, for selecting further data from said corresponding field in said database related to the data in said selected portion of said report;
for displaying said further data in a second report;
said second report comprising a graphical image derived from said further data;
said report displaying said further data in relation to said selected categories.

6. A process for interactively constructing a graphic image on a data processing display system from information stored in a table in a database storage means; said information including a number of data fields, identification for said fields, and data in said fields, said process comprising:
displaying a report in response to a request by said operator depicting the relationship of data in selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
upon detecting a request from an operator for further detail for a selected portion of said report, displaying to said. operator selectable categories related to said data fields;
upon detecting the selection of a selected category selecting further data from said corresponding field of said table related to(as limited by) the data in said selected portion of said report;

displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.

7. Report generating apparatus of a database system adapted to display a graphical report of data of data table comprising:
drill down means for responding to a system user's selection of a portion of a first graphical report displayed by the system to generate a subsequent graphical report of additional data related to the portion of the graphical report selected.

8. The report generating apparatus of claim 7 in which the drill down means for responding to the user's selection is adapted to modify the query used to generate said first graphical report, using information for the portion selected to filter said additional data.

9. The apparatus of claim 7 wherein said drill down means modifies the specification used to form the initial query by filtering using the information for the portion selected to filter said additional data and apply a replacement selection attribute for the attribute used for the initial chart for the generation of a subsequent query for retrieval of selected data for display in a subsequent report.

10. The apparatus of claim 9 in which said subsequent report is a graphical report.

11. The apparatus of claim 9 in which said query being modified is an SQL selection statement in which the filter applied corresponds to the portion of the first chart selected by the user and in which data grouping is specified as selected by the user from a data field of the database table.

12. An article of manufacture comprising:
a computer usable medium having computer readable program code means embodied therein for causing a computer to operate database system to display a report related to data stored in said database, the computer readable program code means in said article of manufacture comprising:
computer readable program code means for causing a computer to respond to a user selection of a portion of a first graphical report displayed by said computer, and a user selection of an attribute of data stored in said database related to said graphical report;
and including computer readable program code means for causing said computer to generate a subsequent report depicting additional data related to said selected portion of said first graphical display having said selected attribute;
and computer readable program code means for causing said computer to display said report as a graphical display.

13. The article of claim 12 wherein said computer readable program code means for causing said computer to generate a subsequent report comprises computer readable program code means for causing said computer to modify the selection specification used to select data from said database to display said first graphical report to filter said subsequent additional data selected by a user of said system by information contained in said selected display portion; and further including program code means for causing said computer to generate a data selection instruction to retreive data from said database satisfying said attribute selected by said user and as filtered by said information of said first chart portion selected by said user for display.

14. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine to perform method steps for interactively constructing a graphic image on a data processing display system from information stored in database storage means; said information including a number of data attributes or category fields, said method steps comprising:
displaying a report depicting the relationship of data in selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
upon detecting a request for further detail for a selected portion of said report, selecting further data corresponding to a category field selected from said corresponding field of said database related to said data in said selected portion of said report;
displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.

15. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine to perform method steps for interactively constructing a graphic image on a data processing display system from information stored in a table in a database storage means; said information including a number of data attributes or category fields, said method steps comprising:
displaying a report depicting the relationship of data in selected categories;
wherein said report displayed is a graphical image having distinguishable portion(s) therein corresponding to said selected data;
upon detecting a request from an operator for further detail for a selected portion of said report, displaying selectable categories related to said data fields;
upon detecting the selection of a selected category selecting further data from said corresponding field of said table related to the data in said selected portion of said report;
displaying said further data in a second report;
said second report comprising a graphical image derived form said further data;
said report displaying said further data in relation to said selected categories.