Systems and methods providing dynamic spreadsheet functionality

• US 20070130503A1
• Filed: 07/21/2006
• Published: 06/07/2007
• Est. Priority Date: 01/16/2001
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: Perlie Voshell
• Inventors: Voshell, Perlie
• Granted Patent: US 8,275,974 B2
• Time in Patent Office: Days
• US Class Current: 715/212
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

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) defining an expansion formula in at least one of said plurality of cells, said expansion formula functioning to control retrieval of data from said database and to automatically vary at least one of the defined number of said columns and rows to accommodate said data retrieval.

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 EVEXP function, EVNXP function, an EvENE function, an EvLST function, an EvSET function, an EvPXR function, 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 including at least one expansion formula inserted into in at least one of said plurality of cells, said expansion formula functioning to control retrieval of data from said at least one database and to automatically vary at least one of the defined number of said columns and rows to accommodate said data retrieval.

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.