DYNAMICALLY FILTERING AGGREGATE REPORTS BASED ON VALUES RESULTING FROM ONE OR MORE PREVIOUSLY APPLIED FILTERS

• US 20110167330A1
• Filed: 09/30/2010
• Published: 07/07/2011
• Est. Priority Date: 06/21/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Hayrapetyan, Tigran, Hawking, Robert, Zhong, Weide, Folting, Allan
• Granted Patent: US 9,195,728 B2
• Time in Patent Office: Days
• US Class Current: 715/219
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• 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

Abstract

Methods, systems, and computer program products dynamically filter an aggregate report based on values resulting from one or more filters previously applied to the aggregate report. A method involves maintaining a collection of filters to be applied in a designated evaluation order to the aggregate report, applying a first filter to the aggregate report, and evaluating values in the aggregate report to produce aggregate values resulting from applying the first filter. The method also involves applying a next filter to the aggregate report and evaluating the aggregate values in the aggregate report resulting from applying the first filter therein producing aggregate values resulting from applying the next filter to the aggregate report. Additionally, the method involves displaying the aggregate report wherein evaluating values resulting from previously applying any filter to the aggregate report involves evaluating visual values displayed in the aggregate report after each previous filter has been applied.

Claims

1. A computer-implemented method for dynamically filtering visible values in a spreadsheet, comprising:

maintaining a plurality of filters to be applied in a designated order to the spreadsheet, the spreadsheet comprising a plurality of cells;

applying, by the computer, a first filter to a first set of cell values in the spreadsheet, the first filter being applied to the first set of cell values at least in response to hiding cell values displayed within at least one of the plurality of cells in the spreadsheet;

generating a second set of cell values by evaluating visible values of a first set of cells;

applying a second filter to the second set of cell values; and

generating a third set of cell values by evaluating visible values of the second set of cell values.

2. The method of claim 1, further comprising:

determining whether there is a third filter from the plurality of filters yet to be applied;

in response to determining that there is a third filter yet to be applied, storing the third set of cell values as the second set of cell values; and

in response to determining that there is not a third filter yet to be applied, storing the third set of cell values as a final set of cell values.

3. The method of claim 1, further comprising displaying the visible values of the second set of cell values.

4. The method of claim 2, further comprising:

receiving a refresh instruction; and

in response to receiving the refresh instruction;

retrieving updated source data; and

reapplying the plurality of filters in the designated order based on the updated source data therein producing an updated final set of cell values.

5. The method of claim 4, wherein reapplying the plurality of filters comprises reapplying the plurality of filters in a single operation without displaying the second set of cell values.

6. The method of claim 4, wherein receiving the refresh instruction comprises receiving a refresh instruction in response to at least one of adding a cell to the spreadsheet or removing a cell from the spreadsheet, further comprising:

receiving notice that a cell has been removed from the spreadsheet;

in response to the cells being removed, removing any filters only associated with the cell from the spreadsheet.

7. The method of claim 1, wherein the designated order is based on an order in which each filter is added to the spreadsheet, further comprising:

adding a new filter to the plurality of filters; and

determining an evaluation order placement for the new filter.

8. The method of claim 7, further comprising:

receiving a change in order policy for the plurality of filters; and

in response to receiving the change in order policy, reordering the evaluation order placement for the plurality of filters wherein the plurality of filters having a designated order of evaluation is exposed by an application program interface.

9. The method of claim 1, further comprising applying multiple filters of the plurality of filters at the same time to different cells of the spreadsheet wherein the different cells reside in at least one of a row area of the spreadsheet, a column area of the spreadsheet, a hidden area of the spreadsheet, or a page filter area of the spreadsheet.

10. The method of claim 1, wherein applying the first filter to the first set of cell values and applying a second filter to the second set of cell values comprises applying filters to a relational spreadsheet or to an online analytical processing (OLAP) spreadsheet and wherein maintaining a plurality of filters comprises maintaining at least one of the following filter types:

a text filter operative to dynamically filter by captions;

a value filter operative to dynamically filter by a value of a data field;

a date filter operative to dynamically filter by a value of a date item; and

a top/bottom filter operative to dynamically filter by values of data fields.

11. One or more computer-readable media, wherein the one or more computer readable media do not consist of a propagated data signal, the one or more computer readable media having stored thereon computer executable instructions that, when executed by a processor, cause a computer to perform a method of dynamically filtering visible values in a spreadsheet, the method comprising:

maintaining a plurality of filters to be applied in a designated order to the spreadsheet, the spreadsheet comprising a plurality of cells;

applying a first filter to a first set of cell values in the spreadsheet, the first filter being applied to the first set of cell values at least in response to hiding cell values displayed within at least one of the plurality of cells in the spreadsheet;

generating a second set of cell values by evaluating visible values of a first set of cells;

applying a second filter to the second set of cell values;

generating a third set of cell values by evaluating visible values of the second set of cell values; and

displaying the visible values of the second set of cell values.

12. The one or more computer-readable media of claim 11, further comprising:

determining whether there is a third filter from the plurality of filters yet to be applied;

in response to determining that there is a third filter yet to be applied, storing the third set of cell values as the second set of cell values; and

in response to determining that there is not a third filter yet to be applied, storing the third set of cell values as a final set of cell values.

13. The one or more computer-readable media of claim 12, further comprising:

receiving a refresh instruction; and

in response to receiving the refresh instruction;

retrieving updated source data; and

reapplying the plurality of filters in the designated order based on the updated source data therein producing an updated final set of cell values.

14. The one or more computer-readable media of claim 13, wherein reapplying the plurality of filters comprises reapplying the plurality of filters in a single operation without displaying the second set of cell values.

15. A system for dynamically filtering visible values in a spreadsheet, comprising:

a memory for storing executable program code; and

a processor, functionally coupled to the memory, the processor being responsive to computer-executable instructions contained in the program code and operative to;

maintain a plurality of filters to be applied in a designated order to the spreadsheet, the spreadsheet comprising a plurality of cells;

apply a first filter to a first set of cell values in the spreadsheet, the first filter being applied to the first set of cell values at least in response to hiding cell values displayed within at least one of the plurality of cells in the spreadsheet;

generate a second set of cell values by evaluating visible values of a first set of cells;

apply a second filter to the second set of cell values;

generate a third set of cell values by evaluating visible values of the second set of cell values;

determine whether there is a third filter from the plurality of filters yet to be applied;

in response to determining that there is a third filter yet to be applied,

store the third set of cell values as the second set of cell values; and

in response to determining that there is not a third filter yet to be applied, storing the third set of cell values as a final set of cell values.

16. The system of claim 15, wherein the processor is further operative to display the visible values of the second set of cell values.

17. The system of claim 15, wherein the processor is further operative to:

receive a refresh instruction; and

in response to receiving the refresh instruction;

retrieve updated source data; and

reapply the plurality of filters in the designated order based on the updated source data therein producing an updated final set of cell values.