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

[Aleks Irinics]

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

[Natalie Paramonova _Stiltsoft_ · Accepted solution]

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:

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

Hope this helps.

[Dhiraj Kr_ Gupta]

Can you help me for removing vertical lines separating column

[Stiltsoft support]

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:

Hope it helps your case.

[Dhiraj Kr_ Gupta]

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]

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;
}

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]

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]

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

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]

Thank You very much