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

[Patrick Mayrhofer]

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

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? 

[Katerina Kovriga _Stiltsoft_]

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.

[Patrick Mayrhofer]

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? 

[Katerina Kovriga _Stiltsoft_]

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:

And give it a unique name:

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

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

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.

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.

[Katerina Kovriga _Stiltsoft_]

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

[Patrick Mayrhofer]

@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) 

[Katerina Kovriga _Stiltsoft_]

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.

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:

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'

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

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'

[Patrick Mayrhofer]

Thank you!