Patent Application
20100228752
A summary table is a data summarization tool that allows detailed, transactional source data to be “rolled-up” or summarized at varying levels of detail. Summary table functionality, also known as pivot tables, can be found in data visualization and analysis programs such as spreadsheets programs and data reporting tools. The data analysis program may allow a user to draw the summary table as a grid, specifying data fields from the source data for the row and column labels of the grid as well as the data values summarized in the grid. The data analysis program may then group, sort, count, and/or total the data values in the grid based on the row and column labels extracted from the source data.
The data analysis program may allow the user to specify multiple data fields for the row and/or column labels of the summary table. In this case, the data analysis program traditionally displays the data values summarized hierarchically by the row or column labels based on the order of the data fields specified. The data analysis program may also allow the user to interactively manipulate the summary table, expanding and collapsing row labels or column labels in the hierarchy to display more or fewer data values, depending on the user's requirements.
Some implementations of interactive summary tables may allow the user to limit the data displayed by specifying data filters for the summary table. The data filters may be report level filters, which limit the source data utilized by the data analysis program in building the summary table, or the data filters may be row or column filters, which allow the user to choose which rows or columns are displayed according to the value of the corresponding row or column labels or the data values summarized in that row or column.
Because of limitations in the user interfaces for specifying the data filters, these implementations are generally limited to one filter criteria or condition per row or column, and do not allow for complex data filters containing multiple, additive conditions to be created. Such complex data filters may be necessary when searching for specific pieces of information among a large set of diverse source data. In these cases, the user may have to resort to utilizing database experts to pre-filter the source data by using query expressions or other mechanisms before the data is accessed by the data analysis program. This adds complexity and resources to the reporting process and may result in a specialized summary table that limits the user's ability to interactively manipulate the data displayed.
It is with respect to these considerations and others that the disclosure made herein is presented.
Technologies are described herein for allowing a user of an interactive summary table to specify multi-condition data filters to modify the data displayed in the summary table. Utilizing the technologies described herein, a data analysis program may provide a user interface that allows the user to create complex, multi-condition data filters, as well as group and order the conditions of the data filters so that the intended data is displayed in the summary table. This may allow the user to display a particularized subset of data rows or columns in the summary table from a large, diverse set of source data, without having to employ a database expert to pre-filter the source data before it is read by the data analysis program.
According to one embodiment, a user interface is displayed to a user that allows the user to specify a multi-condition data filter. The specification of the multi-condition data filter includes a set of filter conditions connected by logical operators. One or more filter expressions are parsed from the specification of the multi-condition data filter based on the filter conditions and the logical operators, and the filter expressions are applied to the summary data from which the summary table is displayed.
It should be appreciated that the above-described subject matter may be implemented as a computer-controlled apparatus, a computer process, a computing system, or as an article of manufacture such as a computer-readable medium. These and various other features will be apparent from a reading of the following Detailed Description and a review of the associated drawings.
This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended that this Summary be used to limit the scope of the claimed subject matter. Furthermore, the claimed subject matter is not limited to implementations that solve any or all disadvantages noted in any part of this disclosure.
The following detailed description is directed to technologies for providing multi-condition filtering of data displayed in an interactive summary table. While the subject matter described herein is presented in the general context of program modules that execute in conjunction with the execution of an operating system and application programs on a computer system, those skilled in the art will recognize that other implementations may be performed in combination with other types of program modules. Generally, program modules include routines, programs, components, data structures, and other types of structures that perform particular tasks or implement particular abstract data types. Moreover, those skilled in the art will appreciate that the subject matter described herein may be practiced with other computer system configurations, including hand-held devices, multiprocessor systems, microprocessor-based or programmable consumer electronics, minicomputers, mainframe computers, and the like.
In the following detailed description, references are made to the accompanying drawings that form a part hereof, and that show, by way of illustration, specific embodiments or examples. In the accompanying drawings, like numerals represent like elements through the several figures.
The computer 102 executes a data analysis program 104. The data analysis program 104 is an application program that allows a user 106 of the computer 102 to visualize and manipulate data contained in a data source 108 accessible by the computer. The data analysis program 104 may be a spreadsheet program, such as the MICROSOFT® EXCEL® spreadsheet software from Microsoft Corporation of Redmond, Wash., or the IBM® LOTUS® 1-2-3® spreadsheet software from IBM Corporation of Armonk, N.Y. The data analysis program 104 may also be a data reporting application, such as SAP® BUSINESSOBJECTS™ CRYSTAL REPORTS from SAP, AG of Waldorf, Germany. The data source 108 may be a file system attached to or accessible by the computer 102 and may contain spreadsheet files or other data files. The data source 108 may also be a database or multi-dimensional online analytical processing (“OLAP”) cube containing detailed and/or summarized data which may be queried by the data analysis program 104.
The data analysis program 104 may include a summary table data access module 110 that is operative to retrieve source data from the data source 108 and transform the source data through summarization functions into summary data. A summary table UI module 112 may then display the summary data to the user 106 on a display device 114 connected to the computer 102. The display device 114 may be a computer monitor, a flat-panel display, a digital projector, a printer, a plotter, or any other output device attached to the computer 102. The display device 114 may also be a Web browser application program or other application program executing on a remote computing device accessing the computer 102 over a network. The user may utilize input devices 116 connected to the computer 102 or remote computing device, such as a keyboard, mouse, touchscreen, stylus, or trackball, to control or manipulate the display of the summary table on the display device 114 through the summary table UI module 112.
According to embodiments, the data analysis program also includes a summary table data filter module 118. The summary table data filter module 118 accepts the specification of one or more data filters from the user 106 through a user interface (“UI”) displayed by the summary table UI module 112, and filters the data that is displayed in the summary table accordingly, as will be described in more detail below. It will be appreciated that, while the summary table data access module 110, the summary table data filter module 118, and summary table UI module 112 are described herein as separate modules of the data analysis program 104, these modules are utilized herein to refer to specific functionality of the data analysis program, which may in practice be implemented in more, fewer, or different program modules than those described herein. It is not intended that the description of these modules impart any limitations on the programmatic structure of the data analysis program 104.
The SALESMAN row labels 210 may rollup to a COUNTRY field from the source data, while the column labels may rollup by YEAR. The summary table 204 also includes two data values fields 212A, 212B, summarized for each row and column label: a SALES data value field 212A and a PROFITS data value field 212B. For example, the data value 214 shown in
According to one embodiment, the user 106 may specify multiple criteria or filter conditions 304A-304D (referred to herein collectively as filter conditions 304) for the data filter. Additional filter conditions 304 may be added to the data filter window 302 by the user 106 selecting an add condition UI control 306, such as the “ADD CONDITION” button control shown in
The filter conditions 304 are connected by logical operators which the user 106 may select for each additional condition using a logical operator selection control 310A-310C (referred to herein collectively as logical operator selection control 310). In one embodiment, the user may select an “AND” or an “OR” operator for each additional filter condition 304 added in the data filter window 302. The logical operators connecting the filter conditions 304 determine how the data filters will be applied by the summary table data filter module 118, as will be described in more detail below in regard to
It will be appreciated that the order of evaluation of the logical operators between the filter conditions 304 may have an impact on the corresponding filtering of the summary data that will be applied. According to one embodiment, the logical operators are evaluated in a default order corresponding to the order in which the filter conditions 304 are specified. From the example shown in
(((SALESMAN contains “A” AND SALESMAN is between “C” and “F”) OR SALESMAN equals “LEVERLING”) AND SALESMAN is less than “Z”)
In another embodiment, the data analysis program 104 may provide the user 106 with the ability to group filter conditions 304 together in order to modify the order of evaluation of the logical operators, as will be described in more detail below in regard to
In addition, the data filter window 302 may allow the user 106 to change the order of the filter conditions 304 listed in the window. The user may utilize reorder condition UI controls 312, 314, such as the up and down button controls further shown in
It will be appreciated that other embodiments of the data filter window 302 may be imagined that allow multi-condition data filters to be specified for the rows 206 or columns 208 of the summary table 204, including, but not limited to, a “top 10” data filter containing multiple “top 10” filter conditions 304 related to the total values 216A, 216B shown in the summary table, or a date range data filter containing multiple date range filter conditions 304 for the ORDER DATE field reflected in the columns of the summary table. In a further embodiment, a custom data filter window 302 may be provided by the summary table UI module 112 that allows the user 106 to specify multiple filter conditions 304 of different types for the same entity, such as the data filter window shown in
(TOTAL PROFITS is greater than 0 AND Top 2 items by TOTAL SALES)
As described above in regard to
(Top 2 items by TOTAL SALES AND TOTAL PROFITS is greater than 0)
In one embodiment, the above expression and the filter expression corresponding to the multi-condition data filter discussed above in regard to
According to embodiments, a number of multi-condition data filters may be specified by the user 106 to be applied to the summary table 204. These multi-condition data filters may be applied using separate invocations of the data filter window 302 to specify different data filter types for the same entity or different data filters for different entities. For example, the user 106 may utilize the data filter window 302 to specify a data filter having multiple filter conditions 304 related to the TOTAL SALES value 216A and TOTAL PROFITS value 216B corresponding to each SALESMAN row label 210 in the summary table 204, as discussed above in regard to
((TOTAL SALES is less than 125,000 OR TOTAL SALES is greater than 150,000) AND TOTAL PROFITS is greater than 0)
In addition, the user 106 may further utilize the data filter window 302 to specify a data filter limiting the displayed rows to the SALESMAN row labels 210 having the top two corresponding TOTAL SALES values 216A. This may result in the following filter expression being further applied to the summary table 204:
(Top 2 items by TOTAL SALES)
It will be appreciated that the order of application of the two multi-condition data filters described above to the summary data may affect the resulting summary table rows 206 that are displayed in the summary table 204. Accordingly, the data analysis program 104 may provide an additional UI, such as the UI 600 shown in
The data filter expressions 604A, 604B are listed in the current order of application to the summary data, with the multi-condition data filter related to the TOTAL SALES value 216A and TOTAL PROFITS value 216B applied first, and the data filter related to the top two SALESMAN rows applied second. If the listed data filters were applied in the listed order to the summary table 204 described above in regard to
As shown in
The data filter ordering window 602 also contains an “up” reorder filter UI control 606 and a “down” reorder filter UI control 608, as further shown in
If data filter expressions 604B, 604A were applied in the order shown in
For example, the user 106 may select the second filter condition 304B and the third filter condition 304C in the data filter window 302 shown in
((SALESMAN contains “A” AND (SALESMAN is between “C” and “F” OR SALESMAN equals “LEVERLING”)) AND SALESMAN is less than “Z”)
It will be appreciated that this filter expression may be evaluated differently by the summary table data filter module 118 than the filter expression corresponding to the same four filter conditions 304A-304D specified in the data filter window 302 described above in regard to
According to one embodiment, the summary table UI module 112 indicates the currently grouped filter conditions 304B and 304C in the data filter window 302 by adding a graphical grouping display 806 to the window in conjunction with the display of the filter conditions 304, as further shown in
By way of an additional example, the user 106 may further select the second filter condition 304B, the third filter condition 304C, and the fourth filter condition 304D in the data filter window 302, as indicated by the selection box 502 shown in
(SALESMAN contains “A” AND ((SALESMAN is between “C” and “F” OR SALESMAN equals “LEVERLING”) AND SALESMAN is less than “Z”))
In addition, the summary table UI module 112 may update the graphical grouping display 806 to indicate the two nested groups of filter conditions 304B and 304C and filter conditions 304B, 304C, and 304D, created by the user in the data filter window 302, as further shown in
Referring now to
From operation 902, the routine 900 proceeds to operation 904, where the summary table data filter module 118 receives the specification of one or more multi-condition data filters. According to one embodiment, the multi-condition data filters are specified by the user 106 interactively using the data filter window 302 provided by the summary table UI module, as described in detail above in regard to
The routine 900 proceeds from operation 904 to operation 906, where the summary table data filter module 118 parses the filter expressions from the data filters specifications received in operation 904. The filter expression parsed from a multi-condition data filter will depend upon the individual filter conditions 304 specified in the data filter, the logical operators connecting the filter conditions, the order in which the filter conditions were specified, and any groupings specified between filter conditions. For example, the multi-condition data filter specified in data filter window 302 shown in
(((SALESMAN contains “A” AND SALESMAN is between “C” and “F”) OR SALESMAN equals “LEVERLING”) AND SALESMAN is less than “Z”)
This expression results from the order of the filter conditions 304 and the logical operators specified between each filter condition. Here, default grouping is applied in the absence of any specified groupings.
Similarly, the multi-condition data filter specified in data filter window 302 shown in
(SALESMAN contains “A” AND ((SALESMAN is between “C” and “F” OR SALESMAN equals “LEVERLING”) AND SALESMAN is less than “Z”))
This variation of the filter expression from the same four filter conditions 304A, 304B, 304C, and 304D used above results from the specification by the user 106 of the two nested groups of filter conditions 304B and 304C, and filter conditions 304B, 304C, and 304D, made in the data filter windows 302.
According to one embodiment, the parsing of certain multi-condition data filters may result in multiple, separate filter expressions, because of the need for the filter expressions to be applied sequentially to the summary data. For example, the multi-condition data filter specified in the data filter window 302 shown in
(PROFITS is greater than 0)
and
(Top 2 items by SALES)
In this case, the filter expressions should be applied sequentially: the set of SALESMAN rows where the TOTAL PROFITS 216B are greater than zero established first, and then the top two SALESMAN rows by TOTAL SALES value 216A selected from that set.
From operation 906, the routine 900 proceeds to operation 908, where the summary table data filter module 118 determines the order in which to apply the filter expressions parsed from the multi-condition data filters in operation 906 above. The order of application of the multi-condition data filters may be based on the order in which the data filters were created by the user 106, or the order may be specified by the user using the UI 600 discussed above in regard to
Next, the routine 900 proceeds from operation 908 to operation 910, where the summary table data filter module 118 applies the filter expressions parsed from the multi-condition data filters to the summary data in the order established in operation 908 above. When multiple filter expressions are to be applied in a specific order, the summary table data filter module 118 may be required to recalculate aggregate summary values, such as row and column totals as well subtotals in any hierarchies displayed in the summary table 204, between the application of each filter expression. In other words, the input to a subsequent filter expression is the result of the application of a previous filter expression with any aggregate values recalculated after the application of the previous filter expression. This may be accomplished by applying a methodology similar to that described in U.S. Patent Pub. No. US 2006-0287998 A1, filed on Jun. 21, 2005, and entitled “Dynamically filtering Aggregate Reports Based on Values Resulting from One or More Previously Applied Filters,” which is expressly incorporated herein by reference in its entirety.
From operation 910, the routine 900 proceeds to operation 912, where the summary table data filter module 118 passes the filtered summary data to a display module, such as the summary table UI module described above in regard to
The computer architecture shown in
The mass storage device 1010 is connected to the CPU 1002 through a mass storage controller (not shown) connected to the bus 1004. The mass storage device 1010 and its associated computer-readable media provide non-volatile storage for the computer 1000. Although the description of computer-readable media contained herein refers to a mass storage device, such as a hard disk or CD-ROM drive, it should be appreciated by those skilled in the art that computer-readable media can be any available computer storage media that can be accessed by the computer 1000.
By way of example, and not limitation, computer-readable media may include volatile and non-volatile, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules, or other data. For example, computer-readable media includes, but is not limited to, RAM, ROM, EPROM, EEPROM, flash memory or other solid state memory technology, CD-ROM, digital versatile disks (DVD), HD-DVD, BLU-RAY, or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and that can be accessed by the computer 1000.
According to various embodiments, the computer 1000 may operate in a networked environment using logical connections to remote computing devices and computer systems through a network 1020. The computer 1000 may connect to the network 1020 through a network interface unit 1006 connected to the bus 1004. It should be appreciated that the network interface unit 1006 may also be utilized to connect to other types of networks and remote computer systems. The computer 1000 may also include an input/output controller 1012 for receiving and processing input from a number of input devices 116, including a keyboard, a mouse, a touchpad, a touch screen, an electronic stylus, or other type of input device. Similarly, the input/output controller 1012 may provide output to a display device 114, such as a computer monitor, a flat-panel display, a digital projector, a printer, a plotter, or other type of output device.
As mentioned briefly above, a number of program modules and data files may be stored in the mass storage device 1010 and RAM 1014 of the computer 1000, including an operating system 1018 suitable for controlling the operation of a computer. The mass storage device 1010 and RAM 1014 may also store one or more program modules. In particular, the mass storage device 1010 and the RAM 1014 may store the data analysis program 104, which was described in detail above in regard to
Based on the foregoing, it should be appreciated that technologies for providing multi-condition filtering of an interactive summary table are provided herein. Although the subject matter presented herein has been described in language specific to computer structural features, methodological acts, and computer-readable media, it is to be understood that the invention defined in the appended claims is not necessarily limited to the specific features, acts, or media described herein. Rather, the specific features, acts, and mediums are disclosed as example forms of implementing the claims.
The subject matter described above is provided by way of illustration only and should not be construed as limiting. Various modifications and changes may be made to the subject matter described herein without following the example embodiments and applications illustrated and described, and without departing from the true spirit and scope of the present invention, which is set forth in the following claims.
