Framework for metadata-driven dynamic reporting systems and methods

• US 8,140,471 B2
• Filed: 05/15/2007
• Issued: 03/20/2012
• Est. Priority Date: 05/15/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Jiang, Yuan, Wieneke, Paul
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): MOSER, BRUCE M
• Application Number:
  US11/748,613
• Publication Number:
  US 20080288451A1
• Time in Patent Office: 1,771 Days
• Field of Search: 707/3, 707/102, 707/4, 707/104.1, 707/999.003, 707/999.004, 707/999.102, 707/999.104, 707/603, 707/103, 707/999.103, 707/999.107, 707/722, 707/769, 707/803, 707/804
• US Class Current: 707/603
• CPC Class Codes: G06F 16/2443 Stored procedures
• CPC Class Codes: G06Q 10/10 Office automation; Time management

Abstract

Embodiments of the invention provide systems and methods for dynamically reporting information stored in a repository. According to one embodiment, a method for dynamically reporting information stored in one or more data repositories can comprise retrieving report metadata from a report repository. The report metadata can define a format for the report and identify one or more queries for the report. Query metadata can be retrieved from a query repository. The query metadata can define one or more queries, for example, Structured Query Language (SQL) queries. Retrieving the query metadata can be based on the one or more queries identified in the report metadata. The one or more queries defined by the query metadata can be performed on the one or more data repositories and a results matrix can be populated with results of performing the one or more queries defined in the query metadata.

Claims

1. A method of dynamically reporting information stored in one or more data repositories, the method comprising:

receiving a request for a pre-defined report, the request identifying a name of the report requested;

retrieving report metadata from a report repository based on the name of the requested report, the report metadata identifying the name of the report, defining a format for the report including a column name for each of a plurality of columns in the report, identifying a query name for each of a plurality of pre-defined queries for the report, and defining a column sequence for each of the plurality columns in the report indicating an order for results of each of the plurality of queries;

retrieving query metadata from a query repository, the query metadata defining each of the plurality of queries, wherein retrieving the query metadata from the query repository is based on the query names identified in the report metadata;

performing each of the plurality of queries defined by the query metadata on the one or more data repositories; and

populating a results matrix with results of performing each of the plurality of queries defined in the query metadata based on the column sequence for the plurality of columns defined in the report metadata.

2. The method of claim 1, wherein the one or more queries comprise Structured Query Language (SQL) queries.

3. The method of claim 1, further comprising:

populating a plurality if columns in a table with the column names defined by the report metadata; and

populating the plurality of columns of the table with the results of performing each of the plurality of queries from the results matrix.

4. The method of claim 3, further comprising determining whether the results of performing each of the plurality of queries comprises less than a predetermined size of the table.

5. The method of claim 4, further comprising, in response to determining the results of performing each of the plurality of queries comprises less than the predetermined size of the table, hiding unused columns of the table.

6. The method of claim 3, further comprising outputting the table.

7. A system comprising:

a source data repository;

a report repository having stored therein report metadata, the report metadata defining a format for one or more reports and identifying each of a plurality of queries for each of the one or more reports;

a query repository having stored therein query metadata, the query metadata defining each of the plurality of queries; and

a dynamic data provider module communicatively coupled with the source data repository, the report repository, and the query repository, wherein the dynamic data provider receives a request for a pre-defined report, the request identifying a name of the report requested, retrieves report metadata for a requested report from the report repository based on the name of the requested report, retrieves query metadata from the query repository, wherein the report metadata comprises a query name for each of the plurality of queries, an identification of the name of the report, a column name for each of a plurality of columns in the report, and a column sequence for each of the plurality of columns in the report indicating an order for results of each of the plurality of queries, and wherein retrieving the query metadata from the query repository is based on the query names, performs each of the plurality of queries defined by the query metadata on the source data repository, and populates a results matrix with results of performing each of the plurality of queries defined in the query metadata based on the column sequence for the plurality of columns defined in the report metadata.

8. The system of claim 7, wherein the one or more queries comprise Structured Query Language (SQL) queries.

9. The system of claim 7, further comprising a dynamic report renderor and wherein the dynamic data provider provides the results matrix and the column names defined by the report metadata to a dynamic report renderor.

10. The system of claim 9, wherein the dynamic report renderor receives the results matrix and the column names defined by the report metadata from the dynamic data provider, populates a plurality of columns in a table with the column names defined by the report metadata, and populates the plurality of columns of the table with the results of performing the one or more queries from the results matrix.

11. The system of claim 10, wherein the dynamic report renderor determines whether the results of performing each of the plurality of queries comprises less than a predetermined size of the table.

12. The system of claim 11, wherein the dynamic report renderor hides unused columns of the table in response to determining the results of performing each of the plurality of queries comprises less than the predetermined size of the table.

13. The system of claim 10, wherein the dynamic report renderor outputs the table.

14. The system of claim 7, further comprising a report designer tool communicatively coupled with the report repository and providing a user interface and utilities for generating and maintaining report metadata stored in the report repository.

15. The system of claim 7, further comprising a query repository builder tool communicatively coupled with the query repository and providing a user interface and utilities for generating and maintaining query metadata stored in the query repository.

16. A machine-readable medium having stored thereon a series of instructions which, when executed by a processor, cause the processor to report information stored in one or more data repositories by:

receiving a request for a pre-defined report, the request identifying a name of the report requested;

retrieving report metadata from a report repository based on the name of the requested report, the report metadata identifying the name of the report, defining a format for the report including a column name for each of a plurality of columns in the report, identifying a query name for each of a plurality of pre-defined queries for the report, and defining a column sequence for each of the plurality columns in the report indicating an order for results of each of the plurality of queries;

retrieving query metadata from a query repository, the query metadata defining each of the plurality of queries, wherein retrieving the query metadata from the query repository is based on the query names identified in the report metadata;

performing each of the plurality of queries defined by the query metadata on the one or more data repositories; and

populating a results matrix with results of performing each of the plurality of queries defined in the query metadata based on the column sequence for the plurality of columns defined in the report metadata.

17. The machine-readable medium of claim 16, further comprising:

populating a plurality if columns in a table with the column names defined by the report metadata; and

populating the plurality of columns of the table with the results of performing each of the plurality of queries from the results matrix.

18. The machine-readable medium of claim 17, further comprising outputting the table.