It's all about the answers!

Ask a question

Report builder - recursive query?


Steven Hovater (10920) | asked Sep 29 '23, 8:11 a.m.

 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



permanent link
Davyd Norris (2.1k217) | answered Sep 30 '23, 11:32 p.m.
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.

permanent link
Agnieszka Zawislak (213) | answered Oct 02 '23, 5:20 a.m.

 Hi Davyd, 

can you share any example of SQL recursive query that can be used in Report Builder?

Thanks, 
Agnieszka

Your answer


Register or to post your answer.