Report builder - recursive query?
hi folks
We've been presented with the request to provide a report builder report
that shows the hierarchy of workitems associated with a given workitem.
So, P = starting point, c.x = children @level
P
-c1
--c1.1
---c1.1.1
there may be a fairly deep structure.
Ideas?
Assume we're reporting against the data warehouse, where the queries are in SQL.
I know we can do this in RPE using recursion, but then we don't have graph/charting.
thanks!
Steve
2 answers
Hey Steve!!!!
I've had to do this before and the only way really is to build the query with the maximum level of nesting you think you're going to encounter.
If the Parent WI type is not unique then this adds another wrinkle because you then have to add a condition on the top level that selects only the ones that have no parent link, otherwise you'll end up pulling children into the mix as well.
I've also previously added a custom field that concatenates the entire tree of WI numbers into a single value that can be sorted so that you get the hierarchy shown one under the other in some vague order.