Note: Descriptions are shown in the official language in which they were submitted.
.6 5 ~
CA9-95-009
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
2151651
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.
2151651
CA9-95-O09
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
21516S4
CA9-95-009
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,
2151651
CA9-95-009
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 said5 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 of0 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 of5 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~
CA9-95-O09
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
CA9-95-OO9
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
-
CA9-95-OO9
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~
CA9-95-009
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
CA9-95-009
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~
CA9-95-OO9
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
11
~15165~
CA9-95-OO9
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~
CA9-95-009
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.
13
21$I 6S~
CA9-95-OO9
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
CA9-95-009
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~
CA9-95-009
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,
2I 51 654
CA9-95-009
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
CA9-95-009
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 operator5 DRILL_DOWN_FILTER = CATEGORY_FIELD_NAME 1 + "=" +
SELECTED_CATEGORY
18
~ /S)(oS7~
CA9-95-009
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:
CHART-DATA-SPECIFICATION 2
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
19
~'
21516~4
CA9-95-009
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~
CA9-95-OO9
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~
CA9-95-009
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
CA9-95-OO9
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~
CA9-95-009
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
24
215165~
CA9-95-OO9
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
CA9-95-009
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.
26
