Automatic formatting of pivot table reports within a...

Details Automatic formatting of pivot table reports within a... Automatic formatting of pivot table reports within a...

1999-06-14

2003-09-30

Feild, Joseph H. (Department: 2176)

Data processing: presentation processing of document, operator i

Presentation processing of document

Layout

C715S252000, C715S252000, C715S252000, C715S965000

Reexamination Certificate

active

06626959

ABSTRACT:

FIELD OF THE INVENTION
The present invention generally relates to pivot tables within a spreadsheet program, and more specifically, to the formatting of such pivot tables.
BACKGROUND OF THE INVENTION
Pivot tables have been provided in Microsoft Corporation's EXCEL™ spreadsheet program for several years. A pivot table provides an efficient way to display and summarize data that is included in a database or in the data listing of a spreadsheet by automatically displaying fields of the data in a manner determined by the user and by determining and displaying selected parameters such as the sum, variance, count, standard deviation, etc. of selected data fields. Relatively structured spreadsheets that already have subtotals, data entry cells, and summaries of fields are generally not appropriately expressed using a pivot table. In contrast, any data included in a database that can be queried from within the spreadsheet, or spreadsheet data comprising lists that are not already summarized are ideal candidates for the power of pivot tables.
Although pivot tables are a very powerful tool for processing and displaying data, they often are not in an acceptable form for presentation in a written or live report when they are initially produced. The creation of a pivot table produces a tabular grouping of data laid out in a non-traditional grid format that is generally plain, wherein the information provided by the data may not be intuitive to some observers, or be difficult to understand. It would be preferable to arrange the data presented in a pivot table in a more traditional format, such as a banded report layout.
Banded reports have been the predominate database reporting paradigm since use of computers in large corporations became commonplace. In a typical scenario, a manager might make a request to a corporation's computing department (somewhat analogous to today's MIS department) for a report showing the sales of products A, B, and C, organized by territory and responsible sales representative, on a quarterly basis, for the past 3 years. The computing department would then deliver the requested information printed on paper, organized in a tiered, hierarchical form, usually with subtotals for each level and sublevel of the hierarchy. A hierarchy in such a report is generally formed by grouping individual fields together, based on shared field attributes or values. Although this process was slow and cumbersome, the form of the data as presented was very intuitive and understandable. As a result, banded reports have become a de facto standard for organizing the presentation of such data in reports.
At present, the most common types of programs that generate banded reports are database applications. A prior art example of such an application is the BRIO QUERY program distributed by the Brio Technology Corporation of Mountain View, Calif. This program enables the creation of a banded report called a “Pivot Report” through the use of a tool called an “Outliner.” However, the data in the banded report are developed by applying a query to database data. The data used to create the Pivot Report and the Pivot Report itself cannot be manipulated like data in a spreadsheet or in the pivot table of a spreadsheet program. This inability to manipulate the data in the prior art Pivot Report, for example, by associating formulas or functions with portions of the data, greatly limits the flexibility of a database-type banded report; reformulating various fields in the report, such as totals or averages, cannot be accomplished in this prior art Pivot Report without recreating the entire report through a new query. Additionally, pivot table functionality, such as drilldown (which enables only selected groups of data to be displayed in the pivot table), is not available in a banded report created within this prior art database program. Clearly, it would be desirable to provide a spreadsheet program that could generate a banded report based on data included in a spreadsheet pivot table, while maintaining the functionality of the spreadsheet for manipulating data and other aspects of the pivot table. It would also be desirable to easily add or change various formatting attributes, such as font color, size, style, and borders of existing pivot tables or banded reports created therefrom.
SUMMARY OF THE INVENTION
The present invention overcomes the foregoing limitations of the prior art by providing a method that enables a user to selectively reformat a spreadsheet pivot table into one of a plurality of predefined formats, including various banded report formats. The pivot table can be reformatted automatically, based on a user selection from a plurality of predefined banded table and tabular formats, or it may be incrementally formatted through user interaction with a plurality of formatting dialogs. The reformatted pivot table provides an enhanced appearance to a pivot table, while maintaining its full functionality within the spreadsheet program.
According to a first aspect of the invention, a method is provided for enabling a user to selectively reformat a spreadsheet pivot table presenting a specified set of data with an original format. The pivot table has an original functionality comprising a set of direct manipulation pivot table operations that can be performed on the pivot table and which affect the data contained within it. The method provides a user interface that enables a user to select a predefined banded report format to be applied to the pivot table from a plurality of predefined banded report formats, preferably from a plurality of thumbnail representations that graphically portray an exemplary appearance of the reformatted pivot table. The pivot table is then automatically reformatted based on the selected banded report format and specified set of data. The reformatted pivot table maintains its original functionality, thereby enabling a user to perform any of the direct manipulation pivot table operations on the reformatted pivot table that could be performed on the pivot table prior to reformatting. The plurality of predefined banded report formats includes formats that comprise outlined fields, and differ in various other ways, such as displaying grouped summary calculations in the first line or last line of a group, and inserting blank lines between groups of data. The plurality of predefined formats also comprises a variety of color, border, and font style schemes, enabling the user to easily produce a desired banded table report based on the pivot table.
In order to obtain the desired banded table report format, it may be necessary to rotate fields within the pivot table. As with all pivot tables, the original format of the pivot table comprises a left-hand portion defining a row region comprising a first row listing names of row fields and successive rows containing associated data items for the row fields and calculated-data labels. A data region is disposed adjacent to the row region and includes calculated numerical data associated corresponding to the calculated-data labels. A column field region is disposed immediately above the data region and includes field names included within the column region and labels corresponding to the calculated numerical data.
The rotation of the fields when creating the reformatted pivot table is performed by pivoting any column fields to the row region, and pivoting the calculated data labels to the column region.
The plurality of predefined formats preferably also includes a plurality of tabular formats, again comprising a variety of different color, border, and font style schemes. A primary difference between the banded report formats and the tabular formats is that the latter doesn't provide for field outlining. The list of predefined formats also includes an option to return a reformatted pivot table back to its original format, and to produce a pivot table with no formatting.
According to another aspect of the invention, a method is defined that enables a user to selectively reformat a pivot table on an incremental bas

Affiliated with

Also associated with

Rating

Automatic formatting of pivot table reports within a... does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Automatic formatting of pivot table reports within a..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Automatic formatting of pivot table reports within a... will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3070143