Method and system for building a report for execution against a data store

• US 7,814,093 B2
• Filed: 07/25/2003
• Issued: 10/12/2010
• Est. Priority Date: 07/25/2003
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Meyers, Robert Alan, Meyers, Eric Stephen, Meyers, Aaron Stephen
• Primary Examiner(s): Pham; Hung Q
• Application Number:
  US10/627,180
• Publication Number:
  US 20050039033A1
• Time in Patent Office: 2,636 Days
• Field of Search: 713/168, 713/185, 713/189
• US Class Current: 707/714
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

A data query and reporting method and system are provided. The method and system allow non-technical computer users to build and execute complex database queries. The method and system accommodate the need for a simple, easy-to-understand interactive and iterative means for generating and validating database queries. The method and system reduce the complexity and costs associated with retrieving useful information for organizations of all sizes, especially smaller businesses. The method and system support a robust security model, enabling organizations to protect sensitive data while providing broad data access.

Claims

1. A method for building a report, comprising the steps of:

defining a relational abstraction of a data store, the defined relational abstraction including a plurality of views, fields associated with the plurality of views, and relations between the plurality of views;

selecting one of the plurality of views as a base view of a report; and

creating at least one report field using the views, fields and relations associated with the base view, wherein the at least one report field is created by;

a) specifying a relation path from the base view to a predetermined destination view, the relation path including one or more relations;

b) if the specified relation path contains any to-many relations, creating an aggregate field by;

i) creating at least one nested field to be aggregated, where a relation path of the created at least one nested field begins at a destination view of the created aggregate field; and

ii) specifying an expression for aggregating values associated with the created at least one nested field; and

c) if the specified relation path does not contain any to-many relations, creating a scalar field, by;

i) referencing a source field from the destination view;

or

ii) creating an expression field that includes at least one nested field, where a relation path of the at least one nested field begins at a destination view of the created expression field.

2. The method of claim 1 wherein information returned in a report is limited by specifying a filter that limits any rows returned in the report.

3. The method of claim 2 wherein a filter is specified that limits any rows returned in the report by:

a) creating a plurality of fields for use as filter fields; and

b) specifying at least one logical filter operator and at least one corresponding filter value on each filter field.

4. The method of claim 3 wherein filter fields are organized into nested filter groups, filters are specified for each of the nested filter groups and an output of applying the filters of the nested filter groups are used to combine results of the report.

5. The method of claim 2 wherein results of the report are sorted by at least one field.

6. The method of claim 2 wherein results of the report are grouped by at least one field.

7. The method of claim 1 wherein information returned in a report containing the created at least one aggregate field is limited by specifying a filter on the destination view associated with the created at least one aggregate field.

8. The method of claim 1 wherein a filter is specified that limits any rows returned in the report by:

a) creating a plurality of fields for use as filter fields; and

b) specifying at least one logical filter operator and at least one corresponding filter value on each filter field.

9. The method of claim 8 wherein filter fields are organized into nested filter groups, filters are specified for each of the nested filter groups and an output of applying the filters of the nested filter groups are used to combine results of the report.

10. The method of claim 1 wherein results of the report are sorted by at least one field.

11. The method of claim 1 wherein results of the report are grouped by at least one field.

12. The method of claim 1 wherein a total aggregate field is created that provides a total of the created aggregate field based upon relations contained in the relation path for the created aggregate field.

13. The method of claim 1 wherein an SQL query that includes report fields is generated, by:

a) generating an initial SELECT statement with a FROM clause that references the base view of the report;

b) adding each report field having an empty relation path to a first subsequent SELECT statement; and

c) adding each report field having a non-empty relation path to a second subsequent SELECT statement, by;

i) adding a JOIN clause from the base view of the report to an initial nested SELECT statement and a FROM clause referencing a destination view associated with a first relation in the non-empty relation path; and

ii) adding a subsequent nested SELECT statement inside the initial nested SELECT statement for each successive relation in the non-empty relation path.

14. The method of claim 13 wherein a report filter is added to the SQL query by adding a WHERE clause to an outer SELECT statement.

15. The method of claim 13 wherein a filter specified on a to-many relationship in a relation path of an aggregate field is added to the SQL query by adding a WHERE clause to a corresponding nested SELECT statement.

16. The method of claim 13 wherein the SQL query is optimized by consolidating nested SELECT statements associated with report fields where relation paths are equal to or are a prefix of each other.

17. The method of claim 1 wherein a reusable field definition is created that includes any filters specified on to-many relationships in a relation path associated with a field.

18. The method of claim 1 wherein a reusable filter definition is created that includes filter groups and fields associated with the report and operators and values that correspond to the filter groups and fields.

19. The method of claim 1 further comprising the steps of:

a) defining a security principal;

b) creating an access control entry for at least one element of information in the data store, which access control entry grants or denies access to the security principal;

c) defining a security filter on a view, by;

i) creating a filter that identifies at least one row in the view; and

ii) creating an access control entry denying the security principal access to any information to be excluded by the filter; and

d) enforcing the access controls on a report, by;

i) authenticating the security principal; and

ii) applying the security filter to the report.

20. The method of claim 1 further comprising the steps of:

a) creating a reusable list of keys or row identifiers by specifying a filter on a view and selecting at least one of resulting keys or rows; and

b) using the list as a set of filter values in conjunction with an IN or NOT IN filter operator.

21. The method of claim 1 wherein the report field includes two or more non-consecutive references to a particular view.

22. A computer-readable storage medium storing instructions executable by a processor to perform a method according to claim 2.