Account Hierarchy User-Defined Sort Order Using File-Based Data Import

When account hierarchy values are entered and created using the Segment Values and Hierarchies template, the financial reports defined with that hierarchy will display the account hierarchy members in the order in which they were entered, rather than in alphanumeric order.

Maintaining the original order provides more meaningful Smart View and Financial Reporting Studio reports, without having to use a manual means to achieve a particular sort order.

When you use any of the following methods or pages to create or update member hierarchies, the order in which you define the hierarchies is preserved.

• Rapid Implementation worksheet: When the enterprise structure is uploaded, the user-defined order of hierarchies is retained.
• Segment Values and Hierarchies file-based data import (FBDI) template: When the hierarchies are imported through the FBDI template, the user-defined order of hierarchies is preserved.
• Manage Account Hierarchies page: Incremental updates and changes preserve the sort order when viewed from the page.
• Manage Chart of Account Configurations page, Upload Hierarchy button: Incremental updates to existing hierarchies retain the user-defined sort order.

You can view hierarchies through several reports and pages. The imported members within the hierarchy display in the user-defined creation order, instead of in alphanumeric order. Here are some of those pages and reports.

• Financial Reporting Studio reports: When hierarchies are published to the GL balances cube, the user-defined order of hierarchies is retained when viewing hierarchies in Smart View and in Financial Reporting Studio reports.
• Inquire and Analyze Balances page: The user-defined hierarchy sort order is retained when balances display in the inquiry pages, that is, the Account Inspector.
• Manage Account Hierarchies page: Hierarchies viewed on this page appear in the order in which they are loaded through FBDI.
• Manage Chart of Accounts Configurations page, Download Hierarchy button: When hierarchies are downloaded, the FBDI file displays the hierarchy values in the order in which they were created.

Let’s say you define an account hierarchy for the cost center segment in the Segment Value Hierarchies tab of the Import Segment Values and Hierarchies FBDI template. Within that hierarchy, parent value 530 contains the following child values in this order: 531, 535, 533, 534, 532, and 536.

This image of the segment value hierarchies tab of the Import Segment Values and Hierarchies FBDI template shows the parent and child values in the cost center hierarchy. Parent value 530 and its child values are highlighted.

Next you generate the .csv file.

The generated GlSegmentHierInterface.csv file will populate a sort order column with a sequence number that represents the order in which the members have been entered in the template. This is the last column in the row. Tree nodes are numbered 1 through N, in the order of their entry by row. Child nodes of each parent are numbered 1 through N, that is, each subtree will have a sort order of 1 through N assigned to its child nodes. The sort order value is stored whenever the hierarchy members are created or updated.

This partial image of the generated .csv file shows the parent and child values for the cost center hierarchy. The sequence number at the end of each row is highlighted. For parent value 530, child values 531, 535, 533, 534, 532, and 536 have the following respective sequence numbers: 1, 2, 3, 4, 5, and 6.

After the hierarchy has been published to the GL balances cube, you define a report using Smart View or Financial Reporting.

This image of the cost center hierarchy in the report shows that the child values of parent value 530 appear in the user-defined order of 531, 535, 533, 534, 532, and 536, instead of in alphanumeric order.