When creating a workbook for a balance sheet or another transaction query, in order to show the balances for the Chart of accounts in a hierarchical manner, try selecting the multi-column hierarchy from the options of Account.
In the option of showing Subtotals and grand totals, select ‘Show Hierarchy Sub-totals’