Patent Application
20070050322
Summary tables are interactive worksheet tables used to summarize and analyze data from existing, and perhaps large, tables, databases or OLAP cubes. A user can manipulate the rows and columns in a summary table to view or summarize information in different ways or for different purposes of analysis. The data in the summary tables moves and changes as the summary table is manipulated. Problems arise when a user wants to highlight or conditionally format particular data within the summary table, such as the top ten percent of values, or values above a certain threshold.
When a conditionally-formatted summary table having highlighted data is manipulated, the originally highlighted data may be moved to a different location within the summary table, while the static highlighting remains with the particular cells where it was originally defined and applied. This may cause confusion as different data is now being highlighted in the original cells. It is with respect to these and other considerations that the present invention has been made.
Exemplary embodiments disclosed herein relate to a method and system for associating conditions with data points in a summary table, such that when the summary table is manipulated and the data points are moved, the conditions will still be applied to the same data points at their new locations.
Other embodiments include a method for associating conditions with related data points based upon the different fields that intersect with the active data point. Furthermore, a user may be able to select from the different groups of related data points to apply conditional formatting.
A system and methods described herein may be implemented as a computer process, a computing system, or as an article of manufacture, such as a computer program product. The computer program product may be a computer storage medium readable by a computer system and encoding a computer program of instructions for executing a computer process. The computer program product may also be a propagated signal on a carrier readable by a computing system and encoding a computer program of instructions for executing a computer process.
A system for applying conditions to data points within a summary table is shown in
Summary table 104 includes a data field 106. As shown in this particular embodiment, the data field 106 contains profit values, arranged in this instance by country, state and city, as well as by year and quarter as shown. It will be appreciated that many different configurations of fields on the row and column axes of the summary table may be utilized depending on the needs of the user. Furthermore, the fields and data points may be continuously reorganized by manipulating the fields within a summary table.
A plurality of selected cells are shown at 108. There may be many different ways for selecting cells, including clicking, double clicking, clicking and dragging, using keyboard arrow keys, etc. Selected cells 108 are indicated by border 110, as well as by shading. It will be appreciated that one or more cells may be selected, and that the selected cells may include one or more data points 112, as desired. A user may then apply a conditional format to the selected cells, such as applying a yellow highlight to positive values that are less than a predetermined number.
In one embodiment, once the cells have been selected and a conditional format applied, a user may click on an icon 113 to open a related data selection interface 114. This interface displays the different types of related data that may be included within the scope of the conditional format applied to the selected data points. In the embodiment shown in
Conditional formats that could be associated to the selected cells include highlighting cells with a value greater than a certain number, less than a certain number, within the top ten percent of all values in a defined region, within the bottom ten percent, etc. Utilizing the present invention, conditions may be associated to an explicit set of data points, all data points for a particular data field, or all data points where a particular set of fields intersect, depending on the scoping option chosen by the user. This information may then be saved to a summary table cache such that, when the summary table is rearranged, or more or different data is displayed, the condition is re-applied to these data points at their new locations in the summary table. A condition's scoping information may be saved via coordinates from a linked spreadsheet, database, and/or data source.
It will also be appreciated that these conditions and associated scoping information can be saved such that every time the summary table is viewed the conditions remain associated with the data points included in the condition's scope, regardless of whether these data points are currently hidden due to a manipulation of the summary table (in which case the formatting will also be hidden), or whether the data points are displayed (in which case the formatting will also be displayed).
System 200 may also contain communications connection(s) 212 that allow the system to communicate with other devices. Communications connection(s) 212 is an example of communication media. Communication media typically embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media. The term “modulated data signal” means a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communication media includes wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared and other wireless media.
System 200 also includes input devices 214 such as a keyboard, mouse, pen, voice input device, touch input device, etc. Output device(s) 216 such as a display, speakers, printer, etc. may also be included. All these devices are well known in the art and need not be discussed at length here.
A computing device, such as system 200, typically includes at least some form of computer-readable media. Computer-readable media can be any available media that can be accessed by the system 200. By way of example, and not limitation, computer-readable media may comprise computer storage media and/or communication media, as described above.
Within summary table 304 there may be one or more selected cells. Once cells have been selected and an “active cell” has been determined as described above, a determination module 310 determines related data points to those selected data points, as well as data point groups related to the active cell. Furthermore, in an embodiment, determination module 310 also preferably determines which commands (including the command to add a new conditional format) and/or rules may be applied to the selected data points and the related data points, and further sends the commands to the user interface 306 to be displayed.
The displayed commands and data points may then be selected by user input 308 via user interface 306. If the user chooses a command to add a new conditional format, information about the condition (e.g. the condition type, parameters, associated formatting) and selected data points are then sent to association module 312 via the determination module 310 and/or the user interface 306. In one embodiment, association module 312 is configured to associate the condition specified by the user with the selected data point(s). Furthermore, association module 312 may be configured to send and save that information to cache 314 such that the saved condition and associated data points may be utilized by the summary table 304. Alternatively, cache 314 may be configured to save the information for future display within the summary table 304. While the association module 312 is shown in the illustrated embodiment as being separate from the spreadsheet or data source 302, in an alternative embodiment of the present invention the association module 312 may be a part of the spreadsheet or other data source 302, or the association module 312 may be included within another portion of the system 300.
In this manner, conditional formats and associated data points may be saved such that when the particular data points are moved within the summary table 304, or removed from the display, the conditions remain associated with the data points. Furthermore, the cache 314 may be saved along with the summary table 304 such that when the summary table 304 is used again, or the data points are redisplayed, the conditional formats associated with the data points may still be displayed and configured. Typically, all components reside on one computer; however, one or more portions of system 300 may reside on separate computers, in separate locations, accessible via networks, etc., without deviating from the concepts disclosed herein.
Next, selection operation 404 includes the step of selecting one or more cells. A cell may be selected in many different ways, including clicking, double clicking, clicking and dragging, using keyboard arrow keys, etc. Selection operation 404 also includes the step of determining an “active cell” as described above (e.g., determining a top left cell within the selected cells).
In one embodiment, the method 400 includes a display operation 406 where potential conditional formatting options are displayed to a user. As described below with respect to
Once one or more cells have been selected and a conditional format has been selected in operation 408, control passes to determine operation 410 to determine or identify related groups of data points. The system identifies related data points and groups of data points based upon the cells selected and/or the data field within the “active cell.” The related groups of data points may include only the data points in the highlighted cells, data points displaying numbers from the same data field as the active cell, and/or all data points where the same set of fields intersect as in the active cell. One embodiment of the determine operation 410 is described below with respect to
The selected condition is actually associated with the previously specified data points or groups of data points in association operation 414. In particular, following the association operation 414, the condition remains associated with the selected data points throughout the summary table, even when the data within the summary table is reorganized so that the selected data points occupy different cells within the table. Furthermore, the condition remains associated with the selected data points even when the data points are not currently displayed in the reorganized summary table. Thus, when a currently non-displayed data point has a conditional formatting rule associated with it, the conditional format will be still applied when the data point is later displayed (i.e., following a reorganization of the summary table).
Finally, save operation 416 saves the conditions with the associated data points, typically to a cache 314 which is associated with the summary table 304, as illustrated in
A second icon 605 within the exemplary embodiment of the user interface 600 provides access to a second drop down menu 606 where a user may access additional condition types, namely “top/bottom” rules that apply conditional formatting to the highest or lowest values in an identified range of data items (e.g., top ten, top five percent, bottom twenty percent, above average, etc.).
In an embodiment, a third icon 607 provides access to additional condition types contained in a further drop down menu 608. This menu 608 contains options for visualizing or highlighting the different data points according to their relative values. Thus, different colors, icons or data bars may be applied to the data points according to the specific conditional formatting rule selected by the user. For example, a user may configure a “3 color gradient” condition to apply a green gradient to data entries that are in the top twenty percent of the selected data items, and a red gradient to the bottom thirty percent of the selected data entries.
In one embodiment, user interface 700 includes a data point identifier portion at 702, which would allow the user to directly select the fields and/or data points to be utilized, either by typing a range within the text box or by clicking the icon 703 to allow the user to select the cells directly on the spreadsheet. Additionally, the data identifier portion 702 also preferably includes radio buttons 704, 706 and 708 for identifying related data points and/or groups, as shown in
In the exemplary embodiment shown in
The logical operations of the various exemplary embodiments may be implemented (I) as a sequence of computer implemented acts or program modules running on a computing system and/or (2) as interconnected machine logic circuits or circuit modules within the computing system. The implementation is a matter of choice dependent on the performance requirements of the computing system implementing the invention. Accordingly, the logical operations making up the embodiments of the exemplary embodiments described herein are referred to variously as operations, structural devices, acts or modules. It will be recognized by one skilled in the art that these operations, structural devices, acts and modules may be implemented in software, in firmware, in special purpose digital logic, and/or any combination thereof without deviating from the spirit and scope of the present disclosure as recited within the claims attached hereto.
Although the exemplary embodiments have been described in language specific to computer structural features, methodological acts, and by computer readable media, it is to be understood that the exemplary embodiments defined in the appended claims are not necessarily limited to the specific structures, acts or media described. As an example, different modules may be utilized to receive the selected conditional formats and groups of data entries, and to associate the two. Therefore, the specific structural features, acts and mediums are disclosed as exemplary embodiments implementing the claimed invention.
The various embodiments described above are provided by way of illustration only and should not be construed to limit this disclosure. Those skilled in the art will readily recognize various modifications and changes that may be made to the present disclosure without following the exemplary embodiments and applications illustrated and described herein, and without departing from the true spirit and scope of the present disclosure, which is set forth in the following claims.
