Data mining and reporting

• US 7,945,850 B2
• Filed: 02/27/2007
• Issued: 05/17/2011
• Est. Priority Date: 01/07/2000
• Status: Expired due to Fees

• Current Assignee: Mastermine Software Incorporated
• Original Assignee: Mastermine Software Incorporated
• Inventors: Machalek, Robert
• Primary Examiner(s): Hutton; Doug
• Assistant Examiner(s): Blackwell; James H
• Application Number:
  US11/679,647
• Publication Number:
  US 20070143661A1
• Time in Patent Office: 1,540 Days
• Field of Search: None
• US Class Current: 715/212
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06Q 10/10 Office automation; Time management

Abstract

An integrated system is described that allows a user to easily mine and report data maintained by a customer relationship management (CRM) application. In general, the system automates the process of creating electronic reports, such as an electronic worksheet, in a format that can be directly manipulated and viewed from spreadsheet application. In one embodiment, the report is automatically generated to include a pivot table for displaying the data. The system includes a database adapted to store the CRM data. First and second reporting modules execute within the CRM software application and the worksheet software application, respectively. The reporting modules cooperate in order to integrate CRM application and spreadsheet application, thereby automating the process of generating a worksheet based on the CRM database.

Claims

1. A method comprising:

executing a customer relationship management (CRM) software application on a computer, wherein the CRM software application includes customized settings and local field names, and further wherein the CRM software application includes a CRM database that stores CRM data;

receiving a report generation request within the CRM software application;

in response to the request, examining, with a reporting module installed within the CRM software application, a schema and data structures of the CRM database and customized settings including local field names within the CRM application, wherein the reporting module installed within the CRM software application includes one or more report toolkits;

displaying a list of report templates with the reporting module installed within the CRM software application that are pre-defined by the one or more report toolkits of the reporting module installed within the CRM software application;

receiving a selection of one of the report templates with the reporting module installed within the CRM software application;

presenting a set of user-selectable database fields with the reporting module installed within the CRM software application as a function of the selected report template;

receiving, from the user with the reporting module installed within the CRM software application, a selection of one or more of the user-selectable database fields;

generating, with the reporting module installed within the CRM software application, a database query as a function of the user-selectable database fields that were selected by the user;

invoking a spreadsheet application from the reporting module installed within the CRM software application using an application programming interface (API) of the spreadsheet application to automatically generate an electronic worksheet viewable by the spreadsheet software application, wherein the automatically generating the electronic worksheet comprises directing the spreadsheet application with the reporting module installed within the CRM software application to create a new workbook having the electronic worksheet;

further invoking the spreadsheet application from the reporting module installed within the CRM software application using the API to automatically generate a pivot table within the electronic worksheet according to the database query, wherein the pivot table contains the CRM data from the CRM database, and wherein invoking the spreadsheet application includes communicating report parameters from the reporting module installed within the CRM software application to the spreadsheet software application based on the schema and data structures of the CRM database and the customized settings including the local field names within the CRM software application;

presenting the pivot table to a user with the spreadsheet application in accordance with the report parameters received from the reporting module installed within the CRM software application;

receiving within the spreadsheet application a selection from the user of the selected CRM data contained within the pivot table; and

in response to the selection, automatically creating a second worksheet that displays the CRM data as a number of rows in columnar format according to fields within the CRM database,

wherein each row represents a single CRM record stored within the CRM database.

2. The method of claim 1, further comprising:

receiving within the spreadsheet application a selection of one or more of the rows of the second worksheet from the user; and

in response to the selection, automatically invoking the CRM software application from the spreadsheet application to display CRM records of the CRM database that correspond to the selected one or more rows.

3. The method of claim 1, wherein generating the electronic worksheet comprises executing one or more macros within the spreadsheet software application to format the pivot table.

4. The method of claim 3, wherein executing the one or more macros includes formatting the electronic worksheet according to a report template.

5. The method of claim 1 further comprising:

receiving, with a reporting module installed within the spreadsheet software application, user input specifying one or more records to search for among the CRM data contained within of the pivot table; and

creating, with the reporting module installed within the spreadsheet software application, a group within the CRM database of the one or more records within the pivot table that are specified by the user input.

6. The method of claim 1, further comprising:

receiving within the spreadsheet application and by interaction of the user with the pivot table, a request to create a group within the CRM software application; and

in response to the user request, automatically invoking functions within the CRM software application from the worksheet software application to create the group consisting of CRM records within the CRM database.

7. The method of claim 6 further comprising:

receiving group parameters within the spreadsheet software application; and

communicating the group parameters to the CRM application.

8. A system comprising:

a database adapted to store customer relationship management (CRM) records containing CRM data;

a programmable processor adapted to execute a CRM software application and a spreadsheet software application;

a reporting module installed within the CRM software application, wherein the CRM software application includes a plurality of report toolkits, each report toolkit defining one or more report templates;

wherein the reporting module installed within the CRM software application is adapted to, in response to a report generation request, examine a schema and data structures of the CRM database and examine customized settings including local field names within the CRM application,

wherein the reporting module installed within the CRM software application includes a user interface to receive a selection of one of the plurality of report toolkits,

wherein the reporting module installed within the CRM software application displays a list of the report templates based on the selected report toolkit and receives a selection of one of the one or more report templates,

wherein the reporting module installed within the CRM software application presents a set of user-selectable database fields as a function of the selected report template, receives from the user a selection of one or more of the user-selectable database fields, and generates a database query as a function of the user selected database fields,

wherein the reporting module invokes the spreadsheet software application using an application programming interface (API) of the spreadsheet application to automatically create a new workbook, generate within the workbook an electronic worksheet viewable within the spreadsheet software application and automatically generate a pivot table within the electronic worksheet based on the CRM data by communicating the database query from the CRM software application to the spreadsheet software application and issuing the query from the spreadsheet software application to the CRM database to retrieve the CRM records and automatically create the pivot table,

wherein, when invoking the spreadsheet software application, the reporting module is adapted to communicate report parameters from the CRM software application to the spreadsheet software application based on the schema and data structures of the CRM database and the customized settings including the local field names within the CRM application,

wherein, in response, the spreadsheet software application generates the pivot table within the electronic worksheet to present the CRM data in accordance with the report parameters.

9. The system of claim 8, further comprising:

wherein the spreadsheet application receives from the user a selection of one or more cells of the pivot table,

wherein, in response to the user selection of the one or more cells within the pivot table, the spreadsheet software application automatically communicates with the CRM software application to access CRM records within the CRM database that are associated with the selected cells of the pivot table and creates a second worksheet that displays the CRM records as a number of rows according to fields within the CRM database,

wherein the spreadsheet application receives from the user a selection of one or more rows of the second worksheet, and

wherein, in response, the spreadsheet software application automatically invokes the CRM software application to display CRM records of the CRM database that correspond to the selected one or more rows.

10. The system of claim 8, wherein the spreadsheet software application is adapted to receive user input specifying one or more elements to search for among the data of the pivot table and to direct the spreadsheet software application to create a group within the pivot table according to the user input.

11. The system of claim 8, wherein the generated electronic spreadsheet includes formatting information to format the electronic worksheet according to the selected report template.

12. A non-transitory computer-readable storage article having computer-executable instructions stored thereon comprising a first reporting module installed within a customer relationship management (CRM) software application, and a second reporting module installed within a spreadsheet software application wherein the computer-executable instructions cause a programmable processor to:

receive a report generation request within the customer relationship management (CRM) software application having a CRM database that stores CRM data;

prior to automatically generating the pivot table within the electronic worksheet, receive a selection of one of a plurality of report toolkits with the reporting module installed with the CRM software application;

display a list of report templates with the reporting module installed within the CRM software application based on the selected report toolkit;

receive a selection of one of the report templates with the reporting module installed within the CRM software application;

examine, with the reporting module installed within the CRM software application, customized settings including local field names within the CRM application;

in response to the request, invoke the reporting module of the spreadsheet application from the reporting module of the CRM software application using an application programming interface (API) of the spreadsheet application to automatically generate an electronic worksheet viewable by the spreadsheet software application by directing the spreadsheet application with the reporting module installed within the CRM software application via the API of the spreadsheet application to create a new workbook and copy the selected report template into the workbook as the electronic worksheet;

invoke the reporting module of the spreadsheet application from the reporting module of the CRM software application using the API to automatically generate a pivot table within the electronic worksheet, including communicating report parameters from the reporting module installed within the CRM software application to the spreadsheet software application based on the customized settings including the local field names within the CRM software application,

wherein the pivot table contains CRM data from the CRM database and presents the CRM data in accordance with the report parameters;

present the pivot table to a user with the reporting module of the spreadsheet application;

receive with the reporting module of the spreadsheet application from the user a user selection of the CRM data contained within the pivot table; and

in response to the selection, automatically create a second worksheet that displays the CRM data as a number of rows in columnar format according to fields within the CRM database, wherein each row represents a single CRM record stored within the CRM database.

13. The article of claim 12, further comprising executable instructions to cause the reporting module of the spreadsheet application to receive a selection of one or more of the rows of the second worksheet from the user and, in response to the selection, to automatically invoke the CRM software application from the spreadsheet application to display the corresponding CRM records of the selected one or more rows.