decimal points appear on simple integer addition on table transformer

[emagnun]

I have a table to track jira task based on their status as below.

The inner table-transformer is to get the numbers properly for each status column. I use split function to retrieve the numbers.  (eg: T1.'To Do'->split(" ")->0 AS 'To Do')

The outer table transformer is to add a total column that sums up each status column. But the output adds decimal points to the total column, i dont know why.

I tried to use ROUND(T1.'To Do' + T1.'In Progress' + T1.'Done' AS 'Total',0) as well, but decimal points still there.  Pls help.

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @emagnun ,

Check the settings of your Table Transformers -> Decimal places. You may have "2" there (if earlier you used macros with this precise settings, the current macro may catch it).

[emagnun]

got it... my bad. it was the settings.

So ROUND() can not override if the settings has it 2?

[Katerina Kovriga _Stiltsoft_]

No, and it is always better to leave this field blank if you use ROUND() inside the SQL query.

[emagnun]

@Katerina Kovriga _Stiltsoft_ I tried to combine two table-transformers into 1 by the adding below yellow query, but it seems to be working as string concatination. How do I get this to work as integer addition?

[Katerina Kovriga _Stiltsoft_]

Try this SQL:

SELECT T1.'Sprint',
T1.'To Do'->split(" ")->0 AS 'To Do',
T1.'In Progress'->split(" ")->0 AS 'In Progress',
CAST((T1.'To Do'->split(" ")->0) AS INT) + CAST((T1.'In Progress'->split(" ")->0) AS INT) AS 'Total'
FROM T*

Adjust it to the third "Done" status, I have the two statuses.

[emagnun]

Worked like a charm. Thanks

[Katerina Kovriga _Stiltsoft_]

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The new macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.