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

[Sharique Azim]

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.

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.