We've seen quite a few requests for a Report on Business Capabilities in which the Capabilities are pivoted out such that the first column(s) is a root or level 0 capability, the next column level 1 and so forth. A Database view can be used for this (be sure to clear things with Admins) with some particular caveats.
First, you must have a consistem hierarchy, meaning you cannot have 3 levels in one tree and 6 in another. It should be consistently 3, 4 or 5 levels in all trees. Anything past 4 or 5, i'd worry about performance.
Next, Business Applications must be associated at the leaf levels. You might be able to fiddle around with left joins to pick up business apps associated to middle levels but the pivot will be trashed.
Here is an example output:
Hard to see, i know. the PNG is attached.
Here is a screen shot of the DB View config, it's pretty easy, start with level 0 (use the Business Capability.hierarchy_level attribute) then attach each level by by child Parent attribute == parent Sys_id
EX.: (i use bc or bc1, bc2 etc) bc2_parent=bc_sys_id
the final part is associate cmdb_rel_ci using a left join (you have to add this attribute to the list or the form) and then also Business Application with a left join as well. On the cmdb_rel_ci table, th eparent attribute is set to the leaf level capability. the Business Application is matched to the child on rel_ci.
rl_parent = bc4_sys_id
ba_sys_id = rl_child
As always, this is for educational purpose. Always, always confirm with your Platform team and admins that using anything like this DB View is acceptable and then use proper procedure to implement and test.
Finally, you can set the column names by adding the appropriate entries in the sys_documentation table.
for each column, create a new Field Label
Hope this helps,
mark
