Set up and import a trial balance containing dimensions

You can assign dimensions to accounts based on spreadsheet columns in a CSV or Excel file. This provides a streamlined approach to assigning multiple dimensions as part of an Excel/CSV import flow, rather than having to manually assign them.

To assign dimensions to accounts:

  1. In the trial balance spreadsheet:

  • Make sure there is a column to hold the financial group number for each account. The financial group must match one of the financial groups in the financial grouping structure defined for the product template.

  • Where there is a single dimension category and multiple dimensions, create a single column for the dimension category whose values will list the dimension numbers. In our example, the Location column represents the Location dimension category. Its values are the unique dimension ID numbers that will also be defined in the Engagement Settings under Features | Dimensions.

  • Where there are multiple dimension categories, create a column for each dimension category. It is recommended that your column headers match the dimension category name. Its The header of the column should be the dimension category, and i values should be the unique dimension IDs in that category.

  1. Enter an engagement and set up the dimension structure on the Engagement Settings dialog, where there will be at least 1 dimension category with multiple related dimensions.

  2. Select the Information icon () at the top menu, then choose Settings. The Engagement Settings dialog opens.

  3. Go to Features | Dimensions.

  4. Select + Dimension Category, then add a name for the new dimension category.

  5. Select + Add dimension to add a dimension for the new category.

  6. Enter a name for the new dimension.

  7. Select View numbers to see unique dimension numbers that are autogenerated for each dimension. In Number, users can change the unique ID for each of the dimensions, instead of using the autogenerated ID. For example, users may want to change the ID to 100/10/200/23/ or to some other combination of letters and numbers.

  1. Once dimension categories and their related dimensions have been created, assign each dimension category to its respective financial groups in the dimension setup, within Engagement Settings:

  1. Select More actions () | Assign Groups.

  1. If your product has multiple financial grouping structures, select the grouping structure you want from the dropdown.

  2. Select all the applicable financial groups.

  3. You can expand and collapse financial groups to view subgroups and use the search bar to find the appropriate groups.

Note: The exact dimension numbers listed in Number must match the dimension numbers already defined within the trial balance data.

Note: You can only assign an account to one dimension within a category. To assign an account to multiple dimensions, each dimension must fall under a different category.

Import the trial balance

  1. Go to the Data page. The Data page opens to the Datasets tab.

    • If you’ve previously imported data, select Add data.

    • If your engagement is consolidated and has internal subsidiary entities, select the entity for which you want to import data from the list.

  2. Under DATA FILE UPLOAD, browse or drag the CSV or Excel file to the file upload area. If the file has already been uploaded to the Documents tab, select Import from Documents, then select it from the list of available documents in the engagement.

  3. In the Configure new dataset dialog, select Trial balance from the Dataset type dropdown.

  4. If planning balances are enabled in your product, select whether you are importing the Final trial balance or a Planning trial balance.

  5. Review the automatically selected header row to ensure the correct header has been selected. If you want to manually select the header row, select Adjust header row and select the correct row.

  6. If you are importing a CSV file and want to change the delimiter to something other than the default comma (,), select Adjust header row and select the correct delimiter.

Note: Both the automatic header detection and manual header selection only take into account the first 30 rows of the imported file. Rows beyond the first 30 rows cannot be selected as a header.

  1. Select Upload.

  2. On the Datasets tab, click Configure to assign columns in the imported data to Caseware fields.

  3. Assign the column containing the financial group number to the Financial Group Number field.

  4. On the Assign Fields page, assign the column containing the dimension number to Dimension. You are prompted to then select the dimension category.

    In the example we have been using, the Location column in the CSV file containing the trial balance is assigned to the Dimension field.

    You are then prompted to select the dimension category. In this example, this is Location.

  5. Select Import.