Sort Page Properties Report by status

[Micha Roon]

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

[Diego · Accepted solution]

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:

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

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

[Micha Roon]

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

[Kelly Pelkie]

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


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:

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_]

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]

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_]

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.

[Kelly Pelkie]

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!

[Torkil Johnsen]

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:

[Katerina Kovriga _Stiltsoft_]

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

[Torkil Johnsen]

Haha, I stand corrected, thanks ;) 

[Elena Ovdienko _Stiltsoft_]

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.