OTBI demystified Series: how to create your financial statement as an infolet

Every organization has its own chart of account with its own hierarchy trees that suit their business operations , enterprise structure and financial reporting requirement that is why Oracle has never been able to deliver out of the box financial statements that suits every organization.

Though we are now in the cloud era building financial statements is not an easy process that still requires super user skillset to build reports through Hyperion financial studio.

there is no major difference between EBS's financial reporting generator (FSG) and hyperion's Financial reporting studio except that latter is based on essbase cube that simplify aggregating the balances for each chart of account dimension.

now with the help of Oracle business intelligence I managed to create infolets as depicted below that can be easily deployed to any environment with minimal configuration steps required.

while the OTBI catalog described in this document does not require to be created as it can be downloaded and deployed to any environment i have documented the steps to create it for education purpose.

1- Create analysis based on GL balance subject area: using the following columns and filters

using the following filter

the primary analysis will look as depicted in the table below:

The challenge is that accounts are not grouped as per the financial statement representation requirement e.g. assets , liabilities etc.

The Solution for that is to use CASE WHEN to select range of accounts that belongs to specific group, for example if all children accounts that are attributed as liabilities fall under the range between 21200 and 26900 as depicted below :

Then i should be selecting accounts that are between 21200 and 26900 & grouping them in a column with custom label 'Liabilities'

similarly i should repeat that for assets and owners equity to eventually have the following analysis summarized:

furthermore i have additional requirement to break down the liabilities to Payables , Debts and Accrued assets each of which has its own range of accounts and for that i will use the same formula with the appropriate account range and custom heading:

For Example : Liability Formula

For Example : Accrued Expenses Formula

For Example : Debt Formula

until I eventually have my final analysis grouped as per my balance sheet representation requirement as showing below:

Now I can build new infolet using Pie chart by simply dragging the respective columns into the measure area

you should repeat that for assets and owners equity to eventually have the following outcome

For inquiries and final catalog please feel free to reach out to me.