How to avoid prefix of "Count" or "Sum" on generating charts from Pivot table

[Ramesh PS]

HI,

I am creating few charts (Pie) using the pivot tables and getting the prefix like "Count" and "Sum".

Unable to find a way to have column labels displayed without such prefixed in chart.

Please advise.

[Natalie Paramonova _Stiltsoft_ · Accepted solution]

Hi @Ramesh PS ,

You can use the Table Transformer macro for this purpose. Wrap the Pivot Table macro in the Table Transformer macro, rename columns and build a chart based on the transformed table:

Use the the similar SQL query:

SELECT 'Column 1', 'Sum of Column 2' AS 'Column 2', 'Sum of Column 3' AS 'Column 3' FROM T*

The result is  the following:

Please let me know if you have any further questions.

[Ramesh PS]

any other alternative if Table Transformer Macro is not available?

[Natalie Paramonova _Stiltsoft_]

The Table Transformer macro is included in the Table Filter and Charts app as well as the Pivot Table and Chart from table macros. This macro is available starting from version 5.0 of the app.

Unfortunately, there are no options to rename table columns except this macro.

[Ramesh PS]

Thanks This helps.

[Natalie Paramonova _Stiltsoft_]

@Ramesh PS , I'm happy it works for you :)

[Ramesh PS]

Hi..

I have another challenge..

I have a table like this..

and using a simple query to rename the column names..

SELECT "RAG Status" as "RAG", "JIRA Phase" as "Phase", "Count Business Solutions" as "BS"  FROM T1 (Tried T* also)

but getting the output as show below and not retaining the actual data after renaming the column name..

please advise.

-----------------

Will it be possible to assign a name to a table .. either created Manually or with table view macro to display form data  or a Pivot table..

I am trying to use custom table name instead of T1 or T* that can be confusing at times.

[Natalie Paramonova _Stiltsoft_]

Hi @Ramesh PS ,

In SQL syntax the double quotes character means a value while the single quote character means a column label. So please try the following query:

SELECT 'RAG Status' AS 'RAG', 'JIRA Phase' AS 'Phase', 'Count Business Solutions' AS 'BS  FROM T1

One more hint is when you type T1. you can see all the columns from the first table and just click to select the column.

Regarding your question about renaming tables, unfortunately, there is no such feature in the app. T* means all tables within the Table Transformer macro body; T1, T2, etc. means the particular table number in order within the Table Transformer macro body.

[Ramesh PS]

Thanks.

[Andrey Khaneev _StiltSoft_]

Chart legend gets rid of "Sim of" starting from version 5.3.0 of Table Filter and Charts.

[Ramesh PS]

Hi.. thanks..

is it by design or do we have to manually setup?

can I get rid of Table Transformation macro in this case?

[Andrey Khaneev _StiltSoft_]

It is by design, no setup is needed. All values in the legend get rid of a common prefix when the chart is built on the basis of the Pivot Table macro.

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Ramesh PS,

Starting with the Table Filter and Charts for Confluence version 9.6.0 (Server/Data Center) and 20 Dec, 2022 for Cloud, we’ve made an improvement to the Pivot Table macro: such prefixes as “Sum of …”, “Literal of …” etc. can be eliminated.