How to dynamically determine parent status from child pages

Question

I am managing multiple products for a business unit with multiple solutions.  Each solution has multiple projects.  Each project has multiple sprints.  So this is a 3 level parent-child hierarchy where the sprint pages rollup to the project page, and the project pages rollup to the solution page.  Each sprint has a status.  I am using the Page Properties and Page Properties Report macros to display multiple sprint statuses on the project page.

What I want to do is to derive a single status for the project page that is based on the statuses of the child sprints.  I've included an example where one of 4 sprints is in "Jeopardy".  I want to derive a single status for the project page which is "Jeopardy" if any of the sprints are in jeopardy.  If no sprint is in jeopardy, but a project is labeled for "Discussion", then I want the project status to be "Discussion", etc.

In the attached example, I have to set the project status manually.  I want to be able to determine the project status dynamically.  Are there any options that would allow me to do this?

Stiltsoft support · Answer

In general the output of the macro is a table but if you change the query as

SELECT 'Status'
FROM T*
WHERE 'Status' LIKE "%JEOPARDY%"
GROUP BY 'Status'

and go to the Options tab and choose to show the result as plain text, you will be shown just status without any borders:

So, you may try to collect these statuses in your 2nd level table and then use another Table Transformer macro to leave the "Jeopardy" statuses as they are and override your empty cells with the new statuses based on the contents of other columns (containing labels, for example).

Here is an example from our documentation (please navigate to the "Conditional inserting of statuses" section).

Maybe it is not an elegant solution but the case itself is rather overloaded and the standard macros won't do any good here.

If you are stuck, you may refer to our support: the portal is confidential, so attach the full screenshots of the tables belonging to the each level, we'll try to recreate them and help to get the most close result to the desired outcome.

David Cuozzo_wb8qvh · Answer

Thank you for the suggestion.  Does the output of the macro need to be a table or can it be a single value?  Also, we have multiple different exception statuses.  How could I configure the query to resolve a condition and then return the desired status as specified in my original question: " I want to derive a single status for the project page which is "Jeopardy" if any of the sprints are in jeopardy.  If no sprint is in jeopardy, but a project is labeled for "Discussion", then I want the project status to be "Discussion", etc.".  Thank you.

Katerina Rudkovskaya _Stiltsoft_ · Answer

Hi @David Cuozzo,

If you have the Table Filter and Charts for Confluence app (the add-on is developed by our company) installed for your instance, you may try to play with the Table Transformer macro – it will simplify your manual work.

SELECT "Project A" AS 'Project Name', 'Status'
FROM T*
WHERE 'Status' LIKE "%JEOPARDY%"
GROUP BY 'Status'

For example, here we automatically check the table with sprints if they have “JEOPARDY” statuses.