How to Cost Cumulative Aggregation OOB Report based on Org Hierarchy without Performance Analytics?

[Sharique Azim]

[Although I was already aware of the accepted solution, my intention was to check for alternative methods. I’ve marked the question as closed to recognize and appreciate the contribution of the fellow member.]

UPDATE- I was able to make it work somehow via UI Builder and next experience, will update a blog post  someday as there are no  documents or samples available from servicenow side. However the approach is very complicated - Check the comment below the solution, to understand how it looks.

Hello All,

I’m working on a project where the goal is to build a dynamic, user‑friendly Report/Dashboard that visualizes software license allocation and cost across users, based on our organization hierarchy.

Example organization hierarchy.

 

Constraints:

• We do NOT have Performance Analytics in our instance.

• The report needs to be dynamic, not dependent on static filters.

• Out‑of‑box reporting features currently only show individual record costs — they don’t roll up the costs of all reportees under a given manager.

What I’m looking for:

• An approach to aggregate license costs cumulatively up the hierarchy (manager → their direct reports → their reports’ reports, etc.)

• A way to present this in a dashboard or visual report that updates dynamically as the hierarchy changes.

• Any examples, scripts, or configurations others have used to achieve similar roll‑ups without PA.

What I have referred so far (Recent ones are mentioned, not exhaustive list however):

1. Report based on Management Hierarchy  [Recommendations in this post]-
   💡Fixed filter upto a certain depth -❌ defeats the purpose of dynamic filter, still would only show the individuals records in report not cumulative
   💡interactive filters on dashboard -❌ would only show only the individuals records in report not cumulative
   
   
2. Report based on Org Chart/Management Hierarchy(by different author)  [Recommendations in this post]
   💡Database view-❌  still would only show the individuals records in report not cumulative / wont roll-up.
   
   
3. My custom script include to return all the sys ids of the users and make a report out of it
   Shows only the individuals records in report not cumulative
   

Has anyone implemented something like this OOB or with minimal or full customization? Any guidance, patterns be greatly appreciated.

Thanks in advance.
Sharique Azim

[sizzleMcFace]

If I understand correctly, you want a dashboard with an Interactive Filter where the choosable options are Users. When you select a user, a visualization should show the sum of the license costs of that user, and all users "under" them in the hierarchy. I assume the "license cost" is stored as a number (integer, float) on the User [sys_user] table.

By default, you need the report to:

show all users who are in-scope for your use-case.

show the SUM of the "License cost" field values. (use the Score type)

Then, you need an Interactive Filter, which is based on the User [sys_user] table and again, is filtered to only relevant users.

If you create the report on the User [sys_user] table, the filter will just filter for that one selected user and show their license cost without the hierarchy. You can solve this by creating a Custom Interactive Filter (Dynamic Content Block) which applies a custom filter condition based on the selected user. At the time of selection, it would query the Sys IDs of all users under the selected user (using a GlideAjax call), and apply "Sys ID is one of ..." filter condition to the User [sys_user] table, and therefore the report.

Another option would be to create a database view which joins the User [sys_user] table with another instance of the same table, and in the second "View table" record you specify in the "Where clause" that the 1st instance is either the same as the 2nd (usr1.sys_id = usr2.sys_id) or the "manager" or "manager.manager" and so on as many levels as you need. This would result in a database view where each user has as many rows as they have users under them +1 (themselves). You would base your report on this database view, and then a simple Interactive Filter would be mapped to this database view with usr1.sys_id (I think without scripting this is only possible with a Cascading Filter, not a Reference filter, but that should be fine).

[Sharique Azim]

Thanks for taking your time and efforts to help me here. I appreciate your response.
I am not very much aligned to dynamic block or cascading filter, thus not able to visualize in my head.
But what benefit will database view will give me? because i think it would a fixed query throughout, what i mean is the where clause cannot be updated on the go, it would create too many redundant records right?
Btw the custom table i have created, contains the user, license name, cost. Please refer the image for some idea.

 Also please let me know how to make it work otherwise.

[sizzleMcFace]

After reviewing on my PDI, unfortunately the database view solution would not work, because the "Where clause" is actually an "ON" SQL statement and the "WHERE" statement is not supported so it is not possible to dot-walk in the "Where clause", which would be necessary for "manager.manager" and so on.

This means that the only feasible solution is the custom dynamic content.

I created a minimalistic solution to illustrate, I attach the XML record here. You can add it to a Core UI dashboard from the "Content Block" widget category. It can't be added to Platform Analytics dashboards, but it can be migrated along with Core UI dashboards.

If you set up the report on the User [sys_user] table so that it sums up the "License cost" field's value, then when you select a user, you will see the sum of license costs from that user and their hierarchy under them.

The ScriptInclude function called with GlideAjax should be a simple GR query that takes the selected user's Sys ID and returns a comma separated list of Sys IDs of users under their hierarchy.

[Sharique Azim]

Looks great and definitely a good approach @sizzleMcFace , but it just solves one problem. I wonder how to use it to generate the charts that is the actual goal of the requirement.