Enterprise resource planning (ERP) systems integrate management information from the multiple different portions of an organization. A very common requirement for a user of an ERP system is the need to create reports over data from the ERP system. The ERP user is typically a functional expert who understands the data that he or she wants to report on. Reports are often highly personalized where the user needs to be able to choose the data fields and the selection criteria for the data to be included in the report, the types of calculation to be carried out by the report, as well as the content and layout (for example, charts, graphs or tables) of the report. Because they are so highly personalized, these types of specific reports are usually not included in the standard ERP product.
There are many challenges that make it difficult for end users to create reports in an ERP system. The ERP's interactive application provides the interface that users are most familiar with as they work within these applications as part of their day to day tasks. These applications allow users to easily query filtered data, but their reporting capabilities are very limited as compared to traditional reporting tools (and are normally limited to a data table).
In contrast, traditional reporting tools have sophisticated design features for layout and many choices for output format that users desire, but are outside of the interactive applications that the users normally work with. Because they typically do not interact with the ERP application and so must obtain data directly from the database or database middleware, these report creation tools require deep knowledge of the database schema and table relationships which make it very difficult for an end user to successfully use. In addition, the reporting tool often does not have access to any of the logic, transformations, and aggregations that may already have been performed on the data by the ERP application, and so it may be necessary to also duplicate the logic of the ERP application, creating unnecessary complexity when trying to generate end user reports for ERP systems.
Therefore, while the ERP user understands what it is they want to create in a report, they do not normally have the computer/application/software development skills necessary to create a report using traditional reporting solutions. The reports must normally be developed by specialized personal in an IT department or by external vendors. This means that it can become very costly to develop these types of reports with either internal IT departments or outside vendors.
As is evident, conventional systems do not offer effective and efficient solutions to enable end users to create these on-demand, highly personalized reports. ERP interactive applications are easy to use, but lack the reporting capabilities necessary. Traditional reporting tools offer sophisticated reporting capabilities but are too difficult for end users.
Therefore, there is a need for an improved approach for implementing reporting for interactive applications which addresses the problems of the prior approaches.
Some embodiments of the invention address the above problems using a method and apparatus that efficiently provides for end user reporting from interactive applications. One embodiment provides an approach of implementing dynamic field selection at an ERP application, where in user may make selections specifying a plurality of report parameters. Using an ERP query tool, queries are automatically generated based on the user's inputs to from a report data model. The report data model can then be loaded into a reporting tool and used to design a report definition. The report definition can then be used to render the end user reports.
Further details of aspects, objects, and advantages of the invention are described below in the detailed description, drawings, and claims. Both the foregoing general description and the following detailed description are exemplary and explanatory, and are not intended to be limiting as to the scope of the invention.
Various embodiments are described hereinafter with reference to the figures. It should be noted that the figures are not drawn to scale and that the elements of similar structures or functions are represented by like reference numerals throughout the figures. It should be noted that the figures are only intended to facilitate the description of the embodiments. They are not intended as an exhaustive description of the invention or as a limitation on the scope of the invention. In addition, an illustrated embodiment need not have all the aspects or advantages shown. An aspect or an advantage described in conjunction with a particular embodiment is not necessarily limited to that embodiment and can be practiced in any other embodiments even if not so illustrated. Also, reference throughout this specification to “some embodiments” or “other embodiments” means that a particular feature, structure, material, or characteristic described in connection with the embodiments is included in at least one embodiment. Thus, the appearances of the phrase “in some embodiment” or “in other embodiments” in various places throughout this specification are not necessarily referring to the same embodiment or embodiments. In addition, for the purposes of illustration and explanation, the present disclosure is described in various embodiments in the context of ERP applications. It is noted, however, that the invention is not limited in its scope to ERP applications, and indeed, may be applied to other types of applications as well.
Some embodiments of the invention address the above problems by providing an approach for implementing end user reporting from interactive applications. One embodiment provides an approach for implementing dynamic field selection in the end user reports.
The present embodiment provides for an interactive application that allows users to query data with query capabilities. The users are able to select report parameters such as data fields to be included in the reports intuitively and directly from the interactive application screen. A metadata mechanism is employed to store the data fields (data model) of the reports. An interactive report design tool that allows user to design the report with a variety of charts, graphics and tables. An engine queries the data based on the user defined query criteria, packages the user selected fields within the data and generate the report output on the fly.
There are generally four steps that are needed to create any report. First is the definition of the data fields that will be included. It is critical that the correct data tables and fields are chosen to meet the desired report requirements. Next is the selection of the data to be included in the report. Data selection allows users to filter data so their report contains only those rows that are desired. Third is the definition of the report layout which defines visualization aspects of the report, e.g., selecting whether the data will be shown as a pie chart, vertical bar chart, pivot or data table, and whether there will be subtotaling, bold, italics, colors, titles, etc. The last step is to view the report output.
As noted above, the problem is that typical users of ERP systems (such as AR clerks) do not normally understand the intricacies of the ERP data model. This creates significant problems if the ERP user needs to create a new report.
The present embodiment provides an approach that allows the user to create a report by using an interface that he or she is most familiar with, that they work with as part of their day to day tasks. The present reporting solution merges the interactive applications that users are so familiar with along with a traditional reporting tool for robust report layout and output capabilities. The solution leverages the interactive ERP applications ease of use and familiarity for the selection of data fields to be included in the report and for the dynamic creation of queries to find the right data to report on. For a sophisticated report layout design and output tool where users can easily design the report with a variety of charts, tables and other graphics, one can utilize a reporting tool, such as the Oracle Business Intelligence (BI) Publisher tool. The interactive application and the reporting tool are tightly integrated for a seamless reporting solution that enables end users to easily create on-demand, highly personalized reports.
The ERP user at the user station 101 operates the system to generate the new report. The user station 101 comprises any type of computing station that may be used to operate or interface with the application server. Examples of such user stations include for example, workstations, personal computers, laptop computers, or remote computing terminals. The user station 101 comprises a display device, such as a display monitor or screen, for displaying interface elements and report data to the user. The user station 101 may also comprise one or more input devices for the user to provide operational control over the activities of the system, such as a mouse, touch screen, keypad, or keyboard. The users of the user station 101 correspond to any individual, organization, or other entity that uses system to access applications on application server 102, such as the ERP interactive application 104 on the application server 105.
The database 103 corresponds to any type of computer readable mediums or storage devices. The computer readable storage devices comprise any combination of hardware and software that allows for ready access to the data within the database. For example, the computer readable storage device could be implemented as computer memory or disk drives operatively managed by an operating system.
An ERP interactive application 203 retrieves data from database 103, and may process and performs conversions, aggregations, and other logic on the retrieved data. An example of a suitable ERP interactive application is the JD Edwards EnterpriseOne application, available from Oracle Corporation of Redwood Shores, Calif. Users of ERP systems will be familiar retrieving and processing data with the ERP interactive application, which will generally provide a user interface that does not require the user to have a deep understanding of the schema of the underlying database 103.
The ERP interactive application 203 in accordance with embodiments of the invention also allows the user to specify data fields for which to create a report. In some embodiments, the ERP interactive application 203 may display queried data to the user, allowing the user to select which data fields are to be included in the report. Methods for selecting data fields for the report may include clicking on a button next to the desired data fields, or dragging and dropping the displayed data fields. Once the user has finished selecting the desired data fields for a report, the selections may be saved as a report data model. In some embodiments, the report data model may be saved as an XML file capable of being loaded by reporting tool 105.
Generating a report requires both the actual raw data for the report, and instructions on how the data will be formatted and displayed. ERP query tool 208 and ERP data 209 are used to handle the actual data for the report, while reporting tool 105, comprising report data model 204 and report layout tool 205, is used to handle the instructions for formatting and displaying the data.
ERP query tool 208 is an interface where a user can define queries to retrieve the raw data that they wish to generate a report on. The ERP query tool 208 can automatically create queries in response to user selections without the user needing to have a deep understanding of the underlying database schema. In some embodiments, the ERP query tool may display data to the user, allowing the user to select one or more data fields and then define query criteria based on the selected data fields. Methods for defining query criteria may include clicking on a button next to a desired data field to select the data field, and then defining one or more query criteria by selecting the criteria at one or more menus, without the need for the user to write any database queries. Instead, the necessary queries are generated automatically by the ERP query tool based on the user's selections and inputs.
Once the ERP query tool 208 has automatically generated queries based on the user's selections and inputs, the generated queries may be run on the database 103 in order to produce the ERP data 209. The ERP data 209 comprises the actual raw data that will be used in the end user report.
While the ERP application 104 is responsible for the actual data to be used in the report, the reporting tool 105 is responsible for how the data is to be formatted and displayed in the report. The reporting tool 105 comprises the report data model 204 and report layout tool 205. Report data model 204 comprises the data fields that the user wishes to create a report on. This information is received from the ERP application by loading a saved report data model created by the user at the ERP interactive application 203. The received information may be converted to form a representation of the data fields that usable by the report layout tool 205 for defining a report layout. For example, data fields extracted from the loaded report data model may be displayed as a list to the user that can be used with report layout tool 205.
The report layout tool 205 is an interface in the reporting tool that allows the user to create a layout for the report using the data in the report data model 204. The layout of the report comprises instructions specifying how the data of the report is to be displayed, and may include instructions for creating a plurality of data visualization elements, including tables, charts, and graphs based upon the data fields in the report data model 204. For example, the user may specify instructions to create a bar graph with a horizontal axis corresponding to a particular data field from the report data model 204. Once the user is finished defining a layout, it may be saved as a report definition.
The report render engine 206 is a tool that generates the actual end user report that is displayed to the user, and is invoked when the user desires to run a report. In order to run a report, it necessary to have both the raw data that is the subject of the report, as well as the instructions for how that data is to be displayed in the report. The report render engine 206 takes both the ERP data 209, comprising the data to be used in the report, and the report definition generated by the user using the report layout tool 205, comprising the instructions on how the data is to be displayed, and uses them together to generate the end user report 207, which is the finished report that is displayed to the user.
To perform query setup, ERP query tool 208 is used to present an interface to a user for selecting of query parameters at 210. This would allow users of the ERP application to define queries using the ERP graphical interface with which they are familiar with, without having to have detailed knowledge of the structure of the underlying database, or the technical knowledge of manually writing database queries. Next, at 211, the ERP query tool 208 receives the query parameters inputted by the user. For example, the ERP query tool 208 may display data fields to the user, wherein the user may select one or more data fields and define query criteria based on the selected data fields. The ERP query tool 208 then automatically generates database queries based upon the user inputs at 212. These generated queries may then be run on the database to return the raw data for the end user report. The query setup process is described in more detail below and in
To perform reporting setup, the ERP interactive application 203 first presents an interface to the user for selection of data fields at 213. As with the query setup described above, the interface allows the user to specify data fields to be included in the report using the ERP graphical interface with which they are familiar with. At 214, the ERP interactive application receives user inputs through the ERP interface. Using the inputs received from the user, the ERP interactive application generates a data model specifying the data fields to be used in the report at 215. The data model generated by the ERP interactive application 203 is then loaded into the reporting tool 105 to form the report data model 204. By allowing reporting tool 105 to obtain the data fields for the report from the ERP application instead of directly from the database 102, a deep understanding of the underlying database structure and schema by the user is not required. Instead, only a familiarity with the graphical interface of the ERP application is needed.
At the reporting tool 105, the data fields that comprise the report data model 204 may be displayed to the user. This allows the user to use the selected data fields with the report layout tool 205 to define a layout for the report at 216. The user may use report layout tool 205 to create various visual elements, such as charts, tables, and graphs, for which to represent the data fields contained in report data model 204. Once finished, the report layout is saved as a report definition. The reporting setup is described in greater detail below and in
Once both the query setup and the reporting setup have been completed, an end user report can be generated. To do so, the user selects a query at 217, and then selects a report definition at 218. At 219, the user-selected query is used to query the database to produce ERP data 209. There may be multiple combinations of queries and report definitions available. This data comprises the raw data for the report, which is then used by the report render engine 206 with the user-selected report definition, comprising the instructions for the layout of the report, to generate the end user report 207. Executing a report is described in greater detail below and in
In
Next, data from the database is retrieved by the ERP application at and displayed to the user at 402. This may be data that the user was viewing when initializing the creation of a new report. In some embodiments, a user may request certain data to be displayed after initializing the creation of a new report. Data fields may be presented to the user to allow selection for the new report at 403. These data fields are extracted from the metadata established for the data/system. Interface elements are provided to display the data fields and for allowing selection of desired fields. For example, the system may display a “+” signs on data fields to allow them to be selected for a report, where the user clicks on a “+” sign to include the column in the report. The user can repeat this action until all desired data fields have been selected.
At 404, once the user has specified the data fields to be included in the report, the user may optionally specify additional configurations. Additional interface elements may be present allowing a user to specify these additional configurations, which may include the maximum number of rows in a data field that will be used to generate the report, or selecting the naming conventions for the data fields to be used in the report. For example, a user may wish to, due to performance issues, limit the number of rows that are used to generate the report, even though a data query may return a larger number of rows.
At 405, the user-selected data fields and configurations may then be saved (e.g., initiated by the User clicking on a “Save” button). The system saves the user selected data and data fields into a report data model which is stored in the database. In some embodiments, the report data model may be stored as an XML document.
Sidebar 410 also contains options for a user to specify additional configurations, including the number of rows the user wishes to be reported on, and the naming convention for data fields to be used in the report. Once the user has finalized the data fields to be included in the report data model, the user can then click the save button 411 to save the report data model to be stored in the database.
At 304, after the saving of the report data model, the system invokes a reporting tool to design the actual report definition. Any suitable reporting tool may be employed, such as the BI Publisher tool available from Oracle Corporation. In some embodiments, a reporting tool may be launched automatically by the ERP application when a user saves a report data model. In other embodiments, the user may choose to manually launch the reporting tool by clicking a button in the ERP application after saving a report data model, or launch the reporting tool independently.
The saved report data model is then loaded into the reporting tool at 305. The user can then use the reporting tool to design the report at 306, using the data fields extracted from the report data model. Once the user has finished designing the report, the user then saves the design as a report definition data at 307. The steps taken by the reporting tool to generate the report definition are described in more detail in
Once the report data model has been loaded, the data fields in the report data model may be displayed to the user at 503. Using the tools provided by the reporting tool, the user may then define a report layout based upon these data fields. The report layout specifies how the data is to be displayed in the end user report, and may comprise a plurality of data visualizations, such as tables, charts, and graphs. For example, in some embodiments, the user may click on a button to create a new graph, and specify that the data of a particular data field be associated with an axis of the graph by dragging and dropping a data field from where it is displayed to an axis of the graph. Once the user has finished defining the report layout, it is saved as a report definition at 505.
The user may also modify various aspects of the chart in work area 508. For example, the user may change the title of the chart by clicking on the appropriate fields, and may specify which data fields the chart will represent by dragging the desired data fields at list 506 to the appropriate locations in the work area 508. When the user has finished defining the report layout, the resulting report definition may then be saved by clicking on the save button at 509. Once a report definition is saved, it is available to be executed by a user to create a new report.
The report definition created using the process described above and in
Next, data from the database is retrieved by the ERP application at and displayed to the user at 602. This may be data that the user was viewing when initializing the creation of a new query set. In some embodiments, a user may request certain data to be displayed after initializing the creation of a new query set. Data fields may be presented to the user to allow selection for the queries at 603. These data fields are extracted from the metadata established for the data/system. Interface elements are provided to display the data fields and for allowing selection of desired fields. For example, the system may display a “+” signs on data fields to allow them to be selected for the queries, where the user clicks on a “+” sign to include the data field as part of the queries. The user can repeat this action until all desired data fields have been selected. Once the desired data fields are selected, the user may then define the query criteria for the selected rows. For example, the user may define a query criteria specifying that the query return rows where the value in a selected data field contains a certain string, or falls within a user-defined numerical range. The user may define multiple criteria using the selected data fields. The user may configure whether a row is returned if it satisfies all of the query criteria, or just any of the criteria.
At 604, the ERP application extracts predicates based on the user inputs. The predicates may comprise logic based on the user selections specifying which rows in a data field are to be retrieved. For example, the predicates may comprise logic limiting the data to only include rows where the value in a certain column is within a user-specified range, or to only include rows where the value in a certain data field meets user-specified criteria.
At 605, once the ERP application has extracted the predicates based on the user's inputs and selections, the application automatically generates one or more queries based on the extracted predicates. The automatic generation of queries by the ERP application saves the user from having to understand how the data is structured in the underlying database, allowing for the user to make data selections using the more familiar and user-friendly ERP user interface. At 606, the user-defined set of queries may then be saved (e.g., initiated by the user clicking on a “Save” button) to the database.
Sidebar 609 also contains menus, buttons, or other user interface features with which the user can specify query criteria for the selected data fields. For example, in
Once the user has both a report definition and a set of queries, a report can then be created.
As noted above, the creation of the ERP report depends upon the definition of the data fields that will be included in its output. It is important for the correct data tables and fields are chosen to meet the desired report requirements.
Traditional report creation tools require deep knowledge of the database schema and table relationships. While end users understand the data from the context of the applications they work in, they typically do not have the database schema and table relationship knowledge required to build reports using traditional reporting tools. This often leads to a reliance on IT to build these reports. End users define their report requirements and present them to IT to build. The need to involve IT makes the process costly and inefficient.
According to some embodiments of the invention, the invention provides an approach of using dynamic, intuitive, field selection to provide the ability to effectively choose the data fields necessary to meet the requirements for the specific report they wish to create without the involvement of IT staff.
It is understood that the users will understand the transactional data from the context of the ERP applications, while likely not really understanding the underlying table schema itself. The present solution leverages the interactive applications end users are familiar with by providing the ability to build a report data model by simply choosing data fields directly from within the interactive applications they work with on a daily basis. The system will pass these selected data fields to the reporting tool where they represent the report data model. Within the reporting tool, the report data model with its data fields are available for the user to apply to their own custom report.
When the user executes their report, the system retrieves the data from the database and filters the data with the Report Data Model. The system packages the filtered data and generates a report using the layout the user has designed for the report.
Therefore, what has been described is a new approach that efficiently provides for end user reporting from interactive applications. One embodiment provides an approach for implementing dynamic field selection in the end user reports. The present embodiment provides for an interactive application that allows users to query data with query capabilities. The user is able to select data fields to be included in the reports intuitively, and directly from the interactive application screen. A metadata mechanism is employed to store the data fields (data model) of the reports. An interactive report design tool that allows user to design the report with a variety of charts, graphics and tables. An engine that queries the data based on the user defined query criteria, packages the user selected fields within the data and generate the report output on the fly.
Conventional ERP system fail to provide for such an effective, efficient, dynamic, intuitive method for end users to effectively choose the data fields for report creation. In the present embodiment, data field selection is an important feature that facilitates end user reporting solution which provides business insight to organizations without incurring IT development costs. Selecting data fields by simply pointing and clicking from within familiar applications makes the solution very easy to use, even for novice users. Users do not require knowledge of the data schema including tables, joins and data fields to retrieve the proper data. This is all handled by the interactive application which makes this solution a truly end user solution.
According to one embodiment of the invention, computer system 1400 performs specific operations by processor 1407 executing one or more sequences of one or more instructions contained in system memory 1408. Such instructions may be read into system memory 1408 from another computer readable/usable medium, such as static storage device 1409 or disk drive 1410. In alternative embodiments, hard-wired circuitry may be used in place of or in combination with software instructions to implement the invention. Thus, embodiments of the invention are not limited to any specific combination of hardware circuitry and/or software. In one embodiment, the term “logic” shall mean any combination of software or hardware that is used to implement all or part of the invention.
The term “computer readable medium” or “computer usable medium” as used herein refers to any medium that participates in providing instructions to processor 1407 for execution. Such a medium may take many forms, including but not limited to, non-volatile media and volatile media. Non-volatile media includes, for example, optical or magnetic disks, such as disk drive 1410. Volatile media includes dynamic memory, such as system memory 1408.
Common forms of computer readable media includes, for example, floppy disk, flexible disk, hard disk, magnetic tape, any other magnetic medium, CD-ROM, any other optical medium, punch cards, paper tape, any other physical medium with patterns of holes, RAM, PROM, EPROM, FLASH-EPROM, any other memory chip or cartridge, or any other medium from which a computer can read.
In an embodiment of the invention, execution of the sequences of instructions to practice the invention is performed by a single computer system 1400. According to other embodiments of the invention, two or more computer systems 1400 coupled by communication link 1415 (e.g., LAN, PTSN, or wireless network) may perform the sequence of instructions required to practice the invention in coordination with one another.
Computer system 1400 may transmit and receive messages, data, and instructions, including program, i.e., application code, through communication link 1415 and communication interface 1414. Received program code may be executed by processor 1407 as it is received, and/or stored in disk drive 1410, or other non-volatile storage for later execution.
In the foregoing specification, the invention has been described with reference to specific embodiments thereof It will, however, be evident that various modifications and changes may be made thereto without departing from the broader spirit and scope of the invention. For example, the above-described process flows are described with reference to a particular ordering of process actions. However, the ordering of many of the described process actions may be changed without affecting the scope or operation of the invention. The specification and drawings are, accordingly, to be regarded in an illustrative rather than restrictive sense.
The present application claims the benefit of priority to U.S. Provisional Application Ser. No. 61/593,098, filed on Jan. 31, 2012, which is hereby incorporated by reference in its entirety.
Number | Date | Country | |
---|---|---|---|
61593098 | Jan 2012 | US |