Issue with field sorting using Table Transformer and Pivot Table in Confluence

[Santanu Roy]

Issue with field sorting using Table Transformer and Pivot Table in Confluence.

I am using Table Transformer macro and have come-up with a sort order of a column.

Column Name: Approval Category

Values: Req, QA, BSA, UAT

However, when I am using a Pivot on top of Table Transformer, my column Sort Order is changing.

I am getting the sorting in this form (which I do NOT want).

Values: BSA, QA, Req, UAT

Is there a way to retain my sort order in Pivot, either using SQL or on JIRA?

Values: Req, QA, BSA, UAT

[Katerina Kovriga _Stiltsoft_]

Hi @Santanu Roy ,

You have two options to solve the problem.

The first one is to choose the Preserve original sorting type of sorting for your Pivot macro. If the default sorting or the sequence of mentioned items in the original table suit you, it will be a perfect workaround:

The second option is to use an additional Table Transformer macro after your Pivot Table macro and set your own custom sorting strictly:

Type in your custom SQL query:

SELECT * FROM T*
ORDER BY
CASE WHEN T1.'Approval Category' LIKE "Req" THEN "1"
WHEN T1.'Approval Category' LIKE "QA" THEN "2"
WHEN T1.'Approval Category' LIKE "BSA" THEN "3"
WHEN T1.'Approval Category' LIKE "UAT" THEN "4"
ELSE T1.'Approval Category' END ASC

[Candice]

Hi Katerina.

I am trying to custom sort my pivot table as per the 2 options you provided above.

In both instances, the Table Transformer reflects the correct sort order however, the pivot table defaults to alphabetical ordering.

Problem:

1. There are 10+ project managers assigned to multiple projects for the year.

2. Each project has a different project size (weeks are allocated per size).

3. I want to see the total number of projects each PM is working on as well as the total number of weeks.

4. I am able to see this view however, I'd like to custom sort the project sizes to start from XS - S - M - etc.

[Stiltsoft support]

Hi @Candice ,

In the examples above we talk about "vertical" sorting (I mean, in columns).

From your screenshot I see that you use rows in your Pivot Table: both options won't work here. 

[Candice]

Thanks for the response. I see what you mean.
Is there any way that I could do "horizontal" sorting?

[Stiltsoft support]

The only way is a manual one: wrap your Pivot Table in the Table Transformer macro. You'll get long one-row headers. Smth like "Count L", "Count M", etc.

Then you'll need to list every column in that particular order that you need and rename it if necessary.

For example, smth like this:

SELECT T1.'Project Manager / Project Size' AS 'Project Manager',

T1.'Count XS' AS 'XS',

T1.'Count S' AS 'S',

...

FROM T1

You may type in T1. and wait for the list of available columns - choose the required column from the dropdown menu.

[Candice]

Thanks for this. The view isn't the best - I appreciate your feedback :)