ArticlesBite Size Analytics: How to Sort in Trial Balance Levels

• Spring 2023 version and onwards

To order by your chart of accounts, when you create your analytics app, we store the picklist order of the trial balances on the general ledger account records. If you decide to have your administrator re-order the picklist values then you will need to reconfigure your analytics app to rebuild. The order is stored in a field within both the Financial Transactions and Financial Balances datasets. If you are not familiar with these datasets, Financial Transactions contains full transaction line information and Financial Balances contains all of that plus any budgets or forecasts from the reporting balances object so you'll need to filter by the 'balance type' field with this dataset.

The fields we store the value in are called TB1 Order, TB2 Order, TB3 Order and TB4 Order. These are measures so when adding you need to use the maximum function otherwise numbers get multiplied.

You can then order by this field to order based on the picklist order and hide the column since you don't want it making your report look poor. This can even be consumed by our Financial Report Builder package.

So there you have it, a way to order fields by picklist values. The main thing to remember is if you want to change the order you will need to reconfigure your app so that the new picklist order can be consumed.