Table Transformer - Format row background colour if its from T1, T2...Tn

Aleks Irinics November 16, 2022

Hi all

I am new to SQL and Table formatting, and paving my knowledge but got stuck with the following: 

I collating information from multiple tables (around 40) into a single one based on the condition (pretty simple so far). However, I realised as the result table will be quite big, it would be helpful to identify information coming from input tables. I am looking for something like this (see excel example). So Odd tables are highlighted and even are kept as is.

Any help if appreciated. Thank you very much. 

Aleks

Capture.PNG

1 answer

1 accepted

3 votes
Answer accepted
Natalie Paramonova _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.
November 17, 2022

Hi @Aleks Irinics ,

To color rows coming from different tables, we may suggest adding an optional column to every table indicating its belonging. For example, name it as a “Source table” and put the T1, T2, T3,… Tn in the cells.

Then you may merge/transform the master report with the first Table Transformer macro and then use the second Table Transformer macro to color your rows based on the contents of the “Source table” column. And don’t forget to use the Table Toolbox to nest macros as your hosting is Cloud.

Here is an example how to color background based on conditions.

Then you may use the Table Filter macro (again inside the Table Toolbox macro) and hide the unwanted “Source table” column.If this is not the case, here is an example of an alternative row coloring that may also help to make your big tables easy-to-read:

Wed 9-1.png

Wed 9-2.png

Wed 9-3.png

SELECT
FORMATWIKI("{cell:bgColor=" + 'Color' + "}", 'Description', "{cell}") AS 'Description',
FORMATWIKI("{cell:bgColor=" + 'Color' + "}", 'Additional notes', "{cell}") AS 'Additional notes',
FORMATWIKI("{cell:bgColor=" + 'Color' + "}", 'Source', "{cell}") AS 'Source'
FROM
(SELECT *,
CASE
WHEN 'Row No.'%2 = 0 THEN "#DEEBFF"
ELSE "white"
END AS 'Color'
FROM (SELECT *, ROWNUM(*) AS 'Row No.'
FROM T1))

Wed 9-4.png

Hope this helps.

Dhiraj Kr_ Gupta May 15, 2024

Can you help me for removing vertical lines separating column

Stiltsoft support
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.
May 16, 2024

Hi @Dhiraj Kr_ Gupta ,

As the Table Transformer macro treats source tables as mini databases, it draws borders around your cell contents. But you can color them white and make them "invisible".

The first option is to use the following SQL query:

SELECT
FORMATWIKI("{cell:border-color=white}", 'Column 1' + "{cell}") AS 'Column 1',
FORMATWIKI("{cell:border-color=white}" + 'Column 2' + "{cell}") AS 'Column 2'
FROM T*

And the second option is to use the Stylesheet tab of the macro: 

td {border-color:white}

The result will be the same:

Thu 7-1.png

Hope it helps your case.

Dhiraj Kr_ Gupta May 16, 2024

Thanks for your support, td stylesheet macro worked.

How to make appearance of row color alternative like blue row then white and so on..

Stiltsoft support
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.
May 16, 2024

And another CSS for the Stylesheet tab from our developers to remove only vertical lines from your table:


.confluenceTh, .confluenceTd {
border: none;
border-bottom: 1px solid #C1C7D0;
}

Thu 9-1.png

To color your rows alternatively, you may use the following SQL query:

SELECT

FORMATWIKI("{cell:bgColor=" + 'Color' + "}", 'Column 1', "{cell}") AS 'Column 1',

FORMATWIKI("{cell:bgColor=" + 'Color' + "}", 'Column 2', "{cell}") AS 'Column 2'

FROM

(SELECT *,

CASE

WHEN 'Row No.'%2 = 0 THEN "#DEEBFF"

ELSE "white"

END AS 'Color'

FROM (SELECT *, ROWNUM(*) AS 'Row No.'

FROM T1))

Hope it helps your case.

Dhiraj Kr_ Gupta May 16, 2024

Through sql, it is working but it makes the link in the field simple text/ and some become invalid. Thus it  will not work for me. Please give me any stylesheet Alternative

Stiltsoft support
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.
May 16, 2024

The query should be working fine and preserve your initial cell formatting:

Thu 9-2.png

The links may break if you use plusses instead of commas:

FORMATWIKI("{cell:bgColor=" + 'Color' + "}" + 'Column 1' + "{cell}") AS 'Column 1',

The variant from the previous reply uses commas that preserve initial cell formatting:

FORMATWIKI("{cell:bgColor=" + 'Color' + "}", 'Column 1', "{cell}") AS 'Column 1',

Dhiraj Kr_ Gupta May 16, 2024

Thank You very much

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events