I load the hierarchy for internal order from ERP into BI in 0COORDER InfoObject.
But the requirement I have is to show the Hierarchy as a flat structure and not as a drill down.
For e.g.: the hierarchy is: Company ¬ Business Unit ¬ Area ¬ Region ¬ Sector ¬ Internal Order.
When I use the hierarchy display in the query for 0COORDER, it will obviously display it as a drilldown. But I need it to display as a normal flat structure.
One solution that I can think of is to create a DSO to maintain this hierarchy which will feed of 0COORDER. But is this possible? How can I extract the Hierarchy data (from table: /BI0/HCOORDER) to load into a DSO?
I've created a Generic DataSource on the H table where the Hierarchy is stored in BI. I then wrote an Expert Transformation routine from the DataSource to the DSO which will convert the Hierarchy data into flat structure.
There was an issue with this approach though. The expert routine will only act on the data in each data package. If the higher level hierachy details are in a different package then the hierarchy structure will not be built correctly. To overcome this, there should be a temporary table in the expert routine which again reads the HIERNODE from the H table.
A slightly better approach would've been to write a Function Module on the H table which converts the data into flat structure and then create a Generic DataSource on this Function Module. The transformation will then be a simple one-to-one mapping.