mcastoe
ServiceNow Employee
ServiceNow Employee

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:

mcastoe_0-1741610170554.png

 

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 

mcastoe_1-1741610500166.png


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.  

mcastoe_0-1741611553415.png

for each column, create a new Field Label

mcastoe_1-1741611604385.png

 


Hope this helps,

mark

 

 

 

Version history
Last update:
‎03-10-2025 06:00 AM
Updated by:
Contributors