Converting structured reports to formulas

• US 20070061344A1
• Filed: 11/07/2005
• Published: 03/15/2007
• Est. Priority Date: 09/09/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Hawking, Robert, Hu, Kaicheng, Dickerman, Howard
• Granted Patent: US 7,792,847 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/248 Presentation of query results
• 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: Y10S 707/957 Multidimensional

Abstract

Data corresponding to a structured report or multi-dimensional presentation is converted to formulas within cells of a spreadsheet. The cell formulas operate on a multi-dimension database that was the data source for the structured report. Therefore the layout of the data in the cells is similar to the layout of the data in structured report. The user is able to move the formulas around in the spreadsheet resulting in any custom layout the user desires. Because the data is no longer in a structured report, it is no longer subject to the layout restrictions of the report.

Claims

1. A computer-implemented method for converting a structured report into formulas, comprising:

converting dimension members included in the structured report into formulas of a first type; and

converting measures included in the structured report into formulas of a second type, wherein formulas of the second type depend on calculation of the formulas of the first type.

2. The computer-implemented method of claim 1, wherein the first type of formula is arranged to obtain at least one of the following from a multi-dimensional database associated with the structured report:

a cube member;

a cube set; and

a member property.

3. The computer-implemented method of claim 1, wherein the second type of formula is arranged to obtain at least one of the following from a multi-dimensional database associated with the structured report:

a cube value; and

a cube set; and

a member property.

4. The computer-implemented method of claim 1, further comprising providing a formula presentation, wherein the formula presentation provides a display of data retrieved from a multi-dimensional database in response to execution of the formulas.

5. The computer-implemented method of claim 4, wherein obtaining the data from the multi-dimensional database comprises querying for the data in the multi-dimensional database based on the type and arguments of the formula provided.

6. The computer-implemented method of claim 4, wherein a report filter is retained in the formula presentation without converting the report filter to a formula, such that filtering of the data according to the report filter is reflected in the formula presentation.

7. The computer-implemented method of claim 6, wherein metadata associated with the report filter allows other formulas within the formula presentation to reference the report filter as if the report filter had been converted to a formula.

8. The computer-implemented method of claim 4, wherein metadata associated with each cell of the formula presentation is arranged to provide similar formatting of the data as was provided for that data by the structured report.

9. The computer-implemented method of claim 4, wherein formulas of the first type include a caption that is displayed in the formula presentation in place of data retrieved from the multi-dimensional database.

10. The computer-implemented method of claim 1, wherein a formula of the first type includes a tuple when additional dimensions are included in the structured report in at least one of a left and upward direction from the formula.

11. A computer-readable medium having stored thereon instructions that when executed implements the method of claim 1.

12. A computer-readable medium having computer-executable instructions for converting a structured report to a spreadsheet that includes formulaic cells, comprising:

converting dimension member cells of the structured report to member formulas, wherein the member formulas are included in the formulaic cells of the spreadsheet that correspond to the associated dimension member cells of the structured report;

converting value cells of the structured report to value formulas, wherein the value formulas are included in the formulaic cells of the spreadsheet that correspond to the associated value cells of the structured report; and

arranging the value formulas to reference the member formulas such that when underlying data of a multi-dimensional database that is linked to the structured report and spreadsheet is changed, values in the formulaic cells correspond to the value formulas are updated to reflect the changes.

13. The computer-readable medium of claim 12, wherein set formulas are created to identify at least one of a set of members from within the multi-dimensional database and a set of tuples from within the multi-dimensional database.

14. The computer-readable medium of claim 12, further comprising retaining a report filter included in the structured report for presentation of the spreadsheet.

15. The computer-readable medium of claim 14, wherein the report filter is maintained according to an updated structured report associated with the spreadsheet, wherein the updated structured report uses similar properties as the structured report that was converted into the spreadsheet.

16. The computer-readable medium of claim 12, wherein a compact axis included in the structured report is represented by a member formula that includes a tuple while displaying a caption for a member in that tuple.

17. The computer-readable medium of claim 12, wherein the member formulas and value formulas are comprised of a set of cube functions including at least one of the following:

a cube value function;

a cube member function;

a cube set function; and

a member property function.

18. A system for converting a structured report into a spreadsheet, comprising:

at least one application that includes functionality for presentation a structured report and spreadsheet and is configured to perform steps, comprising;

finalizing the structured report;

converting the cells of the structured report into formulas for inclusion in the formulaic cells of the spreadsheet, wherein the formulas are arranged so that when value data of an underlying multi-dimensional database is changed, formulaic cells of the spreadsheet are updated to reflect the changes;

coupling the spreadsheet to the multi-dimensional database according to a property of at least one of the formulas;

creating queries for data in the multi-dimensional database based on the formulas; and

fetching the data from the multi-dimensional database; and

displaying the results within the formulaic cells of the spreadsheet such that the display of the spreadsheet is similar to the display of the structured report.

19. The system of claim 18, wherein converting the cells of the structured report into formulas further comprises referencing the formulaic cells of the spreadsheet corresponding to dimensions of the structured report with the formulas in the formulaic cells of the spreadsheet corresponding to values of the structured report.

20. The system of claim 18, wherein the at least one application further includes functionality for retaining a report filter provided according to the structured report in the display of the spreadsheet.