Business Unit Trees provide powerful options for Business Unit reporting with the flexibility to map business units to a header hierarchy including consolidation options at each level. Check out the Business Unit Trees help article for more details, but these structures can also be exported and imported from Excel which is what we will cover in this help article.
Navigate Reports -> Report Tools -> Business Unit Trees
orSettings -> Organisation Settings -> Business Unit Trees
From the selected tree dropdown select the tree you would like to export.
Export Business Unit Tree
From the selected tree dropdown click the settings cog and choose Export tree.
Import Business Unit Tree
Warning ⚠️: Importing from a spreadsheet replaces the entire Business Unit Tree with your import values, and the process cannot be reversed.
We recommend exporting your existing account tree as a backup first. Click Export your Business Unit Tree to download a backup before proceeding.
From the Business Unit Tree Selector, click the dropdown and choose Import tree.
BROWSE and select the file you would like to import, choose the sheet to import then click NEXT STEP.
Use the checkboxes to select the organisations from which you would like to map Business Units to this tree.
Map all the required fields to the column headings in your spreadsheet the click NEXT STEP
Tip: 💡If the spreadsheet column headings match the required field names these will be mapped automatically. See spreadsheet requirements below for detailed requirements.
Calxa will verify the spreadsheet rows can be imported and list any errors. If no errors are detected click NEXT STEP.
See the Import Validation errors listed below for more details.
When the import is complete click DONE.
Spreadsheet Requirements
Column Headings
The spreadsheet requires 6 columns and we recommend using the following column heading names to avoid manual mapping during the import process.
Row Type
Item Number
Item Name
Item Detail
Parent Item Number
Parent Item Name
Row Type
Each row in the spreadsheet will have one of the following row types.
Header: used to create a business unit header
Business Unit category names: the category of business unit to map
This will reflect the category names configured under Business Unit Configuration
Row Type = Header
When adding header row types you must provide the following details under the remaining column headings.
Item Number: the business unit header number used for sorting in the tree structure
Item Name: the business unit header name used for displaying in the tree structure
Item Detail: determines the Consolidation type of this header where the supported options are [Consolidated], [Detail] or [ConsolidatedAndDetail]
Parent Item Number: enter the item number from another header row if you would like this to be a sub-header of that row
Parent Item Name: enter the item name to match the Parent Item Number
Row Type = Business Units
When the Row Type is a business unit category the remaining details represents a business unit to be mapped to the tree and therefore the following details are required under the remaining column headings
Item Number: the business unit number to match your accounting records (optional if the business unit does not contain a number)
Item Name: the business unit name to match your accounting records
Note: 💬 an exact match on both the item number and item name is required for mapping to occur during the import. Business Units that do not match exactly will be ignored.
Item Detail: (optional) comma separated list of organisations to map business units from specific organisations instead of all organisations
Parent Item Number: enter the item number from a header row to map this business unit to that header
Parent Item Name: enter the item name to match the Parent Item Number
Import Validation Errors
Before importing the Business Unit Tree, the rows of your spreadsheet will be validated based on the requirements for each row type. You will need to fix any validation errors in the spreadsheet before proceeding with the import.
Errors on validation will look like the following. If the list of errors is large you can use the DOWNLOAD LOG button to download a text document with the full list of errors.
Spreadsheet error occurred in row #
This prefix is added to all errors that relate to a specific row. The row number mentioned allows you to easily lookup this row in your spreadsheet and resolve accordingly.
Item Name required for business unit row types
The Item Name column requires a value for this row type. Please enter the business unit name to be mapped to the tree in the Item Name column.
Parent Item [Number: <number>; Name: <name>] does not exist as a header row type
When defining a Parent Item Name and Parent Item Number the details provided must match an Item Number and Item Name of a header row in the spreadsheet. This is the parent item or location that this row will be assigned to. Please ensure both the number and name match a header row type exactly.
Invalid Item Detail. [Consolidated], [Detail] or [ConsolidatedAndDetail] is required for Item Detail in header row types.
For a header row type you must define the consolidation setting. Please enter either [Consolidated], [Detail] or [ConsolidatedAndDetail] in the Item Detail column.