I am building an interactive report over the table ItemBalances. One of the fields is InventoryOrgUnit. I would like to view this report with the hierarchy that I built in Company Structure so that I can group the report by Org Unit, and be able to expand the rows to see each level of the Company Structure. Is there a way for me to achieve this?
I’ll refer to @roby or @jeremy.keller regarding the functionality within Interactive Reports.
One way to work around this issue is to find or create a Join table between ItemBalances and CompanyStructureFlat (joining the ItemBalances_InventoryOrgUnit to the CompanyStructureFlat_OrganizationalUnit), then use the OrganizationalUnit in tandem with hierarchy level. With this information, you can create associated Group Rows to display one Org Unit under another.
[1]
Note: be careful with how you filter if using this method. Using the AssociatedOrgUnit field is dangerous in this case because the Join will produce duplicated rows, so the CompanyStructureFlat table is useful for its hierarchy depth and not its relationship information. ↩︎
This sounds like potentially a use case for the “Hierarchies” feature in Interactive Reports. You can create a Hierarchy Definition (view the “Configure hierarchies for interactive reports” help text page). Then, you can assign that hierarchy to your row grouping. This should let you expand to see the structure of your heirarchy in a single field in the Interactive Report.
2 Likes