Systems and methods providing dynamic spreadsheet functionality

• US 7,082,569 B2
• Filed: 01/11/2002
• Issued: 07/25/2006
• Est. Priority Date: 01/17/2001
• Status: Expired due to Term

• Current Assignee: Outlooksoft Corporation (SAP SE)
• Original Assignee: Outlooksoft Corporation (SAP SE)
• Inventors: Voshell, Perlie E.
• Primary Examiner(s): Hong, Stephen
• Assistant Examiner(s): Faber, David
• Application Number:
  US10/043,675
• Publication Number:
  US 20020169799A1
• Time in Patent Office: 1,656 Days
• Field of Search: 715503-508, 715/531, 345/803
• US Class Current: 715/212
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: H03M 7/30 Compression speech analysis-synthesis for redundancy reduction G10L19/00; for image communication H04N; Expansion; Suppression of unnecessary data, e.g. redundancy reduction
• CPC Class Codes: H04L 12/2801 Broadband local area networks
• CPC Class Codes: H04L 69/04 Protocols for data compression, e.g. ROHC
• CPC Class Codes: H04L 69/22 Parsing or analysis of headers
• CPC Class Codes: H04W 28/06 Optimizing the usage of the radio link, e.g. header compression, information sizing , discarding information system modifying transmission characteristic according to link quality by modifying frame length H04L1/0007; dynamic adaptation of the packet size for flow control or congestion control H04L47/365

Abstract

An advantageous system and method are provided for supporting and/or enabling the creation of dynamic reports and/or data presentation(s) in connection with a spreadsheet-based application or program. The disclosed system and method offer a full complement of spreadsheet-based formatting and calculation capabilities. In addition, the system and method of the present disclosure may be used in conjunction with and/or incorporated as part of spreadsheet-based application(s) and/or program(s) found on stand-alone clients and/or networked clients. The disclosed system and method generally include an electronic spreadsheet having a plurality of cells that are arrayed in a defined number of columns and rows, a database in communication with the electronic spreadsheet, and an expansion formula that functions to control retrieval of data from the database and automatically varies/expands at least one of the defined number of columns and rows to accommodate the data retrieval.

Claims

1. A method for dynamic spreadsheet reporting, comprising:

a) providing an electronic spreadsheet having a plurality of cells that are arrayed in a defined number of columns and rows;

b) providing a database in communication with said electronic spreadsheet;

c) receiving an expansion formula in at least one of said plurality of cells that comprises at least one dimension member and instructions for expansion, said expansion formula functioning to control retrieval of data associated with said at least one dimension member;

d) in response to rendering said electronic spreadsheet, using said at least one dimension member from said expansion formula to query said database to retrieve data associated with said at least one dimension member from said database;

e) storing said retrieved data associated with said at least one dimension member from said expansion formula in a cache for use to respond to one or more queries to said database for said at least one dimension member;

f) using said instructions for expansion from said expansion formula to automatically adjust at least one of the defined number of said columns and rows to accommodate said data responsive to said querying of said database; and

g) populating said adjusted columns and rows with said retrieved data while maintaining the operation and functionality of said electronic spreadsheet.

2. A method according to claim 1, wherein said electronic spreadsheet is supported by a spreadsheet application that provides formatting and calculation functionalities to said electronic spreadsheet.

3. A method according to claim 2, wherein said spreadsheet application operatively resides on a computer system selected from the group consisting of a stand-alone client and a networked client.

4. A method according to claim 2, wherein said spreadsheet application is a collaborative spreadsheet application that operates across a plurality of networked computers.

5. A method according to claim 1, wherein said data includes at least one dimension member, and wherein said at least one dimension member is identified by at least one parameter set forth in said expansion formula.

6. A method according to claim 5, wherein said at least one dimension member is a data category.

7. A method according to claim 6, wherein said data category is selected from the group consisting of time periods, departments, account types, account categories, product types, product categories and combinations thereof.

8. A method according to claim 5, further comprising:

d) automatically updating said electronic spreadsheet to include a new dimension member upon introduction of said new dimension member to said database, provided the definition of said expansion formula calls for retrieval of said new dimension member from said database.

9. A method according to claim 1, wherein in automatically varying at least one of the defined number of said columns and rows, said expansion formula does not impair operability or functionality of said electronic spreadsheet external to said data retrieval accommodation.

10. A method according to claim 1, further comprising defining a member list in said database, said member list including hierarchical list of members within a given category of said data.

11. A method according to claim 1, wherein said expansion formula utilizes at least one function selected from the group consisting of an EVNXP function, an EVNXP function, an EvENE function, an EvLST function, an EvSET function, an EvPXR, and combinations thereof.

12. A method according to claim 11, wherein said expansion formula utilizes at least two nested functions.

13. A method according to claim 1, further comprising:

d) recalculating said electronic spreadsheet based on data retrieval from said database controlled by said expansion formula, and

e) building a cache in a memory for said expansion formula.

14. A method according to claim 13, further comprising:

f) expanding said electronic spreadsheet by querying said database for a list of members as defined in expansion parameters included in said expansion formula;

g) adjusting the number of cells in a key range and a data range associated with said expansion formula as necessary to accommodate said list of members;

h) populating said key range with members from said list of members, and

i) copying formulas from at least one of said plurality of cells to an appropriate newly inserted cell to fill an entire data range.

15. A method according to claim 14, further comprising:

j) deleting said cache in said memory; and

k) recalculating said electronic spreadsheet in said expanded electronic spreadsheet.

16. A system for supporting dynamic spreadsheet reporting, comprising:

a client that includes a processor and associated data storage containing at least one database, said processor adapted to process programmatic instructions associated with an electronic spreadsheet having a plurality of cells that are arrayed in a defined number of columns and rows;

said programmatic instructions directing said processor to;

receive at least one expansion formula inserted into in at least one of said plurality of cells that comprises at least one dimension member and instructions for expansion, said expansion formula functioning to control retrieval of data associated with the at least one dimension member;

in response to rendering said electronic spreadsheet, use said at least one dimension member from said expansion formula to query said database to retrieve data associated with said at least one dimension member from said database;

store said retrieved data associated with said at least one dimension member from said expansion formula for use to respond to one or more queries to said database for said at least one dimension member;

use said instructions for expansion from said expansion formula to automatically adjust at least one of the defined number of said columns and rows to accommodate said data responsive to said querying of said database; and

populate said adjusted columns and rows with said retrieved data while maintaining the operation and functionality of said electronic spreadsheet.

17. A system according to claim 16, wherein said programmatic instructions provides formatting and calculation functionalities to said electronic spreadsheet.

18. A system according to claim 16, wherein said client communicates with a server across a network, and wherein said data storage is associated with said server.

19. A system according to claim 16, wherein said spreadsheet application is a collaborative spreadsheet application that operates across a plurality of networked computers.

20. A system according to claim 16, wherein said data includes at least one dimension member, and wherein said at least one dimension member is identified by at least one parameter set forth in said expansion formula.

21. A system according to claim 16, wherein said programmatic instructions affect automatic updating of said electronic spreadsheet to include a new dimension member upon introduction of said new dimension member to said database, provided the definition of said expansion formula calls for retrieval of said new dimension member from said database.

22. A system according to claim 16, wherein in automatically varying at least one of the defined number of said columns and rows, processing of said expansion formula by said processor does not impair operability or functionality of said electronic spreadsheet external to said data retrieval accommodation.

23. A system according to claim 16, wherein in processing said expansion formula, said processor processes at least one function selected from the group consisting of an EVEXP function, EVNXP function, an EvENE function, an EvLST function, an EvSET function, an EvPXR function, and combinations thereof.

24. A system according to claim 16, wherein in processing said expansion formula, said processor processes at least two nested functions.