The disclosed subject matter relates to the field of computer systems and, more particularly, to a system and method for generating a set of pivot table layouts to display a set of data fields.
Computer systems often use spreadsheet applications to display sets of data. Pivot tables have been an integral part of the spreadsheet applications to display summarized data. For example, sales data may be summarized based on time (e.g., weeks, months) or location (e.g., cities, states/provinces, regions), and pivot tables have been a useful utility to display the summarized data.
Tools have been developed to help creation or customization of pivot tables. For example, modern spreadsheet applications sometimes provide a software wizard that facilitates generation of a pivot table. The wizard typically allows a user to select the source data from a worksheet list or external database, then provides the user with a worksheet area for the report and a list of the available fields, and then let the user to drag the fields from the list window to the outlined areas. If a pivot table has been generated, the spreadsheet applications can enable customization to re-arrange the table to focus on the information the user wants. For example, the user can change the layout, drag-and-drop data fields, change the format, or drill down to display more detailed data.
However, creating a pivot table for related data fields is a daunting task for an inexperienced user. A lot of times, it is difficult to determine which fields should be combined to generate summarized data and how to select a layout that is easy to interpret the data. While the existing tools are helpful to experienced users, they do not provide enough help to inexperienced users to create meaningful tables with combinations of fields that may be useful and easy to interpret. Accordingly, a need exists for a method and system to help inexperienced users to generate a set of pivot table layouts to display a set of data fields.
Embodiments of the present invention may provide a method for generating a meaningful pivot table. In one embodiment, the method may comprise receiving a command to generate a pivot table for a plurality of related data fields. The data fields may be related to each other in a hierarchy or other relationships. The method may further comprise generating a plurality of pivot table layouts based on a variety of combinations of the related data fields. The combinations may keep an order to maintain the hierarchy of the data fields. The method may further comprise displaying the plurality of pivot table layouts to a user, receiving a user input selecting one particular pivot table layout from the plurality of layouts, and displaying a pivot table according to the selected pivot table layout.
While the pivot tables may be used to display summarized data, the summation of data may be based on a relationship of selected data fields.
In one embodiment, the possible pivot table layouts may be generated based on the relations between the selected data fields (e.g., a one to many relation between two fields). Further, when the data fields may have a hierarchy, the summation and layouts may be in an order to maintain the hierarchy. For example, the layout 208 may show the data being displayed according to the hierarchy of each data field 1 containing multiple data field 2, and each data field 2 containing multiple data field 3. The layouts 210 and 212 may take advantage of two dimensions of a tabular layout. In the layout 210, data field 1 containing multiple data field 2 may be shown in one dimension and data field 3 may be shown in another dimension. In the layout 212, data field 1 may be shown in one dimension and data field 2 containing multiple data field 3 may be shown in another dimension. Thus, as long as the order is maintained, any combination of data fields may be used as part of a pivot table. It should be noted that the position of the data fields may be inverted. That is, data fields may be either on top or left of a tabular presentation. Accordingly, the number of possible layouts may be doubled.
The data fields may be grouped in field sets. The number of sets may be calculated by the equation of
where N may stand for the number of data fields available. For the example of the data field 1, 2 and 3, the available field sets may seven field sets include, data field 1, data field 2, data field 3, data field 1 and data field 2, data field 1 and data field 3, data field 2 and data field 3, data field 1 and data field 2 and data field 3. If a data field is not shown in a tabular layout, it may be used as filter fields to filter the data to be displayed.
In one embodiment, the command button 304 may be a drop down command list that contains a plurality of dynamically generated pivot table layouts based on the selected data fields as shown in
The processor 902 is a programmable processor that executes instructions residing in the memory 904 to receive and send data via the I/O device(s) 906. The instructions may perform the operations of a message handling system according to an exemplary embodiment. The term programmable processor as used herein is any programmable microprocessor or processor or combination of microprocessors or processors that can operate on digital data, which may be special or general purpose processors coupled to receive data and instructions from, and to transmit data and instructions to, a machine-readable medium. According to one embodiment of the present invention processor 902 is an Intel® microprocessor.
Memory 904 is a machine-readable medium that stores data that is processed by processor 902. The term machine-readable medium as used herein is any addressable storage device that stores digital data including any computer program product, apparatus and/or device (e.g., a random access memory (RAM), read only memory (ROM), magnetic disc, optical disc, programmable logic device (PLD), tape, hard drives, RAID storage device, flash memory or any combination of these devices). This may include external machine-readable mediums that are connected to processor 902 via one or more I/O device(s) 906.
The I/O device(s) 906 may be one or more input/output interfaces that receive and/or send digital data to and from an external device. Interfaces as used herein are any point of access to an external device where digital data is received or sent, including ports, buffers, queues, subsets thereof, or any other interface to an external device.
The exemplary method and computer program instructions may be embodied on a machine readable storage medium such as a computer disc, optically-readable media, magnetic media, hard drives, RAID storage device, and flash memory. In addition, a server or database server may include machine readable media configured to store machine executable program instructions. The features of the embodiments of the present invention may be implemented in hardware, software, firmware, or a combination thereof and utilized in systems, subsystems, components or subcomponents thereof. When implemented in software, the elements of the invention are programs or the code segments used to perform the necessary tasks. The program or code segments can be stored on machine readable storage media. The “machine readable storage media” may include any medium that can store information. Examples of a machine readable storage medium include electronic circuits, semiconductor memory device, ROM, flash memory, erasable ROM (EROM), floppy diskette, CD-ROM, optical disk, hard disk, fiber optic medium, or any electromagnetic or optical storage device. The code segments may be downloaded via computer networks such as Internet, Intranet, etc.
Although the invention has been described above with reference to specific embodiments, the invention is not limited to the above embodiments and the specific configurations shown in the drawings. For example, some components shown may be combined with each other as one embodiment, or a component may be divided into several subcomponents, or any other known or available component may be added. The operation processes are also not limited to those shown in the examples. Those skilled in the art will appreciate that the invention may be implemented in other ways without departing from the spirit and substantive features of the invention. For example, features and embodiments described above may be combined with and without each other. The present embodiments are therefore to be considered in all respects as illustrative and not restrictive. The scope of the invention is indicated by the appended claims rather than by the foregoing description, and all changes that come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein.