Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm having trouble finding a solution to providing roll-up totals in my Hierarchy tree view.
I know that I can use HierarchyBelongsTo to do this, but that does not provide a tree-view option like Hierachy does, basically, I want to combine these together.
Any thoughts?
Can you create a new table that loads AncestorName and calculates Count(Name) in the load script?
Can I use an expression or Join that calculates Count(Name) for AncestorName and links that value to the Tree-View of my PathTree?
Maybe there is something easy I'm missing? 😞
Script;
Directory; Hierarchy(Child_ID, Parent_ID, Child_Name,Department,Child_ID,PathName,"/",Depth) LOAD Parent_ID, Child_ID, Child_ID as Child_Name FROM FinanceMappingTest.xlsx (ooxml, embedded labels, table is [New Load]); BelongsTo: HierarchyBelongsTo(Child_ID, Parent_ID, Child_NameB, AncestorID, AncenstorName, DepthDiff) LOAD Parent_ID, Child_ID, Child_ID as Child_NameB FROM FinanceMappingTest.xlsx (ooxml, embedded labels, table is [New Load]); Directory; LOAD Name, Child_ID, Job, Manager, Location FROM FinanceMappingTest.xlsx (ooxml, embedded labels, table is [Person Load]);
Thank you for any assistance! 🙂
Hi All,
I've gotten this partially working by following some information posted here;
My hierarchy now correctly calculates roll-up totals used in my HierarchyBelongsTo table using this expression;
SUM(if(Child_ID=AncestorID,aggr(count(Name),AncestorID),0))
The only caveat is, when a field is selected, all other fields go to zero. Can I add something to the expression for it to ignore selections?
Thank you!
Hi All,
Found the solution, just had to add Set Expression to the calculation. Sorry for wasting your time!
SUM({1}if(Child_ID=AncestorID,aggr(count({1}Name),AncestorID),0))
Hi All,
I've gotten this partially working by following some information posted here;
My hierarchy now correctly calculates roll-up totals used in my HierarchyBelongsTo table using this expression;
SUM(if(Child_ID=AncestorID,aggr(count(Name),AncestorID),0))
The only caveat is, when a field is selected, all other fields go to zero. Can I add something to the expression for it to ignore selections?
Thank you!
Hi All,
Found the solution, just had to add Set Expression to the calculation. Sorry for wasting your time!
SUM({1}if(Child_ID=AncestorID,aggr(count({1}Name),AncestorID),0))
Are you able to provide a sample qvw of your solution.
I'm finding that making selections at higher levels done select the correct levels below.