System and methods for reformatting multi-dimensional spreadsheet information

• US 5,604,854 A
• Filed: 05/22/1996
• Issued: 02/18/1997
• Est. Priority Date: 04/22/1994
• Status: Expired due to Term

• Current Assignee: Borland Software Corporation (Open Text Corporation)
• Original Assignee: Borland International, Inc. (Open Text Corporation)
• Inventors: Glassey, Colin R.
• Primary Examiner(s): Herndon, Heather R.
• Assistant Examiner(s): HONG, STEPHEN S
• Application Number:
  US08/651,580
• Time in Patent Office: 272 Days
• Field of Search: 395/144, 395/145, 395/146, 395/148, 395/155, 395/157, 395/159, 395/160, 395/161, 364/419.17, 364/419.19, 345/59
• US Class Current: 715/209
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

An electronic spreadsheet system of the present invention includes a Data Modeling module for creating multi-dimensional reports from spreads of information. The module includes a preferred report workspace interface--a Data Modeling Desktop--for receiving a user-specified range of source data from an electronic spreadsheet, which may include 2-D as well as 3-D ranges of source data from a spreadsheet notebook. In response to user inputs, the system assembles a corresponding multi-dimensional report by transforming 2-D or 3-D spreadsheet information into multi-dimensional format. In this manner, users can continue to build spreadsheet models on an ad hoc basis (i.e., without designing complex multi-dimensional data models beforehand). When multi-dimensional reporting is desired, the users need only indicate to the system that the then-existing spreadsheet information (which is not in multi-dimensional format) is to be automatically converted by the system into a corresponding multi-dimensional structure, for further analysis and reporting.

Claims

1. In an electronic spreadsheet system having a spreadsheet comprising a plurality of information cells for storing user-supplied data and formulas operative on said data, a method for transforming three-dimensional spreadsheet information into two-dimensional format for generating a cross-tabular report, the method comprising:

(a) receiving user input specifying a three-dimensional block of information cells, said three-dimensional block comprising a plurality of two-dimensional blocks, each of said two-dimensional blocks comprising an array of information cells arranged in row and column format, each of said information cells in said three-dimensional block being uniquely identified by a row, a column, and a spreadsheet page where the cell is located;

(b) transforming said three-dimensional block of spreadsheet information into two-dimensional format by copying rows of spreadsheet information from said three-dimensional block into a two-dimensional block, said transforming step comprising;

(i) determining how many columns are present in said three-dimensional block;

(ii) creating a new two-dimensional block of information cells, said new two-dimensional block having one additional column than that present in said three-dimensional block, so that said new two-dimensional block includes a number of columns that is one more than said determined number of columns in said three-dimensional block, each of said information cells in said new two-dimensional block being uniquely identified by a row and a column;

(iii) storing a first row of information cells from said three-dimensional block as a reference row for recognizing each subsequent page of said three-dimensional block; and

(iv) for at least some subsequent rows of information cells present in said three-dimensional block, storing corresponding information in respective columns of said new two-dimensional block of information cells and storing in said additional column an identifier for the page where said each row originated, so that each row of said two-dimensional block comprises a row of information from said three-dimensional block stored together with an identifier for the page where said each row originated, said identifier being determined at least in part on the basis of said recognizing each subsequent page of said three-dimensional block;

(c) receiving additional user input specifying creation of a cross-tabular report based on information from said two-dimensional block of information cells; and

(d) in response to said additional user input, displaying a cross-tabular report generated from said two-dimensional block of information cells, so that identifiers for said pages and information cell contents for at least one column appear as cross-tabular labels for the report.

2. The method of claim 1, wherein step (b)(iv) is carried out for each subsequent row, and comprises, for each subsequent row:

comparing the row with said reference row, a given row being considered to match said reference row if each information cell in the given row has a value that equals the value of a corresponding cell in said reference row;

if the row does not match the stored reference row,

(a) storing a current page identifier for the row in a first column of a new row of said new two-dimensional block of information cells, and

(b) storing remaining information cells of the row in subsequent columns of the new row of said new two-dimensional block; and

if the row matches the stored reference row, storing the identifier of the page for the row as the current page identifier without storing information cells of the row.

3. The method of claim 1, wherein an identifier for a page comprises a descriptive text label provided by a user.

4. The method of claim 1, further comprising:

receiving from a user text input characterizing said page identifiers.

5. The method of claim 4, further comprising:

providing the user with a default text label of "Page" for characterizing said page identifiers.

6. The method of claim 1, further comprising:

receiving yet additional user input for repositioning desired ones of said cross-tabular labels; and

in response to said additional user input, regenerating said cross-tabular report, so that identifiers for rows, columns, and pages appear as repositioned cross-tabular labels for the report.

7. The method of claim 1, wherein said identifiers for said pages appear as side cross-tabular labels.

8. An electronic spreadsheet system for storing and manipulating information as a three-dimensional spreadsheet and for representing said information to a user as a two-dimensional report, the system comprising:

a computer having an input device, a display device, and a storage device;

means for displaying on said display device a plurality of spreadsheet pages, each of said spreadsheet pages comprising an array of information cells arranged in row and column format, at least some of said information cells representing information stored in said storage device, including user-supplied information and formulas operative on said user-supplied information, each of said information cells in said three-dimensional block being uniquely identified by row, column, and page identifiers indicating where the cell is located;

means, responsive to signals from said input device, for selecting a three-dimensional block of information cells, said three-dimensional block being characterized by a number of columns; and

means for transforming said three-dimensional block of spreadsheet information into a two-dimensional block for cross-tabular analysis, said means for transforming including;

means for creating a new two-dimensional block of information cells, said new two-dimensional block having one additional column than that present in said three-dimensional block, so that said new two-dimensional block includes a number of columns that is one more than said number of columns in said three-dimensional block, each of said information cells in said new two-dimensional block being uniquely identified by a row and a column;

means for identifying which rows of said three-dimensional block match a first one of said rows in said three-dimensional block, a given row being considered to match said first one of said rows if each cell in the given row has a value that equals the value of a corresponding cell in said first one of said rows; and

means for storing, only for each row of said three-dimensional block that does not match said first one of said rows, corresponding information in said new two-dimensional block of information cells, so that

(a) each row of said new two-dimensional block comprises a row of information from said three-dimensional block stored together with an identifier for the page where said each row originated, said identifier for the page being stored in said additional column, and

(b) said new two-dimensional block of information does not comprise subsequent rows of said three-dimensional block that do match said first one of said rows.

9. The system of claim 8, wherein said three-dimensional block of information cells comprises a plurality of rows, and wherein at least two of said rows match.

10. The system of claim 8, wherein an identifier for a page comprises a descriptive text label provided by a user.

11. The system of claim 8, and further comprising:

means for displaying on said display device said new two-dimensional block wherein each row in said two-dimensional block that corresponds to a row in said three-dimensional block has a first column containing the page identifier for the page where the row originated and remaining columns containing the row of information from said three-dimensional block.

12. The system of claim 11, wherein said new two-dimensional block of information cells is displayed with rows originating from a given page in said three-dimensional block grouped together.

13. In an electronic spreadsheet system having a spreadsheet comprising a plurality of information cells for storing user-supplied data and formulas operative on said data, a method for transforming a three-dimensional block of spreadsheet information into a corresponding two-dimensional block of spreadsheet information for generating summary reports, the method comprising:

(a) receiving user input specifying a three-dimensional block of information cells to be transformed, said three-dimensional block comprising information cells arranged in row, column, and page format, each row, column, and page having a particular name;

(b) determining column labels from information stored in said three-dimensional block, said column labels describing contents stored in each column of said three-dimensional block;

(c) determining page labels from information stored in said three-dimensional block, said page labels describing contents stored in each page of said three-dimensional block;

(d) transforming said three-dimensional block into a new two-dimensional block, said new two-dimensional block having a plurality of information cells arranged in row and column format, wherein columns of said new two-dimensional block include labels based on said determined column labels, and wherein each row of said new two-dimensional block stores a row of cell information taken from said three-dimensional block together with a label based on said determined page labels, each of said information cells in said new two-dimensional block being uniquely identified by a row and a column;

(e) displaying said new two-dimensional block with a column including said determined page labels;

(f) receiving additional user input specifying creation of a cross-tabulation report; and

(g) displaying a cross-tabular report generated from said new two-dimensional block of information cells, so that identifiers for said pages and information cell contents for at least one column appear as cross-tabular labels for the report.

14. The method of claim 13, wherein step (d) includes:

determining a current page identifier for each row of said three-dimensional block; and

storing the current page identifier for said each row in a first column of a new row of said new two-dimensional block of information cells, together with information cells taken from a row of said three-dimensional block.

15. The method of claim 14, wherein said determining step includes:

comparing cell contents for each row against a reference row of information cells, said reference row comprising a row of text labels present on each page of said three-dimensional block, a match between a given row and said reference row being considered to occur if each cell in the given row has a value that equals the value of a corresponding cell in said reference row;

if a match does not occur, storing the current page identifier for said each row in a first column of a new row of said new two-dimensional block of information cells, together with information cells taken from a row of said three-dimensional block; and

if a match occurs, storing the page identifier for cells of said each row as the current page identifier without storing information cells of the row.

16. The method of claim 13, wherein said three-dimensional block of information cells comprises a plurality of cell rows, and wherein at least two of said rows store identical information.

17. In an electronic spreadsheet system having a spreadsheet comprising a plurality of information cells for storing user-supplied data and formulas operative on said data, a method for transforming a three-dimensional block of spreadsheet information into a corresponding two-dimensional block of spreadsheet information and generating summary reports, the method comprising:

displaying on a display device a plurality of spreadsheet pages, each of said spreadsheet pages comprising an array of information cells arranged in row and column format, at least some of said information cells representing information stored in a storage device, including user-supplied information and formulas operative on said user-supplied information, each of said information cells in said three-dimensional block being uniquely identified by row, column, and page identifiers indicating where the cell is located;

in response to user input, selecting a three-dimensional block of information cells, said three-dimensional block being characterized by a number of columns;

creating a new two-dimensional block of information cells, said new two-dimensional block having one additional column than that present in said three-dimensional block, so that said new two-dimensional block includes a number of columns that is one more than said number of columns in said three-dimensional block, each of said information cells in said new two-dimensional block being uniquely identified by a row and a column;

identifying which rows of said three-dimensional block match a first one of said rows in said three-dimensional block, a given row being considered to match said first one of said rows if each cell in the given row has a value that equals the value of a corresponding cell in said first one of said rows;

storing, only for each row of said three-dimensional block that does not match said first one of said rows, corresponding information in said two-dimensional block of information cells, so that

(a) each row of said new two-dimensional block comprises a row of information from said three-dimensional block stored together with an identifier for the page where said each row originated, said identifier for the page being stored in said additional column, and

(b) said new two-dimensional block of information does not comprise subsequent rows of said three-dimensional block that do match said first one of said rows;

displaying said two-dimensional block with a column including said determined page labels;

in response to additional user input, generating a cross-tabulation report from said two-dimensional block of information cells; and

displaying the cross-tabular report, so generated, so that identifiers for said pages and information cell contents for at least one column appear as cross-tabular labels for the report.

18. The method of claim 17, wherein an identifier for a page comprises a descriptive text label provided by a user.

19. The method of claim 17, further comprising:

receiving from a user text input characterizing said page identifiers.

20. The method of claim 17, further comprising:

providing the user with a default text label of "Page" for characterizing said page identifiers.

21. The method of claim 17, further comprising:

receiving yet additional user input for repositioning desired ones of said cross-tabular labels; and

in response to said yet additional user input, regenerating said cross-tabular report, so that identifiers for rows, columns, and pages appear as repositioned cross-tabular labels for the report.

22. The method of claim 17, wherein said identifiers for said pages appear as side cross-tabular labels.

23. The method of claim 17, wherein said two-dimensional block of information cells is displayed with rows originating from a given page in said three-dimensional block grouped together.