Loading
HelpCommunityTraining Center

ArticlesBite Size Analytics: How to Sort in Trial Balance Levels

Knowledge Article

Bite Size Analytics: How to Sort in Trial Balance Levels

Custom lenses in Analytics display trial balance columns in alphabetical order by default. It is possible to reorder these columns based on the chart of accounts' picklist order. This is achieved by using the TB Order fields within the Financial Transactions and Financial Balances datasets. Should the picklist order be changed, the analytics app must be reconfigured to reflect the new order. The TB Order column can then be used for sorting and subsequently hidden from the final report.

  • Apr 4, 2024
  • Knowledge

Title
Bite Size Analytics: How to Sort in Trial Balance Levels
URL Name
Bite-Size-Analytics-How-to-Sort-in-Trial-Balance-Levels
Summary
Custom lenses in Analytics display trial balance columns in alphabetical order by default. It is possible to reorder these columns based on the chart of accounts' picklist order. This is achieved by using the TB Order fields within the Financial Transactions and Financial Balances datasets. Should the picklist order be changed, the analytics app must be reconfigured to reflect the new order. The TB Order column can then be used for sorting and subsequently hidden from the final report.

Objective
When it comes to Analytics it is expected that you'd want to build some custom lens for reporting purposes (see bytes). However, when it comes to Finance based reports, you sometimes want to see the trial balances displayed in a different order. By default, any lens you build will be in alphabetical order but you can order the columns if needed in alphabetically ascending or descending order.

Screenshot 2021-05-07 at 15.37.37.png
Product Area
Financial Management
Release Version
Environment
  • Spring 2023 version and onwards
Procedure

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.

Additional Notes
For any feedback or requests for future Bite Size Analytics, post in the Reporting Chatter group.
Reporting & AnalyticsProfessional Services CloudCustomer Success CloudBuilding ReportsBite Size AnalyticsFinancial Management Cloud
Was this article helpful?
00
Upload Files
Recommendations
No results for undefined
Loading
Knowledge:Bite Size Analytics: How to Sort in Trial Balance Levels