Sort Page Properties Report by status

Micha Roon January 30, 2020

I have a Page Properties Report in my page and the pages contain a Status field. I'm trying to sort by that but it does not work

Here is my configuration

Screenshot 2020-01-30 at 15.37.51.png

3 answers

1 accepted

1 vote
Answer accepted
Diego
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
January 31, 2020

Hello @Micha Roon !

As I understand, you need to sort the order of items within your Page Properties Report macro based on the contents of the Status column.

The problem we are facing here is that we are trying to order a macro, which is not a “raw” value for the editor or the Page Properties Report Macro.

I tried to order the values based solely on the macro but was also unable to. My workaround was to add a value right beside the Status Macros of my pages with the Page Properties macros. Like this:

value00.jpg

When I did that, all pages were ordered as expected. Here is how it looks like:

 

value01.jpg

 

I hope this helps you out, Micha! Looking forward to your reply.

Micha Roon February 4, 2020

I also thought of this and it works, but it's a bit ugly. I guess this feature is missing.

Kelly Pelkie August 26, 2021

hi @Diego I'd like to follow on to this, if I may. My team is similarly using macros and we'd like to sort too. We have tried to put in a leading number to get the sorting to work instead of a letter. I've tried a few different forms/formats for this to no avail. 

Here's what I'm getting sorting on "Numerical" via the macro Sort settings - no idea what it's doing (sorting on the alpha num and priority fields yield similarly illogical results):
numerical sort.PNG

The more annoying part is, somewhere in there, it knows how to sort this data by the status macro label value. If I manually click on any of the column headings for Alpha Num, Numerical, or Priority, data is ordered the way that I would expect:sort by heading.PNG

How can I configure my Page Properties report Sort settings to achieve this? I've tried all manner of adding/excluding asc/desc to the Sort parameter, in conjunction (and not) with the "Reverse Sort" option - nothing works. Any ideas?

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 27, 2021

Hi @Kelly Pelkie 

If you look back at the screenshots, you'll see the difference: your numbers are included into your statuses. But for his example Diego put also plain text before the Status macro.

Not "[1-High]", but "1[1-High]"  

Kelly Pelkie August 30, 2021

Hi @Katerina Kovriga {Stiltsoft} 
I see that - thanks for pointing that out. What do you make of the manual column sorting getting those labels into the order I'd expect without needing to do anything additional to the data in those fields? Any plans to expose whatever metadata option it's reading from to users so that we could set that same sort parameter by default?
The status macro is handy and useful for visually grouping items, but that depends on the ability to sort as one would expect.

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 30, 2021

I can suggest trying our app - Table Filter and Charts for Confluence (it is a paid add-on). Maybe you've already been using it for your Confluence - it works with the Page Properties Report macro perfectly well.

You'll be able to filter your report by statuses (like in Excel) and sort them with the help of the Table Filter macro.

Here are even more dashboards based on the app and the Page Properties Report macro.

Like # people like this
Kelly Pelkie August 31, 2021

Thanks for the suggestion for that add on. I'll have to see if that's something we could get. I appreciate the examples too!

3 votes
Torkil Johnsen
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 5, 2022

One way of solving this, with vanilla Confluence cloud and no extra applications, and without "hacking" with adding numbers to help the sorting by having numbers in front of the status labels, or sorting numbers in a separate column:

  1. Add a Table Transformer macro (or a Table Toolbox macro with a Table Transformer macro inside it if you want to)

  2. Inside this Table Transformer macro, add a Page Properties Report like in the original question in this discussion.

  3. Create a normal table inside the Table Transformer macro, below the Page Properties Report. This table will map status names to their ordering in integers. For instance: ToDo = 1, In progress = 2, Done = 3.

    We will now JOIN the two tables by the status column, allowing us to order the joined table by the integers. This allows every status label to get their ordering defined once and in one place.

  4. Add something like the following SQL query to the transformer:
    SELECT T1.'Title', T1.'Status', T1.'Contact'
    FROM T1
    LEFT JOIN T2 ON T2.'Status' = T1.'Status'
    ORDER BY T2.'Ordering'

    This will map the ordering column onto the data table, allowing you to use it for sorting, but without displaying it.

    Here, T1 is the first table (Page Properties Report) and T2 is the second table (The one that maps status to order number)

This is an example of my status ordering table:

status ordering.jpg

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 5, 2022

Hi @Torkil Johnsen ,

Thank you for the description of your workaround!

But please note that the Table Transformer macro you've kindly mentioned above belongs to the Table Filter and Charts for Confluence app (the plugin is developed and supported by our company).

Not to confuse other Community members, the app is not installed by default and it is a paid one unless your Cloud instance is small (1-10 users). 

Like # people like this
Torkil Johnsen
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 5, 2022

Haha, I stand corrected, thanks ;) 

Like # people like this
2 votes
Stiltsoft Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 25, 2022

Hello @Micha Roon and @Kelly Pelkie ,

If you work with many statuses in the Page Properties Report macro, Handy Macros for Confluence can help you do it much faster. Handy Status can be changed in the page view mode and filtered with Table Filter and Charts later on. 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events