Database report generation

• US 7,293,008 B2
• Filed: 04/29/2004
• Issued: 11/06/2007
• Est. Priority Date: 04/29/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: Cognos Incorporated (International Business Machines Corporation)
• Inventors: Cazemier, Henk, Potter, Charles M., Seeds, Glen M.
• Primary Examiner(s): Lee; Wilson
• Application Number:
  US10/835,244
• Publication Number:
  US 20050021516A1
• Time in Patent Office: 1,286 Days
• Field of Search: 707 1- 10, 707/100
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2457 with adaptation to user needs
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 9/454 Multi-language systems; Localisation; Internationalisation
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

The invention provides for a method for producing reports for business intelligence that adapt automatically to a context, defined by a locale variable. A parameter map is defined, based on locale key and field names, and a report layout is produced that contains one or more macrotized database language expressions that include partial expressions built using the parameter map. Later, once a user selects a locale, the partial expression resulting from a lookup in the parameter map using the locale is used to modify one or more macrotized database language expressions. Finally, the resulting valid database language expressions are applied to the database to produce a report.

Claims

1. A method of producing a business report from data contained in a computer based business intelligence system having a report authoring tool, the method comprising the steps of:

producing a report layout containing one or more macrotized database language expressions;

defining a parameter map having one or more keys and one or more field names, the one or more keys being associated with the one or more field names;

accepting from a user one or more user inputs;

modifying the one or more macrotized database language expressions, including;

producing, from the one or more macrotized database language expressions in dependence upon the one or more user inputs and the parameter maps, one or more valid database language expressions for a database query, the one or more valid database language expressions containing at least one of the one or more field names; and

applying the one or more valid database language expressions to one or more databases, including;

retrieving the data from the one or more databases based on the one or more valid database language expressions to produce the business report.

2. The method of claim 1 wherein at least one of the one or more keys is a locale.

3. The method of claim 2 wherein the step of producing one or more valid database language expressions for a database query includes:

selecting the locale based on the user input; and

combining the locale with the parameter map to further modify the one or more macrotized database language expressions.

4. The method of claim 1 wherein the one or more valid database language expressions form Structured Query Language.

5. The method of claim 3 wherein the step of selecting the locale includes:

providing a default locale.

6. The method of claim 1 wherein the step of producing one or more valid database language expressions for a database query includes:

substituting a partial expression delineated by one or more predetermined characters within the macrotized database language expressions.

7. The method of claim 1 wherein the step of producing one or more valid database language expressions for a database query includes:

replacing a partial expression delineated by one or more predetermined characters with a partial expression built by accessing the parameter map with a lookup function.

8. The method of claim 7 wherein the key is locale, and wherein the step of accessing the parameter map includes:

indexing with the locale.

9. The method of claim 8 wherein the step of indexing with the locale includes:

selecting at least one of the one or more field names appropriate to the locale.

10. The method of claim 8 wherein the step of indexing with the locale includes:

selectiug at least one of the one or more field names in the language of the locale.

11. The method of claim 1 wherein the one or more databases comprises a data warehouse.

24. The method of claim 1 wherein at least one of the one or more keys is a mode variable having values selected from the group consisting of test and production.

12. A computer based system for the production of a business report from data contained in a computer based business intelligence system having a report authoring tool, including:

means for producing a report layout containing one or more macrotized database language expressions;

means for defining a parameter map having one or more keys and one or more field names, the one or more keys being associated with the one or more field names;

means for accepting from a user one or more user inputs;

means for modifying the one or more macrotized database language expressions, including;

means for producing, from the one or more macrotized database language expressions in dependence upon the one or more user inputs and the parameter map, one or more valid database language expressions for a database query, the one or more valid database language expressions containing at least one of the one or more field names; and

means for applying the one or more valid database language expressions to one or more databases, including;

means for retrieving the data from the one or more databases based on the one or more valid database language expressions to produce the business report.

13. The system of claim 12 wherein at least one of the one or more keys is a locale.

14. The system of claim 13 wherein the means for producing one or more valid database language expressions for a database query includes:

means for selecting the locale based on the user input; and

means for combining the locale with the parameter map to further modify the one or more macrotized database language expressions.

15. The system of claim 12 wherein the one or more valid database language expressions form Structured Query Language.

16. The system of claim 14 wherein the means for selecting the locale includes:

providing a default locale.

17. The system of claim 12 wherein the means for producing one or more valid database language expressions for a database query includes:

means for substituting a partial expression delineated by one or more predetermined characters within the macrotized database language expressions.

18. The system of claim 12 wherein the means for producing one or more valid database language expressions for a database query includes:

means for replacing a partial expression delineated by one or more predetermined characters with a partial expression built by accessing the parameter map with a lookup function.

19. The system of claim 18 wherein the key is locale, and wherein the means for accessing the parameter map includes:

means for indexing the locale.

20. The system of claim 19 wherein the means for indexing with the locale includes:

means for selecting at least one of the one or more field names appropriate to the locale.

21. The system of claim 19 wherein the means for indexing with the locale includes:

means for selecting at least one of the one or more field names in the language of the locale.

22. The system of claim 12 wherein the one or more databases comprises a data warehouse.

23. A computer-readable medium having computer-executable instructions thereon for performing a method for producing a business report from data contained in one or more database, comprising;

code for producing a report layout containing one or more macrotized database language expressions;

code for defining a parameter map having one or more keys and one or more filed names, the one or more keys being associated with the one of the one or more field names;

code for accepting from a user one or more user inputs;

code for modifying the one or more macrotized database language expressions, including;

producing, from the one or more macrotized database language expressions in dependence upon the one or more user inputs and the parameter map, one or more valid database language expressions for a database query, the one or more valid database language expressions containing at least one of the one of the one or more field names; and

code for applying the one or more valid database language expressions to one or more databases, including;

code for retrieving the data from the one or more databases based on the one or more valid database language expressions to produce the business report.

25. A method of producing a business report from data contained in a computer based business intelligence system, the method comprising the steps of:

producing a report layout containing one or more macrotized database language expressions;

defining a parameter map having at least one key and at least one field name associated with the at least one key;

accepting from a user one or more user inputs;

modifying the one or more macrotized database language expressions in dependence upon the one or more user inputs and the parameter map, including

accessing the parameter map based on the user input, and

replacing a partial expression delineated by one or more predetermined characters with a partial expression based on the result of the access to the parameter map to produce one or more valid database language expressions for a database query;

applying the one or more valid database language expressions to one or more databases to produce the business report.

26. The method of claim 25 wherein the one or more valid database language expressions are defined by database query language.

27. The method of claim 25 wherein the step of accessing comprises:

accessing the parameter map based on the user input with a lookup function.

28. The method of claim 25 wherein at least one of the one or more keys is a locale, the step of accessing comprising:

selecting the locale based on the user input.

29. The method of claim 25 wherein at least one of the one or more keys is a locale, the step of accessing comprising:

indexing with the locale.

30. The method of claim 29 wherein the parameter map comprises one or more field names associated with the one or more keys, and wherein the step of accessing comprises:

selecting a field name appropriate to the locale from the parameter map.

31. The method of claim 29 wherein the parameter map comprises one or more field names associated with the one or more keys, and wherein the step of accessing comprises:

selecting a field name in the language of the locale from the parameter map.

32. The method of claim 26 wherein the one or more valid database expressions form Structured Query Language.

33. The method of claim 28 wherein the step of selecting the locale comprises:

providing a default locale.