Q: How to keep icons and link formatting when grouping cells in a table?

[Jimmy Petersson]

Hi,

My objective is to use Confluence as a live dashboard for reports, and get away from copy/paste data in to Powerpoint status reports. These reports requires selecting fields from different levels of the hierarchy and presenting them in one common view, e g a table with Epics to the left and their Stories to the right.   

For one-to-one relations, Confluence has some great capabilities. Jira Issue/Filter macro combined with Table Transformer macro enables us to create a row that maintains the issue type icons, issue key/summery URIs and status icons. In below picture shows how this formatting is maintained keeping Jira look-and-feel, with directly selectable links. 

However, in our data model we have a lot of objects with one-to-many relationships, e g Epic with multiple Stories. Dashboard therefore need to have one Epic to the left and multiple stories to the right (w/ type icon, key URI and status fields) in the same row.

I accomplish this using the Table Transformer macro by grouping on column 'Epic.Key'. But the formatting is lost, leaving me with only text.

Above table created in Table Transformer macro using:

SELECT 
   Tbl.'Epic.Key',

   /* Place individual values in same cell delimited by new-line */
   FORMATWIKI(SUM(Tbl.'Story.Type' + "\n")) AS 'Type',
   FORMATWIKI(SUM(Tbl.'Story.Key' + "\n")) AS 'Story.Key',
   FORMATWIKI(SUM(Tbl.'Story.Status' + "\n")) AS 'Story.Status'

FROM 
  T1 AS Tbl

GROUP BY 
  Tbl.'Epic.Key'

This leaves me with the question: how can my objective be achieved whilst maintaining the URI, type icon and status icon formatting? Maybe going the Table Transformer route is a dead-end, and there are other ways to do this? I am putting my faith to the community and that someone smarter that I have a solution :-)

Remark #1

I tried using Pivot Table macro. But it only returns unique values. In above example the 'type' column would only have one 'Story' value, and the 'Story.Status' column only one 'Done' value. Thus, I could not use it as the dashboard should show fields per story.

Remark #2

This is a pretty basic use-case and my expectation is that Confluence will have the capabilities to handle it without adding on plugins that costs 20 KUSD annually.

Best Regards

//Jimmy

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Jimmy Petersson ,

You may use the Pivot Table macro for this case:

Put the story Key column with unique values as the second one to group your table:

Now go to the Table Filter macro and hide the last column:

The result table can even still be filtered through the Filtration panel view (Filtration tab -> Filters' display mode):

 Hope this may help.

[Jimmy Petersson]

Thanks for a very detailed answer, @Katerina Kovriga _Stiltsoft_ !

Using Table Filter to remove a column was a very clever trick.

I used your examples and managed to create a pivot table that keeps the formatting!

I even managed to make a nested pivot table aggregating all the epics:

So conceptually your solution works perfectly!

However, as I experimented with adding more fields in the pivot performance tanks to the level where my Confluence page becomes unresponsive and hangs. Next step is to apply this design pattern to a larger dataset to assess how feasible the method is for my context.

In closing, thanks a million for sharing your design pattern. It is by far the closest I have come to achieving my idea for nested, one-to-many relationship dashboards. 

Best Regards

//Jimmy

[Katerina Kovriga _Stiltsoft_]

We have a pattern how to preserve original formatting inside cells - you may check here for an example (change pluses to commas while applying new colors or smth).

But for your case the problem is that you merge several cells into one big cell, so it is not the original cell anymore. And the Table Transformer macro sees tables as mini SQL databases.

We've created a ticket in our backlog and added the link to this question - if the feature is implemented, we'll reach back here.

[Jimmy Petersson]

Thanks for the link. I didn't notice the Stilsoft part of your nice initially. Really appreciate you supporting users like this. The linked document contains a few nuggets for me to explore.

Using Table Transformer allows for merging values in to the same cell and with line-break as delimiter. This would be the preferred way of doing one-to-many relationships in tables. It works great for nested operations as it maintains the columns and rows of the higher level issue type (thus avoiding the big cell issue you pointed out).

My vision is to be able to get all relevant data points in to Jira, and use Confluence to generate real-time reports. During our journey we have realised that consolidating the data in to Jira only gets us half way. We need a presentation layer that can handle one-to-many relationships, that preserves the links and icons from Jira. Or in other words, a helicopter view with ability to click town to explore lower hierarchy data. 

Thanks again Katerina!

//Jimmy

[Katerina Kovriga _Stiltsoft_]

Thank you for reaching out!

Glad that I was able to help (partly, of course, but still).