Confluence Table built from parameterized Jira Issue Macros

[Jim Solomon]

Hi, I'd like to create a high-level roadmap view in the form of a table in Confluence where:

• Each row of the table is a swimlane (I can pull this from a picklist/enum from each issue in Jira)
• Each column in the table is an estimated quarter in which it will ship (I can pull/derive this from a different field from each issue in Jira)

But, since there are lots of rows and columns, is there any way for me to automate the creation of this table, other than the straightforward way of creating the table, and then populating each cell of the table with a unique Jira Issue macro, each with its corresponding unique JQL string to pull the right things for that row/column, and rinsing and repeating this Row x Column times?

Can't help but wonder if there's some really cool thing I'm missing that would allow me to parameterize the JQL query for that Row/Column and blow out that whole table without doing nearly that much work (and maintenance!).

Anyone?

[Katerina Rudkovskaya [Stiltsoft] · Accepted solution]

Hi @Jim Solomon,

Not sure if that is exactly your case, but maybe our Table Filter and Charts for Confluence app will help.If you use the Jira Issues macro with the most general JQL (for example, all the issues for one or several projects), you may use our Pivot Table macro to group (aggregate) this dynamic table. The result pivot will be like a dashboard: all the changes from your Jira will be passed to Confluence through the Jira Issues macro and the Pivot Table macro will catch them automatically.

[Jim Solomon]

Thanks @Katerina Rudkovskaya [Stiltsoft]. Not sure I'm seeing the path forward.

I do follow your first point, which is to make a table with ALL of the issues using general JQL. As an example, that would give me something like this (simplifying):

and then through the magic of Pivots or other Macro wizardry, I would like to display it like this:

Is that possible? 

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Jim Solomon,

Yes, smth like this is definitely possible, please check the example below:

Instead of the manually created table that I've taken for my example, you should put the Jira Issues macro inside the Pivot Table macro (our macros support dynamic Confluence tables such as Jira Issues, Page Properties Report, Content Report Table).

[Jim Solomon]

Awesome, thank you!

[Jim Solomon]

One more question if you don't mind. Can you think of a way to "insert" columns to the left of the Q1 Q2 Q3 columns showing additional Projects based on Status, instead of based on Quarter/Date?  

E.g., if the table looked like this:

Project  Status     Quarter     Swimlane
   A     Shipped   (ignored)       S1
   B     Building  (ignored)       S1
   C     (ignored)    Q1           S1
   D     (ignored)    Q1           S1

would it be possible to have the pivot table show something like

Swimlane   Shipped  Building  Q1   Q2  Q3  [...]
   S1         A         B     C
                              D

and similarly, would it be possible to aggregate any Quarter greater than four quarters away to a catch-all "Later" column to the right?

Would the way to do this be to build a couple of different tables and Join them? I suppose I could also derive a field from existing fields and do a simple table based on that derived field.

Final question: is there a way to hide the v Literal of Project label/control?

[Stiltsoft support]

Hi @Jim Solomon ,

Here is my simple example with the 'Status' column and 4 quarters in the 'Quarter' column where I rename columns with the help of the Table Transformer macro:

SELECT 'Swimlane',
COALESCE('Literal of Project New ‎', "") AS 'Status New',
COALESCE('Literal of Project Old ‎', "") AS 'Status Old',
COALESCE('Literal of Project ‎ Q1', "") AS 'Q1',
COALESCE('Literal of Project ‎ Q2', "") AS 'Q2',
COALESCE('Literal of Project ‎ Q3', "") AS 'Q3',
COALESCE('Literal of Project ‎ Q4', "") AS 'Q4'
FROM T*

If you have Q5, Q7, etc. quarters in your original table and you want to collect them under the label "Later", for example, you may use another Table Transformer macro between the source table and the Pivot Table macro.

In this Table Transformer macro you will use the "CASE WHEN" statement and change specific quarters to the label "Later" for the original "Quarter" column. Please check here for an example.

As you can see, our macros are rather flexible. So, if you are stuck with smth, please refer to our support: attach a screenshot of your original table and describe what you need to achieve. The portal is confidential and we will guide you through all the steps there.

P.S. The first screenshot was taken for Data Center, if you are on Cloud, you should wrap macros via the Table Toolbox macro. Other steps/queries are the same.

[Jim Solomon]

Thanks so much @Stiltsoft support. (Hopefully) final question -- can the output of PivotTable be put into a Tab macro? I've tried putting it into Tab within a Tab Group, but can't seem to do so. Also tried making it a child page and then using Import within a Tab on the parent page, but that's not working either. Thoughts?

[Stiltsoft support]

Hi @Jim Solomon ,

If your tags are correct and you are on Cloud, then it seems to be an expected behavior for its editor: Confluence Cloud doesn't allow macro nesting, you can't wrap macros inside each other.

For example, in the previous reply I've mentioned the Table Toolbox macro - it's a special shell to nest several macros coming with the Table Filter and Charts app on Cloud. Technically we have the source table that is wrapped in the Table Toolbox macro (one macro, no nesting) but inside its shell we can create macro sequences. And it is not possible to wrap the same macros directly on the page.

[Juliette Lallement _Elements Apps_]

@Jim Solomon 

Have you taken a look into the native Jira roadmap macro in Confluence ? It allows you to display your Jira roadmap inside Confluence and interact with it. 

The good thing about it is that it will keep in sync with your Jira instance so you're sure any data displayed in Confluence is up-to-date.

Hope it helps

[Jim Solomon]

Thanks for the suggestion @Juliette Lallement _Elements Apps_. I've played around with Jira Roadmaps and it's a bit too "Gantt-chart-ish" for my purposes. I want a simple rollup of what's shipping by quarter across multiple swimlanes (projects) with higher information density than Jira Roadmaps provides. May have missed some tunability of it though.