Method and apparatus for mapping dimension-based accounting entries to allow segment-based reporting

A method and apparatus are provided for mapping dimension-based financial data to allow segment-based reporting. The method includes selecting a set of dimensions from the dimension-based accounting data and defining an order for the selected dimensions to define a segment-based account code structure. The segment-based account code structure and the dimension-based data are then used to construct segment-based account codes that can be used in segment-based reporting.

BACKGROUND OF THE INVENTION

The present invention relates to financial and accounting software systems. In particular, the present invention relates to reporting tools used in conjunction with accounting software.

In early versions of accounting software, account numbers were used to associate financial data with particular accounts, such as a particular customer. To allow for more detailed accounting information, the concept of the account was extended by segmenting the account number so that different portions of the account number could be associated with specific entities within the company. For example, financial transactions performed by a particular department within the company or a particular sales person within the company could be assigned unique values within a segment of the account code. Thus, if the natural account number is 011, the department identifier is 52, and the sales person identifier is 48, an extended account code can be constructed as 0115248 to identify those financial transactions that are associated with the particular sales person in the particular department for the account. Accounting systems that use such account codes are known as segment-based accounting systems, because the account code can be divided into various segments to identify particular entities or attributes associated with the financial transaction.

Such segment based accounting systems have strict rules regarding the values that can be placed in each segment. In particular, the rules require that once the length of the segment has been defined, all of the entries placed in that segment must be of that length. Such restrictions make it difficult for some users to work with the accounting system.

As a result, dimension-based accounting systems were developed that gave the user more freedom in defining the attributes associated with an account. Under dimension-based accounting, financial transactions are identified by the natural account number and one or more user-defined dimensions. Each dimension is loosely defined such that there are no restrictions on the values that may be set for a dimension after the dimension is created. For example, a “Department” dimension can receive values of different lengths such as “52” and “claims” because there are no restrictions on the lengths of the entries for the dimension.

Reporting tools have been developed for segment-based accounting systems that rely on rigid definitions for the segments within the account codes. Because of the free-form nature of dimension-based accounting systems, the segment-based reporting tools have not been able to generate reports from dimension-based accounting data. Because of this, specialized reporting tools have been developed for dimension-based accounting. This requires users to use different reporting tools for different accounting systems, thereby increasing the learning burden on the user.

SUMMARY OF THE INVENTION

A method and apparatus are provided for mapping dimension-based financial data to allow segment-based reporting. The method includes selecting a set of dimensions from the dimension-based accounting data and defining an order for the selected dimensions to define a segment-based account code structure. The segment-based account code structure and the dimension-based data are then used to construct segment-based account codes that can be used in segment-based reporting.

DETAILED DESCRIPTION OF ILLUSTRATIVE EMBODIMENTS

As shown inFIG. 2, the present invention provides a dimension mapping tool200, which creates mapping additions202for a dimension database204. Dimension database204contains dimension-based tables203created for a dimension-based financial program206. Mapping additions202allow a segment-based reporting tool208to generate financial reports from data in dimension-based tables203by mapping the dimensions in the dimension-based tables into segments for one or more account codes.

FIG. 3provides a method for forming mapping additions202. At step300ofFIG. 3, the user selects a database and a company found within the database. Under one embodiment, the companies available in a database are stored in a DATAAREA table400ofFIG. 2. An example of a DATAAREA table400is shown inFIG. 4and includes an ID field402, an ENTRY NAME field404and a ISVIRTUAL field406. ID field402provides a unique identifier for each company. ENTITY NAME404provides a more user-friendly name for the company. ISVIRTUAL field406indicates whether the company represents a true company or is a fictional company that is formed by combining information from one or more other companies.

At step302ofFIG. 3, the process evaluates a set of dimension entries for the selected company. The dimension entries represent possible values for various dimensions defined in dimension-based tables203. Under one embodiment, the dimension entries are stored in a DIMENSIONS table500. As shown inFIG. 5, DIMENSIONS table500includes an ENTRY DESCRIPTION field502, an ENTRY NAME field504, a DIMENSION CODE field506and a DATAAREAID508. DATAAREAID508indicates the company associated with this dimension entry. DIMENSION CODE506indicates the dimension associated with this dimension entry. ENTRY NAME504provides the name for this entry, and ENTRY DESCRIPTION502provides a user friendly description of the dimension entry. As shown inFIG. 5, there can be more than one dimension entry for a particular dimension. Thus, there are two entries for dimension code0inFIG. 5. In addition, the same dimension code may be used with different data area ID's. Thus, different companies can use the same dimension code. Similarly, different companies can use the same dimension entry names and entry descriptions.

During step302, all of the entries in DIMENSIONS table500that have a DATAAREAID value that matches the company selected in step300are evaluated. In particular, the ENTRY NAME field is examined for each of the company's entries to determine if the entry name includes one of a set of invalid characters and whether the entry name exceeds a maximum length set for the entry name.

Under one embodiment, the set of invalid characters includes:/\( );=,″!'−( )*&^#@$%{ }+?|

and the character sequence “http://”. Thus, any entry name that includes one of these characters or the “http://” string is flagged as being in error. Each time an entry is flagged as being in error, it is added to a log of errors for the company.

The length of each entry name is compared to a length that is stored in a DATA DIMENSION LENGTH table600in the mapping addition section202. As shown inFIG. 6, DATA DIMENSION LENGTH table600includes a DATAAREAID field602, a DIMENSION CODE field604, a FIELDNAME field606, a DIMENSION DESCRIPTION field608and a MAXLENGTH field610. DATAAREAID field602contains the company identifier associated with this dimension. DIMENSION CODE field604is the same as DIMENSION CODE field506of DIMENSIONS table500. FIELDNAME field606contains the names of fields in dimension-based tables203. Specifically, these field names are used as field headings in a set of General Ledger tables220to identify dimensions within General Ledger tables220. For example, the field name “DIMENSION2—” would appear as a heading in a general ledger table such as LEDGERBALANCESDIM table700.

DIMENSION DESCRIPTION field608ofFIG. 6includes a generic description for the dimension and MAXLENGTH field610includes the maximum length for the dimension.

When validating a dimension entry in DIMENSIONS table500, the dimension code found in the DIMENSION CODE field of the dimension entry and the data area ID found in the DATAAREAID field of the dimension entry are used to search DATA DIMENSION LENGTH table600. Upon finding an entry in DATA DIMENSION LENGTH table600that matches the data area ID and the dimension code of the dimension entry, the maximum length value from MAXLENGTH field610is retrieved. This maximum length value is then compared to the number of characters in the entry name found in ENTRY NAME field504of the dimension entry. If the number of characters in the entry name exceeds the maximum length, an error is generated and stored in the error log. Each entry in the error log includes identification information such as the entry name from ENTRY NAME field504and the dimension description from DIMENSION DESCRIPTION field608for the dimension associated with the dimension entry that was in error.

Step302also includes examining the ENTRY DESCRIPTION field of each of the company's entries in DIMENSIONS table500to determine if an entry description includes a null value. If a null value is found for an entry description, the description of the dimension from DIMENSION DESCRIPTION field608and the entry name from ENTRY NAME field504are stored in the error log along with an indication that the entry description for the entry had a null value.

At step304, the method determines if there were any errors in the dimension entries. If there were errors, an error compliance screen is displayed at step306.

An example of an error compliance screen is shown inFIG. 8for an invalid character error. In error compliance screen800, the user is provided with an identification of the company802, a dimension description808, an entry name810that includes the invalid character, and an identification of the invalid character812. As noted above, entry name810is taken from DIMENSIONS table500and dimension description808is taken from DIMENSION DESCRIPTION field608of DATA DIMENSION LENGTH table600.

To correct the invalid character error, the user must access the tables in dimension-based tables203and change the entry name so that it does not include the invalid character. This is typically done using a tool associated with financial program206so that the change in the entry name can be propagated through all of the necessary tables in dimension-based tables203. To assist the user, an export button814is provided that will export the error log to an output format such as a spreadsheet so that the user has access to the error log while changing the entry names in dimension-based tables203.

FIG. 9provides an example of an error compliance screen900that is presented to the user when an entry name exceeds the maximum length set in DATA DIMENSION LENGTH table600. In screen900, the user is told that one or more entry names exceed the length set for their dimension. The user must either modify the entry in the DIMENSIONS table so that its length is below the length set in table600or they may select the next button to set a new value for the length of the dimension. Pressing the next button will cause a screen to be displayed for setting the lengths of the dimensions. This screen is discussed further below. To assist the user, an export button910is provided that will export the error log to an output format such a spreadsheet.

InFIG. 9the entry that has an entry name that is too long is identified by the company902, the dimension description904and the entry name906of the entry. The company is the company associated with the data area ID value found in DATAAREAID field508of the entry. The description904is the dimension description found in DIMENSION DESCRIPTION filed608of DATA DIMENSION LENGTH table600for the dimension code of the entry, and the entry name is taken from the ENTRY NAME field504of the entry.

FIG. 10provides an example of a compliance error screen1000that is presented to a user when an entry description in an entry includes a null value. The entry with the null value is identified in screen1000by a company identifier1002, a description identifier1004, and an entry identifier1006. Company identifier1002is the name of the company associated with the data area ID in the DATAAREAID field of the entry. Description identifier1004is the description in DIMENSION DESCRIPTION field608of DATA DIMENSION LENGTH table600. Entry name identifier1006is the entry name in ENTRY NAME field504of the entry.

To assist the user, an export button1010is provided that causes the error log to be exported to a spreadsheet file so that the user may print out the spreadsheet when making changes to the entry description. To correct an entry description that has a null value, the user must change the description in DIMENSIONS table500. This is typically done using a tool associated with financial program206so that the change can be propagated to all of the tables necessary in dimension-based tables203.

Step306temporarily ends the process until the user has corrected the errors in the dimensions-based tables. Once the errors have been corrected, the user begins the process again at step300and repeats step302and304. When there are no longer any errors at step304, the process continues at step308where the system allows the user to verify/change the lengths for the dimensions associated with the company.

FIG. 11provides a display screen1100that displays the dimensions associated with the company and allows the user to change the dimension length associated with each dimension. This is the same screen that is displayed to the user when they press Next button908in the compliance error screen ofFIG. 9.

In display screen1100, a table is provided with an account/dimension name identifier1102, a description identifier1104and a dimension length identifier1106. Account/dimension name1102is taken from FIELDNAME field606of DATA DIMENSION LENGTH table600for a dimension associated with the data area ID of the selected company. Description identifier1104is taken from DIMENSION DESCRIPTION field608of table600and the dimension length1106is populated with values from MAXLENGTH field610of table600. The table in display screen1100is populated by looking for every entry in table600for the selected company using DATAAREAID field602.

After the user has set the dimension lengths in dimension length identifier field1106, they press the next button1108. Upon the pressing of button1108, the system validates the lengths of the entries at step310ofFIG. 3. This validation involves ensuring that the user has not reduced the length of any of the dimensions and ensuring that the new dimension length is larger than the largest entry name for the dimension in DIMENSIONS table500. If the user attempts to reduce the size of the dimension length, they are returned to display screen1100with an error message indicating that the dimension length cannot be reduced in size. If the chosen dimension length is smaller than an entry name for the dimension in DIMENSIONS table500, the error is logged with an identification of the entry that has an entry name larger than the set dimension length.

At step312, the system determines if there are errors in the error log. If there are errors in the error log, error compliance screen900is displayed, which lists all of the dimension entries that have entry names larger than the dimension lengths set in screen1100. When the user selects next button908inFIG. 9, they are returned to screen1100, where they are allowed to modify the dimension lengths. Thus, the process ofFIG. 3returns from displaying an error compliance screen at step314to step308to allow the user to verify/change the lengths for the dimensions.

When there are no longer any errors at step312, the process ofFIG. 3continues at step316where the user is allowed to select or create a new account code structure to edit. Because separate account codes are generally constructed for different entities within a company, these structures are alternatively referred to as entity structures or simply as entities.

FIG. 12provides a display screen1200that is displayed to the user to allow them to select an existing account code structure to edit or delete or to select an option to create a new account code structure.

To edit an existing account code structure, the user highlights one of the account code structures provided in a list1202. The user then selects edit button1204. The user may also delete an account code structure that has been highlighted by pressing delete button1206. If a user wishes to create a new account code structure they simply press new button1208.

If the user presses new button1208or edit button1204, the system provides the user with display screen1300ofFIG. 13and allows the user to edit the account code structure as shown in step318ofFIG. 3. In display screen1300, the dimension descriptions of the current dimensions that form the selected account code structure are found in display box1302. If a new account code structure is being built, box1302will be empty. The dimensions are organized in box1302such that the first dimension in the account code is at the top of list box1302and the subsequent dimensions are found in order below the top dimension.

Display screen1300also provides a list1304of available dimensions that may be added to the account code structure. The names of the available dimensions are taken from DIMENSION DESCRIPTION field608of DATA DIMENSION LENGTH table600. In particular, all of the dimensions stored in DATA DIMENSION LENGTH table600for the selected company are displayed in box1304, with the exception of those that have already been placed in box1302.

A user may add a dimension from box1304to box1302pressing button1306after highlighting the dimension that they wish to add to box1302. A user may remove a dimension from box1302and place it back in box1304by highlight the dimension in box1302and then pressing button1306.

When a dimension is added to box1302, it is added to the bottom of the list. A user may reorder the dimensions in box1302by highlighting a dimension and pressing button1308to move the dimension vertically. The final order of the dimensions in box1302will be used to form the account code structure.

To assist the user when determine which dimensions to include in box1302, a View Dimension Values button1310is provided on display screen1300. When the user presses View Dimension Values button1310for a highlighted dimension in box1304, a window with sample dimension entry names for the selected dimension is displayed. This helps the user to recognize what the dimension actually represents.

When the user has selected and ordered the dimensions as desired in box1302, the user presses next button1312.

Upon receiving the next button input, the system displays screen1400ofFIG. 14. In display screen1400, the user is allowed to name the account code structure by typing in the name in box1402. The name provided for the account code structure is referred to as an entity name and under one embodiment is always preceded by the company name for the company selected for this account code structure. Thus, for example, inFIG. 14, the full entity name would be “AAA-TRAVEL BY SALES”. In display screen1400, the structure of the account code is shown in entity structure box1404. After the user has entered the name and pressed “Save and Finish” button1406, the method of the present invention adds the account code structure to database204at step320.

Step320involves creating entries in two tables in mapping additions202. The first entries are in ENTITY table1500ofFIG. 15. ENTITY table1500includes an ENTITY NUM field1502, an ENTITY CODE field1504and an ENTITY DESC. field1506. Under one embodiment, a value for the ENTITY NUM field is automatically assigned for each new entity added to ENTITY table1500. The ENTITY CODE field receives the data area ID for the company associated with this entity. ENTITY DESC. field1506receives the name of the account code structure provided in display screen1400.

The actual structure of the entity is placed in a SEGMENT CTRL table1600ofFIG. 16. In SEGMENT CTRL table1600, each row represents a dimension within a single entity, or account code structure. Table1600includes ENTITY NUM field1602, SEG-NUM field1604, DIMENSION CODE field1606, COLUMN NAME field1608, SEG DESC. field1610, SEG START POS. field1612, and SEG LENGTH field1614.

ENTITY NUM field1602identifies the entity associated with this row using the entity number of ENTITY NUM field1502of table1500. SEG-NUM field1604identifies which segment in the entity is represented by this row, where the segments are numbered from left to right in the entity structure. DIMENSION CODE field1606provides the dimension code from DIMENSION CODE field604of table600for the dimension that is placed at this segment in the entity. COLUMN NAME field1608provides the field header name for this dimension in dimension-based tables203. For example, one of the names in COLUMN NAME1608could be “DIMENSION2_”, which is field header702in LEDGERBALANCESDIM table700ofFIG. 7.

SEG DESC. field1610provides the dimension description name for the dimension in this segment, where the dimension description name is the same as the dimension description names in DIMENSION DESCRIPTION field608of table600. SEG START POS. field1612indicates the character position within the entity structure where this segment begins. SEG LENGTH field1614indicates the length of this segment, which is taken from MAXLENGTH field610of table600. In table1600, every segment of the entity is listed as a separate row with the same entity number.

After the entity structure (account code structure) has been added to the tables of mapping additions202, mapping tool200builds a local account code table of active account codes at step322. This local table of account codes reduces the search space for possible account codes when building reports. An example of a local account code table1700is shown inFIG. 17.

To produce an entry for ACCOUNT CODE table1700, mapping tool200accesses LEDGERBALANCESDIM dim table700ofFIG. 7. It searches for any entry that has a balance value, such as a value in DEBITMST field710or CREDITMST field712, and retrieves the account number from ACCOUNTNUM field704and all of the values for dimensions associated with the new account code structure. The values for the dimensions are located in the dimension fields such as DIMENSION field706, DIMENSION2_field702, and DIMENSION10_field708.

The exact form of the account number is placed in ACCT_CODE ORIG field1708. The account number is placed exactly as it appears in LEDGERBALANCESDIM table700, including any leading or trailing spaces so that direct comparisons to the account numbers stored in dimension-based tables203can be made when generating reports.

The account code is also stripped of its leading and trailing zeros and stored in NAT_SEG field1710and SEG01CODE field1712. Each of the retrieved dimension values are stored in a respective dimension field. For example, the value from DIMENSION2field702is stored in SEG02CODE field1714.

The entity number for the account code structure is stored in ENTITY NUM field1704. The full account code is then constructed by combining the account number and dimensions according to the account code structure stored in SEGMENT CTRL table1600. In addition, the descriptions in SEG DESC. field1610are appended together based on the order of the segments in the entity structure to form an account code description that is stored in ACCT_DESC. field1706.

The values in ACCCOUNT CODE table1700may be refreshed from time to time without constructing the entity structure from scratch. To refresh the entries in ACCOUNT CODE table1700for an entity structure, the entity structure is highlighted in screen1200and REFRESH COA button1210is pressed. LEDGERBALANCESDIM table700is then searched for entries that have balance values and the values of the dimensions associated with the selected entity are returned and used to construct account codes for ACCOUNT CODE table1700.

Once the account codes have been added to account code table1700, they may be used for generating reports. In particular, segment-based reporting tool208can set ranges and masks based on rigid rules for the account codes, since the constructed account codes have fixed lengths for the various segments and do not include any invalid characters. A reference to an account code is converted into a reference to the dimension tables by locating the entity associated with the account code in account code table1700, and retrieving the respective column names from table1600for that entity. These column names identify the fields in the dimension-based tables203for the various segments of the account code. The values in these segments can then be used to search the tables in dimension-based tables203to collect data for the reports.

Thus, the present invention provides a mapping tool that allows a segment-based reporting tool208to access and generate reports from a dimension-based database, thereby avoiding having to write a specialized reporting tool for the dimension-based database.

Although the embodiments above describe the mapping additions as being added to the database associated with the dimension-based financial program, those skilled in the art will recognize that the tables in the mapping addition could exist in a separate database from the database for the dimension-based financial program.