Confluence Tables Without Limits: How to Use Table Transformer
You can do anything with Confluence tables. And it is actually true! If you use the native features of Confluence tables and enhance their potential with third-party macros, you can achieve any desired result.
One of such macros is Table Transformer from the Table Filter and Charts for Confluence app. It can help you format your tables precisely as you want it, without any limitations. And in some cases, it can be a true lifesaver that replaces multiple other tools. Although it requires basic SQL knowledge, once you get a hang of it, you’ll keep using the macro all the time.
To show you the versatility of Table Transformer, we gathered the most popular cases from our users. Keep reading to discover more.
Merge data from two different tables
Case: You use both Jira and Confluence to manage projects. Most data about tasks comes from Jira, but some reports require adding more details stored in Confluence. That is why you want to combine a Jira issues table with relevant data about tasks from a regular Confluence table.
Solution: The Table Transformer macro allows you to merge and lookup tables. It can be applied to tables of any structure and length. The only thing you need is a common column for the data to match correctly.
Insert the Table Transformer macro and wrap two tables inside its body: the Jira Issues table and an additional manually created table for your comments.
For you to unite the tables, they both should have a column with the same name. In this case, choose the standard “Key” column.
Go to the Table Transformer macro settings and enter the following custom SQL query:
SELECT T1.'Key', T1.'Summary', T1.'T', T1.'Updated', T1.'Assignee', T1.'Status', T1.'Created', T2.'Comment' FROM T1 JOIN T2 ON T1.'Key' = T2.'Key'
Query explanation: select the columns you want to include and their order, and add the instruction to join two tables based on the “Key” column.
The final table should look like this:
Turn IDs into names
Case: You have a few tables with data about your company’s employees. All they have in common is IDs, but you would like one table with data with names instead of IDs.
Solution: When you merge two tables, you can pick what data you show as well.
You’ll need a reference table to replace IDs with names. If one of your tables already has IDs and names, it will work fine in this case.
Place the Jira macro and the reference table into the Table Transformer macro.
Use the following query:
SELECT T1.'Name', T1.'Gender', T1.'Date of birth', T1.'Insurance', T2.'Email', T2.'Department', T2.'Job title', T2.'Username' FROM T1 OUTER JOIN T* ON T1.'ID' = T2.'ID'
Query explanation: select the columns from both tables you want to show in the final table and choose a column to match the tables by.
After you save the macro and the page, you’ll see the relevant names instead of IDs.
Turn data array into table
Case: You have a list of comma-separated employee names from each office in a table. You want to break it down into a detailed table where each name has a separate cell.
Solution: Formatting your table can be quick and easy with Table Transformer.
Wrap the table into the Table Transformer macro.
Write the following query:
SEARCH / AS @a EX('Employees'->split(",")) /
RETURN(@a->'Office' AS 'Office', _ AS 'Employees') FROM T1
Query explanation: define the separator used in the table and the columns you want to have.
This is what the final table looks like.
Working with dates
Case: You are planning monthly or quarterly tasks, and you want to display each period’s start date in your table automatically.
Solution: Apart from visually formatting the table, Table Transformer allows you to perform custom calculations. You can calculate numbers or dates, depending on what is more relevant to you.
Place your table into the Table Transformer macro.
Write the following query:
SELECT *, YEAR(T1.'Due Date') + "-" + ((1 + (FORMATDATE(T1.'Due Date', "i") - 1) * 3)::string)->padStart(2, "0") + "-01" AS 'Quarter Start' FROM T1
Confluence tables without limits
As you can see, the Table Transformer macro allows you to process Confluence tables in various ways. And these are just a few of our users’ most common cases!
You can learn more about SQL syntax or other useful cases in our documentation.
If you want to test the abilities of Table Transformer and other macros bundled in the Table Filter and Charts for Confluence app, follow the instructions below.
