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

• US 20050039033A1
• Filed: 07/25/2003
• Published: 02/17/2005
• Est. Priority Date: 07/25/2003
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: ActiveViews, Inc. (Microsoft Corporation)
• Inventors: Meyers, Robert Alan, Meyers, Aaron Stephen, Meyers, Eric Stephen
• Granted Patent: US 7,814,093 B2
• Time in Patent Office: Days
• US Class Current: 713/193
• 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:

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

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

c) creating at least one report field using the views, fields and relations associated with the base view.

2. The method of claim 1 wherein the report field is created by:

a) specifying a relation path from the base view to a predetermined destination view, the relation path including zero 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 the relation path of the nested field begins at the destination view of the aggregate field; and

ii) specifying an expression for aggregating the values associated with the 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 the relation path of the nested field begins at the destination view of the expression field.

3. 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.

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

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

6. The method of claim 3 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.

7. The method of claim 4 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.

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

9. The method of claim 7 wherein a plurality of filter fields are organized into nested filter groups, filters are specified for each of the nested filter groups and the output of applying the filters of the nested filter groups are used to combine the 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 2 wherein results of the report are sorted by at least one field.

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

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

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

15. The method of claim 2 wherein an SQL query that includes the 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 the destination view associated with a first relation in the relation path; and

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

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

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

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

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

20. The method of claim 2 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.

21. 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 the security principal access to the element;

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.

22. The method of claim 2 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.

23. 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 the 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.

24. The method of claim 2 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 the 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.

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

26. A computer system for building a report, the system comprising:

a) means for defining a relational abstraction of a data store, the definition including a plurality of views, fields associated with the views, and any relations between the views;

b) means for selecting one view as a base view of the report; and

c) means for creating at least one report field using the views, fields and relations associated with the base view.

27. The system of claim 26 further comprising a means to create the report field by providing means for:

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

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

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

ii) specifying an expression for aggregating the values associated with the nested field; and

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

i) referencing a source field from the destination view, or

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