When doing implementation we often struggle to produce Financial Report with complex multi-dimensional accounting layouts. One of the case is when we would like to breakdown an account by Cost Centers or by Sales Regions. In traditional accounting, we used to find a structure of Chart of Account which composed of many duplicated accounts, for example one set of travel expense accounts for General activity and another set of the same travel expense accounts for Sales activity. With multi-dimensional feature that Adempiere has, we often suggest our clients to get rid of those duplicated accounts and tell them that later on they can break them down by any other dimensions they want.
Sample report breakdown by Dept dimension
Well, not that easy. When it comes to Financial Report, we get hit by the wall that it is so difficult to prepare such a layout using the existing functionality. There is Combination feature in Report Source tab introduced sometimes ago but it is also not offering an easy way we thought. You can use that feature if the dimension only contains small data but what if you have hundreds of records, such as “tell me how much is the Revenue breakdown by products”.
Team Goodwill have tried to improve the current Report Source tab to allow combining multiple report sources in one Report Line using the And/Or condition. Future development would include support for condition in parentheses. For example you can combine two report sources, Account “AND” Business Partner, to produce a Receivables detail report showing the balance for each customer.
Just take a look at the sample report above. The sample #1 is a Report Line using single report source, which is only Account type. By choosing a summary account, it can then expand all its child accounts.
The sample #2 is a Report Line using two report source, one is Account type, and the other is Activity. The Activity is purposely left empty because we want to show all. The system would always expand the details of the report source with the highest Sequence Number, in this case the Activity.
Travel Expense – ALL DEPT (breakdown by Dept)
Here, we used Activity dimension as “Departments”.
The Tree of Activity as Departments
Ok, let’s take a look another examples. In sample #5, we show Travel Expense and filtered by General departments only.
Sample Report Filtered and Breakdown
This is how we do it. We add two report sources like above. But this time we enter “General” activity. In this sample, when the Sequence number is the same, the system would expand the details of the last report source we entered. And since “General” is a summary level activity, system will show all its children.
Travel Expense – General only (breakdown by Dept)
In sample #6, thing is getting serious. We want to show Travel Expense, filtered by Sales dept and breakdown by Sales Region. Here how we do it.
Travel Expense – Sales Dept (breakdown by Sales Region)
That’s it folks. We would like hearing your feedback on how to improve this functionality. Let’s make Adempiere great again 🙂