METHOD FOR CAPTURING DESIGN-TIME AND RUN-TIME FORMULAS ASSOCIATED WITH A CELL

• US 20090113283A1
• Filed: 10/31/2007
• Published: 04/30/2009
• Est. Priority Date: 10/31/2007
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Baumgartner, Patrick J., Sol, Alisson A.S., Yang, Xiaohong Mark, Wang, Luming, Eberhardy, Peter
• Granted Patent: US 8,341,512 B2
• Time in Patent Office: Days
• US Class Current: 715/212
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

An interface for designing a report is provided, with elements of the report being disposed at intersections of report positions and configured to present information derived from data in a database. A formula is associated with one or more selected elements in the report to generate a result for the one or more selected elements. A tuple identifying the report positions of the one or more selected elements is associated and stored with the formula in a formula list associated with the report. The report is configured so that, when the report is generated, it is determined whether identifying report positions corresponding to an identified element in the report correspond with the tuple in the formula list. Upon determining the tuple corresponds with the identifying report positions for the identified element, the formula associated with the tuple is applied to generate the result.

Claims

1. A computer-implemented method, comprising:

providing a spreadsheet application interface for designing a report including a plurality of cells, each of the cells being disposed at an intersection of a spreadsheet coordinates corresponding with and configured to present information derived from data retrievable from a multidimensional database;

in one or more of the cells of the spreadsheet, receiving a formula specified in syntax understandable by the spreadsheet application, the formula being configured to generate a result in the one or more cells;

associating a tuple with the formula identifying the spreadsheet coordinates of the one or more cells with which the formula is associated, the spreadsheet coordinates being specified in terms of content represented by the row and column addresses;

generating a formula list associated with the report, the formula list including an entry for the tuple and the formula associated with the tuple;

storing the formula list in metadata associated with the report; and

configuring the report to be locally generated in the spreadsheet application, wherein locally generating the report includes;

retrieving the data from the multidimensional database referenced in the report;

determining whether the report includes the spreadsheet coordinates corresponding to the tuple in the formula list;

applying the formula stored in the formula list to the one or more cells in the report having the spreadsheet coordinates identified by the tuple; and

displaying the report as a spreadsheet in the spreadsheet application including results generated by the application of the formula.

2. The computer-implemented method of claim 1, wherein:

the spreadsheet coordinates identify an intersection of a row, a column, and one or more additional positions included in the report; and

the tuple associated with the formula identifies the spreadsheet coordinates according to a row position, a column position, and the one or more other additional report positions of the one or more cells included in the report with which the formula is associated.

3. The computer-implemented method of claim 1, wherein the spreadsheet coordinates identified by the tuple include one or more delimited parameters identifying multiple cells.

4. The computer-implemented method of claim 3, wherein when further comprising prioritizing application of formulas associated with the tuples according by which of the tuples includes fewer delimited parameters.

5. The computer-implemented method of claim 1, wherein the spreadsheet coordinates identified by the tuple include parameters indicating the substantive content of the one or more selected elements such that the tuple identifies the formula based on the report positions independent of physical dimensions of the report.

6. The computer-implemented method of claim 1, wherein the formula includes one of:

a design-time formula prepared by a designer of the report, and the formula list in which the design-time formula is stored includes a design-time formula list; and

a run-time formula prepared by a user of the report, and the formula list in which the run-time formula is stored includes a run-time formula list.

7. The computer-implemented method of claim 6, wherein access to the one or more of the design-time formula list and the run-time formula list is designated to one or more of:

one or more specified users; and

one or more groups of users.

8. A computer-implemented method, comprising:

providing an interface for designing a report including a plurality of elements, each of the elements being disposed at an intersection of report positions and configured to present information derived from data storable in a database;

receiving a formula associated with one or more selected elements in the report, the formula being configured to generate a result for the one or more selected elements;

associating a tuple with the formula identifying the report positions of the one or more selected elements with which the formula is associated;

storing the formula with the tuple in a formula list associated with the report; and

configuring the report so that generating the report includes;

determining whether identifying report positions corresponding to an identified element in the report matches the tuple in the formula list; and

upon determining the tuple matches the identifying report positions for the identified element, applying the formula associated with the tuple to generate the result.

9. The computer-implemented method of claim 8, wherein the receiving of the formula includes one or more of:

receiving a new formula; and

receiving one or more modifications to an existing a formula.

10. The computer-implemented method of claim 8, wherein:

the interface includes a spreadsheet application program; and

the formula includes spreadsheet terms recognized by the spreadsheet application program including one or more of;

a format;

one or more operands; and

one or more operators.

11. The computer-implemented method of claim 8, wherein the report positions identified by the tuple include parameters indicating the substantive content of the one or more selected elements such that the tuple identifies the formula based on the report positions independent of a physical layout of the report.

12. The computer-implemented method of claim 8, wherein the report positions identified by the tuple include one or more delimited parameters identifying multiple positions within one or more corresponding report positions, identifying multiple elements within the one or more corresponding report positions.

13. The computer-implemented method of claim 12, wherein the identifying report positions corresponding to the identified element in the report match multiple tuples in the formula list, prioritizing application of one or more other formulas associated with the tuples according to which of the tuples includes fewer delimited parameters.

14. The computer-implemented method of claim 8, wherein the formula includes one of:

a design-time formula prepared by a designer of the report, wherein the formula list associated with the report is configured to be accessible one of with the report and with specified users of the report; and

a run-time formula prepared by a user of the report, wherein the formula list associated with the report is configured to be accessible to one or more users of the report.

15. The computer-implemented method of claim 8, wherein the formula list is stored in metadata associated with the report.

16. A computer-readable storage medium storing instructions executable by a computing system, comprising instructions to:

provide a spreadsheet interface for designing a report, including;

specifying a plurality of elements to be included in the report, each of the elements being disposed at an intersection of report positions and configured to present information derived from data storable in a database; and

provide a formula design interface configured for one of creating and revising a formula associated with one or more selected elements in the report, the formula being configured to generate a result for the one or more selected elements;

generate a formula list configured to store;

a tuple identifying the report positions of the one or more selected elements with which the formula is associated; and

the formula in association with the tuple; and

associate provide a spreadsheet interface for designing a report, including;

generate the report, including;

determining whether identifying report positions corresponding to an identified element in the report matches the tuple in the formula list; and

upon determining the tuple matches the identifying report positions for the identified element, applying the formula associated with the tuple to generate the result.

17. The computer-implemented method of claim 16, wherein:

the spreadsheet interface includes a spreadsheet application program;

the formula includes spreadsheet terms recognized by the spreadsheet application program including one or more of;

a format;

one or more operands; and

one or more operators.

18. The computer-readable storage medium of claim 16, wherein the report positions identified by the tuple include parameters indicating the substantive content of the one or more selected elements such that the tuple identifies the formula based on the report positions independent of a physical layout of the report.

19. The computer-readable storage medium of claim 16, wherein the formula includes one of:

a design-time formula prepared by a designer of the report, wherein the formula list associated with the report is configured to be accessible one of with the report and with specified users of the report; and

a run-time formula prepared by a user of the report, wherein the formula list associated with the report is configured to be accessible to one or more users of the report.

20. The computer-readable storage medium of claim 19, wherein the formula list includes:

a design-time formula list for storing the one or more design-time formulas; and

a run-time formula list for storing the one or more run-time formulas.