It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Follow this question
Question details

×42,807
×11,943
×1,520
×1,038
×408

question asked: Sep 29 '23, 8:11 a.m.
question was seen: 674 times
last updated: Oct 02 '23, 5:20 a.m.

Related questions

FAQ - How this Forum works

Report builder - recursive query?

1
Steven Hovater (109●23) | 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

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
Davyd Norris (2.3k●2●17) | 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.
0
permanent link
Agnieszka Zawislak (21●4) | 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.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.