Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How can I use the output of a pivot table as a page property label

I would like to use the Output of the pivot table below as a label for the page properties: 

image.png

Then use this label to display the content (2h) in a report from multiple pages. 

It works fine if I manually write a table but not with the pivot labels.

I have tried both, "Total" and "Sum of Time Spent". 

Any ideas? 

 

image.png

1 answer

Hi @Patrick Mayrhofer ,

As I understand, you are trying to wrap your pivot tables into the Page Properties macro and collect a report with the help of the Page Properties Report macro. And the problem is that Page Properties doesn't recognize the Pivot Table macro.

We suggest using our native macros - Table Excerpt and Table Excerpt Include macros. They work similar to the Page Properties Report macro but have more advanced features.

Hi @Katerina Kovriga _Stiltsoft_ , 

Thank you for your answer. 

Yes, I think you have summarized correctly. I have multiple Project Summary Pages, each one has Page properties for Project Status fields, project trafic lights, next steps and also sum of time spent last week. Selected page property fields from all our project summary pages are then displayed on a seperate Page via the page properites report. Only the sum of time spent field is a pivot table. I can put the whole pivot table in a page property field which looks ugly but I cannot display just the one value I need. 

Since it is already setup this way it would be much easier for me to use the page properties. 

If that is not possible at all is there an example for the table excerpt achieving something similar? 

The Table Excerpt/Table Excerpt Include macros are pretty similar to the Page Properties/Page Properties Report macros but allow you to collect multiple-row tables.

Wrap your Pivot Table macro into the Table Excerpt macro:

Fri 4-1.png

And give it a unique name:

Fri 4-2.png

Then collect your Excerpts with the help of the Table Excerpt Include macro:

Fri 4-3.png

Fri 4-4.png

The last step is to merge your tables with the help of the Table Transformer macro:

Fri 4-5.png

Fri 4-6.png

You may also check the field "Show page titles of included tables" into the Table Excerpt Include macro settings and you'll get an additional column with a link to the page with the source table.

Fri 4-7.png

This function has been working perfectly well for manually created tables and from the 7.3.6 version was introduced for Pivot Tables - please update your app.

If you have any specific questions, I can suggest raising a support ticket, where you'll be able to attach screenshots. 

@Katerina Kovriga _Stiltsoft_ 

Thanks a lot for the helpful information. 

Is it also possible to use a table generated by the jira Issue/Filter Macro with the table transformer macro? 

Specifically, I have a table with two columns 'Property' and 'Value' as well as a second table from the jira issue macro with two columns 'Key' and 'Time Spent'.

I would like to replace an empty cell in the first table T1.'Value' by the cumulative sum of the column 'time spent' in the second table 

I have tried it like this but it does not work:

SELECT *,
CASE
WHEN T1.'Value' IS NULL THEN T2.'Sum of Time Spent'
ELSE T1.'Value'
END AS 'Value'
FROM T*

(Error: TypeError: Cannot read property 'Sum of Time Spent' of undefined) 

Yes, it is possible to use the Jira Issues macro with the Table Transformer macro and here is one of the examples from the Community.

What concerns your case, I've recreated the two tables manually but it doesn't matter if the second table is generated by the Jira Issues macro or manually created.

Fri 6-1.png

The main thing is that you'll need to match your tables by something unique. For example, it can be your 'Property' name in the first table and 'Key' in the second. Because you need to know which exact value from the second table should be inserted into the first table.

Now let's work with the Transformer 1 and count running totals:

Fri 6-2.png

SELECT 'Key',
SUM (TT2.'Time Spent') AS 'Cumulative Time Spent'
FROM T1 AS TT1
JOIN T1 AS TT2 on TT1.'Key' >= TT2.'Key'
GROUP BY TT1.'Key'
ORDER BY TT1.'Key'

Fri 6-3.png

And now let's replace the empty 'Value' cells with the modified contents of the second table (we are working with the Transformer 2):

Fri 6-4.png

SELECT T1.'Property',
CASE
WHEN T1.'Value' IS NULL
THEN T2.'Cumulative Time Spent'
ELSE T1.'Value'
END
AS 'Updated Value'
FROM T1 LEFT JOIN T2 ON T1.'Property' = T2.'Key'

Fri 6-5.png

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Miss the Confluence demos at Team 21? Watch them here!

Phew, Atlassian Team 2021 blew by. With dozens of demos and a handful of keynotes, you may have missed some of the awesome Confluence Cloud sessions. Don't' worry, you can watch them all here or on Y...

312 views 2 6
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you