Abstract:
Increased reporting capabilities from a database system may be available in a customer relationship system while functionality of the customer relationship management system is maintained.

Description:
BACKGROUND 
       [0001]    Enterprise resource planning (“ERP”) systems have made tracking resources and planning resource use easier. These interactive systems provide immediate information about resources which may be used to better service customers. In addition, the ERP system may store data in a different manner than many systems, such as storing actual total amounts rather than storing formulas to calculate total amounts. ERP systems have report generating abilities that make it easy to visualize the data in the ERP system. 
         [0002]    Database systems have been around even longer than ERP systems. Database systems store large amounts of data in a way that is easy to search and manipulate. Data is usually stored as individual entries and totals usually do not have their own entry but are calculated as needed by adding up the individual elements from the database. As database systems have been around for a significant period of time, the ability to create reports is refined. 
       SUMMARY  
       [0003]    A enterprise resource planning system may be used in conjunction with a database system to create reports. A report object may be used that contains layout information, a report subclass and metadata. The metadata may be used to determine what data should be accessed from a database, how it should be formatted and how it should be displayed. As a result, increased report capabilities from the database system may be available in the enterprise resource planning system while functionality of the enterprise resource planning system is maintained. 
     
     
       FIGURES 
         [0004]      FIG. 1  illustrates a method of using a ERP system to access the report capabilities of a database system; 
           [0005]      FIG. 2  is an illustration of one embodiment of the report object; 
           [0006]      FIG. 3  is a high level review of the creation of a report; and 
           [0007]      FIG. 4  is a specific illustration of the construction of a report. 
       
    
    
     DESCRIPTION 
       [0008]    An enterprise resource planning system (“ERP”) may be able to create some reports, but additional report creating abilities may be created by allowing the ERP system to use report functions from a database system. One method to allow a ERP system to access a database system is described herein. A sample ERP system may be Dynamics Nav by Microsoft Corporation and a sample database program may be SQL Reporting Services by Microsoft Corporation, but the principles of the method and apparatus may be application to virtually any ERP system and any database system.  FIG. 1  may illustrate a method of using a ERP system to access the report capabilities of a database system. 
         [0009]    Referring to  FIG. 1 , at block  100 , the method may select to run a selected report. A user or other program may define desired data items for a report. Data items may be a table with a key, sort order, filter criteria and a join/link condition with another data item such that multiple tables may be used in a report. The user may then pick the fields from the data item that he wants to see on the report. Next, the layout of the report may be selected. A layout designer may be to select that layout of the fields from the data items selected. The report may be designed using an addition program module, such as Visual Studio by Microsoft Corporation. The user may then save the report and continue with other tasks like defining a request form, and calling the report from a menu item or from other code. 
         [0010]    At block  110 , the method may create a report object based on selected report. Creating a report may include creating a layout description file using a layout generator, creating a subclass that contains report specific variables and triggers using a code generator and creating a report metadata file using a metadata generator. 
         [0011]      FIG. 2  may be an illustration of one manner in which the report object  200  is created. A layout generator  210  may be used to create the layout which is report definition language client (“RDLC”) file  220  or any other layout file. 
         [0012]    Also, a code generator  230  such as a C# Code Generator may be used to create a subclass for the report, such as a NavReport subclass. For each report, code for a subclass may be auto-generated. The code may be in a language such as C#. The code generation may basically follow the auto-code generation rules of other objects like tables, forms, and XML ports, etc. The subclass may contain report specific variables and implementations of the report and data item triggers. 
         [0013]    Sample C# code may be as follows: 
         [0000]    
       
         
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 public class Report3 : NavReport 
               
               
                   
                  { 
               
               
                   
                  private Table45 GLRegister; 
               
               
                   
                  private Table17 GLEntry; 
               
               
                   
                  private Table15 GLAcc; 
               
               
                   
                  private NavText GLRegFilter; 
               
               
                   
                  private void InitializeComponent( ) 
               
               
                   
                  { 
               
               
                   
                  } 
               
               
                   
                  protected override void OnInitReport( ) 
               
               
                   
                  { 
               
               
                   
                   GLRegFilter = GLRegister.GetFilters( ); 
               
               
                   
                  } 
               
               
                   
                 } 
               
               
                   
                   
               
             
          
         
       
     
         [0014]    The report object  200  also may use a metadata generator  250  to create a metadata report  250 . The metadata report  250  may be in XML, for example, or in any other suitable language. The metadata file may simply be an XML document that describes the basics of a report. It may contain:
       The report name, captions, permissions, transaction mode, etc.   The data items used in the report, with table views and links between tems; and   The fields of each data item.       
 
         [0018]    A sample metadata file may be as follows: 
         [0000]    
       
         
               
             
           
               
                   
               
             
             
               
                 &lt;?xml version=“1.0” encoding=“utf-8” ?&gt; 
               
               
                 &lt;Report&gt; 
               
               
                  &lt;Id&gt;108&lt;/Id&gt; 
               
               
                  &lt;Name&gt;Customer - Order Detail&lt;/Name&gt; 
               
               
                  &lt;DataItems&gt; 
               
               
                   &lt;DataItem&gt; 
               
               
                    &lt;DataItemIndent&gt;0&lt;/DataItemIndent&gt; 
               
               
                    &lt;DataItemTable&gt;Customer&lt;/DataItemTable&gt; 
               
               
                    &lt;DataItemVariableName&gt;&lt;/DataItemVariableName&gt; 
               
               
                    &lt;Fields&gt; 
               
               
                     &lt;FieldName&gt;No.&lt;/FieldName&gt; 
               
               
                     &lt;FieldType&gt;Code&lt;/FieldType&gt; 
               
               
                    &lt;/Fields&gt; 
               
               
                    &lt;ReqFilterFields&gt;No.,Search Name,Priority&lt;/ReqFilterFields&gt; 
               
               
                    &lt;PrintOnlyIfDetail&gt;Yes&lt;/PrintOnlyIfDetail&gt; 
               
               
                   &lt;/DataItem&gt; 
               
               
                   
               
             
          
         
       
     
         [0019]    Report metadata can be seen as an XML version of the report properties. This dataset and layout are then presented to SQL Reporting Services for rendering this report for preview or printing purposes. 
         [0020]    The reporting runtime may be a .NET implementation that is basically built around an object such as NavReport, and its subtypes. This runtime may be responsible for the following:
       Integrating with the rest of the runtime environment;   Based on data items, accessing data, sorting, filtering records to be accessed;   Executing application code (which may be transformed from application language into C#);   Implementing specific application security and indirect security concepts from the ERP system;   Supporting transaction model in the report properties; and   Binding report parameters and request forms together.       
 
         [0027]    At block  120 , the method may review metadata of the report object to determine whether a request form for a database system is needed. If the report needs to display a request form, the report metadata may note this. If a request form is needed, an event may be raised which may be caught. Then, communication may begin to start a communication with the client to display the request form. At this point, the report object may be instantiated. The method is designed such that the code-behind functionality between the request form and the report object instance may be supported, so when the user makes changes on a request form, the report variables get updated properly. 
         [0028]    A sample schema for the metadata may be as follows: 
         [0000]    
       
         
               
             
           
               
                   
               
             
             
               
                   &lt;?xml version=“1.0” encoding=“utf-8”?&gt; 
               
               
                   &lt;xs:schema 
               
               
                 targetNamespace=“http://www.microsoft.com/Dynamics/Nav/Metadata/Report.xsd” 
               
               
                 elementFormDefault=“qualified” 
               
               
                 xmlns=“http://www.microsoft.com/Dynamics/Nav/Metadata/Report.xsd” 
               
               
                 xmlns:mstns=“http://www.microsoft.com/Dynamics/Nav/Metadata/Report.xsd” 
               
               
                 xmlns:xs=“http://www.w3.org/2001/XMLSchema”&gt; 
               
               
                    &lt;xs:element name=“Report”&gt; 
               
               
                     &lt;xs:complexType&gt; 
               
               
                      &lt;xs:sequence&gt; 
               
               
                       &lt;xs:element name=“Id” type=“xs:string” minOccurs=“1” 
               
               
                 maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“Name” type=“xs:string” minOccurs=“1” 
               
               
                 maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“Caption” type=“xs:string” minOccurs=“1” 
               
               
                 maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“CaptionML” type=“xs:string” minOccurs=“1” 
               
               
                 maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“ShowPrintStatus” type=“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“UseRequestForm” type=“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“ProcessingOnly” type=“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“UseSystemPrinter” type=“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       21 xs:element name=“TransactionType” minOccurs=“1” 
               
               
                 maxOccurs=“1”&gt; 
               
               
                        &lt;xs:simpleType&gt; 
               
               
                         &lt;xs:restriction base=“xs:string”&gt; 
               
               
                          &lt;xs:enumeration value=“UpdateNoLocks” /&gt; 
               
               
                          &lt;xs:enumeration value=“Update” /&gt; 
               
               
                          &lt;xs:enumeration value=“Snapshot” /&gt; 
               
               
                          &lt;xs:enumeration value=“Browse” /&gt; 
               
               
                          &lt;xs:enumeration value=“Report” /&gt; 
               
               
                         &lt;/xs:restriction&gt; 
               
               
                        &lt;/xs:simpleType&gt; 
               
               
                       &lt;/xs:element&gt; 
               
               
                       &lt;xs:element name=“Description” type=“xs:string” minOccurs=“1” 
               
               
                 maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name=“Permissions” type=“xs:string” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:sequence&gt; 
               
               
                        &lt;xs:element ref=“DataItem” minOccurs=“1” 
               
               
                 maxOccurs=“unbounded”/&gt; 
               
               
                       &lt;/xs:sequence&gt; 
               
               
                      &lt;/xs:sequence&gt; 
               
               
                     &lt;/xs:complexType&gt; 
               
               
                    &lt;/xs:element&gt; 
               
               
                    &lt;xs:element name=“DataItem”&gt; 
               
               
                     &lt;xs:complexType&gt; 
               
               
                      &lt;xs:sequence&gt; 
               
               
                       &lt;xs:element name =“DataItemIndent” type =“xs:integer” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“DataItemTable” type =“xs:integer” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“DataItemTableView” type =“xs:string” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“DataItemLinkReference” type =“xs:integer” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“DataItemLink” type =“xs:integer” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“RequestFilterHeading” type =“xs:string” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“RequestFilterHeadingML” type 
               
               
                 =“xs:string” minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“RequestFilterFields” type =“xs:string” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“MaxIteration” type =“xs:integer” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“DataItemVariableName” type =“xs:string” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“PrintOnlyIfDetail” type =“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element ref=“DataItemField” minOccurs=“1” 
               
               
                 maxOccurs=“unbounded”/&gt; 
               
               
                      &lt;/xs:sequence&gt; 
               
               
                     &lt;/xs:complexType&gt; 
               
               
                    &lt;/xs:element&gt; 
               
               
                    &lt;xs:element name=“DataItemField”&gt; 
               
               
                     &lt;xs:complexType&gt; 
               
               
                      &lt;xs:sequence&gt; 
               
               
                       &lt;xs:element name =“Name” type =“xs:string” minOccurs=“1” 
               
               
                 maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“DataType” type=“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“SourceExpression” type =“xs:string” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                       &lt;xs:element name =“AutoCalcField” type=“xs:boolean” 
               
               
                 minOccurs=“1” maxOccurs=“1”/&gt; 
               
               
                      &lt;/xs:sequence&gt; 
               
               
                     &lt;/xs:complexType&gt; 
               
               
                   
               
             
          
         
       
     
         [0029]    At block  130 , if the request form is not needed, the report is created as usual. 
         [0030]    At block  140 , if the request form is needed, the request form is created using the metadata of the report object for the database system. At the time of compilation or during import, two artifacts may be generated: Report metadata, which is an XML document that may be saved into an object metadata table as with any other metadata document, and a NavReport subclass, specific to the report being compiled. If the report has a request form, those also may be transformed into the corresponding artifacts as well. 
         [0031]    Report metadata can be seen as an XML version of the report properties. It contains enough information for the reporting runtime to generate a report dataset if the report does not contain any code. In other words, reporting runtime should be able to take a Report Metadata and a NavReport subclass that has no event handlers or local variables of its own, and then generate a report dataset. 
         [0032]    At block  150 , a filter may be selected for the selected report. The filter may be based on data items and may be used to filter query results. Filters are well known and any logical filter may be used. 
         [0033]    At block  160 , the method may determine tables in the database system needed using the metadata of the report object. The metadata may contain virtually any information. Depending on the database used, the metadata may vary. Sample metadata has been disclosed previously. 
         [0034]    At block  170 , the method may execute the database query on the database system to obtain report results. Database queries are well known. In short, criteria are used to obtain the desired data out of a large quantity of data in the database. 
         [0035]    At block  180 , the method may create a report using the report results from the database query. By using the ERP system, advanced report features may be available such as increased colors, option to create reports in PDF, etc. 
         [0036]      FIG. 3  may be a high level review of the creation of a report. An end user  300  may select a menu item in a ERP system  305  such as Navision to run a report. The ERP system  305  may make a call to a ERP server such as the Navision Service Tier  310  to initialize a NavReport object and check its metadata to see if it requires a request form. If the report requires a request form  315 , the ERP system  305  may build a request form based on the parameters on the report metadata, typically using request form  315  specific parameters on the data items. The user may enter a filter. The ERP client may communicate the changes back to the ERP server  310 . The user may select on Preview such as a report viewer  320 . The request may be communicated to a report server  325 . The ERP system  305  may initiate the execution of the report query through a reporting runtime by communicating a file such as a data processing extension file  335  to the report runtime  340 . Reporting runtime  340  may use the report metadata for detecting which tables in the database  345  to use, the report object to get the parameters set and to execute the code on triggers and retrieves data from SQL  350 . The user may retrieve the resulting data (DataSet) and the format file (RDLC) from the ERP service tier  310  and passes them to the Report Viewer  320 , which renders and displays the report. 
         [0037]      FIG. 4  may be a specific illustration of the construction of a report. In this example, Dynamics Nav is the ERP system used. At block  400 , an end user may select to run a report  405  using the Dynamics client  410 . A run report application may begin  415 . In  FIG. 5 , the application may be RunReport(reportID). The application may call to a server to complete the request  420 . In the example in  FIG. 5 , the method may call to a Navision server service  420 . The Navision server service  420  may call a report run function  425  such as Run(reportID). This request may be forwarded to a specific report running function  425  such as NavReport  430 . The report running function  425  may call for a new report  435  using the specific request data  440 , such as Report  3 , Instance 42  in  FIG. 4 . The report  435  may initialize  445  the needed components and the BeginInitialization function  450  may begin. A request for a new data item may be made  455 . Metadata for the requested report  460  may be stored in a table and the metadata may be used to determine the data to be retrieved  465  from the database  470 . 
         [0038]    For each data item  475  retrieved from the database  470 , a new record  480  may be created and the data item  485  may be registered for an event  490 . The data item  485  may then be added to the report  495 . At block  497 , the initialization may end and the report  435  may be ready. 
         [0039]    The following may be psuedocode implementing the above example. 
         [0000]    
       
         
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 public class Report3 : NavReport 
               
               
                   
                  { 
               
               
                   
                   public Report3( ) 
               
               
                   
                    : base(3) 
               
               
                   
                   { 
               
               
                   
                    InitializeComponent( ); 
               
               
                   
                   } 
               
               
                   
                   public Report3(NavConnection connection) 
               
               
                   
                    : base(connection, 3) 
               
               
                   
                   { 
               
               
                   
                    InitializeComponent( ); 
               
               
                   
                  } 
               
               
                   
                  private void InitializeComponent( ) 
               
               
                   
                   { 
               
               
                   
                    BeginInitialization( ); 
               
               
                   
                    glEntry = new Table17( ); 
               
               
                   
                    glRegister = new Table45( ); 
               
               
                   
                    glAcc = new Table15( ); 
               
               
                   
                    glRegFilter = new NavText( ); 
               
               
                   
                    /* Instantiate DataItems and register their event handlers. */ 
               
               
                   
                    glEntryDataItem = new NavDataItem(glEntry); 
               
               
                   
                    Add(glEntryDataItem); 
               
               
                   
                    glEntryDataItem.OnAfterGetRecord = 
               
               
                   
                 glEntryDataItem_OnAfterGetRecord; 
               
               
                   
                    glEntryDataItem.OnPreDataItem = 
               
               
                   
                    glEntryDataItem_OnPreDataItem; 
               
               
                   
                    glRegisterDataItem = new NavDataItem(glRegister); 
               
               
                   
                    Add(glRegisterDataItem); 
               
               
                   
                    EndInitialization( ); 
               
               
                   
                   } 
               
               
                   
                   void glEntryDataItem_OnPreDataItem( ) 
               
               
                   
                   { 
               
               
                   
                    glEntry.SetRange(“Entry No.”, glRegister.FromEntryNo, 
               
               
                   
                 glRegister.ToEntryNo); 
               
               
                   
                   } 
               
               
                   
                   void glEntryDataItem_OnAfterGetRecord( ) 
               
               
                   
                   { 
               
               
                   
                    if (!glAcc.Get(DataError.ThrowError, “G/L Account No.”)) 
               
               
                   
                    { 
               
               
                   
                      glAcc.Init( ); 
               
               
                   
                    } 
               
               
                   
                   } 
               
               
                   
                   protected override void OnInitReport( ) 
               
               
                   
                   { 
               
               
                   
                     glRegFilter = glRegister.GetFilters( ); 
               
               
                   
                   } 
               
               
                   
                    private Table17 glEntry; 
               
               
                   
                    private Table45 glRegister; 
               
               
                   
                    private NavDataItem glEntryDataItem; 
               
               
                   
                    private NavDataItem glRegisterDataItem; 
               
               
                   
                    private Table15 glAcc; 
               
               
                   
                    private NavText glRegFilter; 
               
               
                   
                  } 
               
               
                   
                   
               
             
          
         
       
     
         [0040]    Other advantages to the method may be available. By keeping the ERP system functional and not just running reports from the SQL system, items that are part of the ERP system may be available as part of the report. For example, ERP systems often have “flowfields” or fields that are actually summations of a plurality of other fields. In a traditional SQL reporting system, each of the individual fields may have to be selected and added before being added to a report. Using the flowfield concept from ERP, only one entry needs to be selected. 
         [0041]    In addition, by keeping the ERP system as the interface into the database system, an extra layer of protection is added to the database system. As the data in the database is likely to be sensitive and unauthorized editing of the data could be devastating, allowing access to the database only through the ERP system may keep users further removed from the underlying data. 
         [0042]    Additionally, ERP systems allow users to design a single report to work with multiple companies. For example, when a user designs a report, it may be designed generically against a table, and it may be run against any company. By accessing a plurality of different databases such as databases specific to each company, the generic report will be company specific. 
         [0043]    Another novel aspect is that it is possible to execute additional business logic by calling other application objects like tables, or codeunits to make calculations while generating the report. For example, a tax amount may be calculated on the fly and this tax amount would not be stored in a database alone. As the business logic resides in the service tier and not in the database, a database reporting system may be unable to access a calculated tax amount (assuming it is not stored in the database as a separate entry). 
         [0044]    Although the forgoing text sets forth a detailed description of numerous different embodiments, it should be understood that the scope of the patent is defined by the words of the claims set forth at the end of this patent. The detailed description is to be construed as exemplary only and does not describe every possible embodiment because describing every possible embodiment would be impractical, if not impossible. Numerous alternative embodiments could be implemented, using either current technology or technology developed after the filing date of this patent, which would still fall within the scope of the claims. 
         [0045]    Thus, many modifications and variations may be made in the techniques and structures described and illustrated herein without departing from the spirit and scope of the present claims. Accordingly, it should be understood that the methods and apparatus described herein are illustrative only and are not limiting upon the scope of the claims.