Table Filter Macro using Variable as Input

Emily Berg June 24, 2021

I have several pages that I need to create that reference the same table excerpt.  Within the header of the Confluence page I would like to include the inputs for filter the table (so I don't have to edit the macro for each page).  Is there way to reference a variable within a Filter Table macro?  In this example, I would like to filter the table based on "Unit #" column.  And my input is "Alpha-2".

2021-06-24_14-26-21.png

 

1 answer

1 accepted

4 votes
Answer accepted
Katerina Kovriga {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.
June 25, 2021

Hi @Emily Berg ,

If I got your case right, you reuse the source table and wrap every Table Excerpt Include macro into the Table Filter macro to filter by different columns and values (so you'll get a kind of a dashboard).

Unfortunately, we don't have any automatic rules to assign filters/values to the Table Filter macro based on additional tables or smth similar.

Emily Berg June 25, 2021

Hi @Katerina Kovriga {Stiltsoft} ,

Okay fair enough.  How about if I wrap the Table Excerpt into a Table Transformer Macro and "filter" the table that way - could I use a page variable to reference in the SQL query to extract a line(s) of interest?

Emily

Katerina Kovriga {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.
June 25, 2021

Hi @Emily Berg ,

The Table Transformer macro can use a page variable in its SQL query:

image (1).png

SELECT T1.* FROM T1
WHERE T1.'Name' = (SELECT T2.'Variable' FROM T2)

The big source table can be reused and inserted via the Table Excerpt Include macro. But you'll still need to wrap your reused table into the Table Transformer and create a mini-table with a different value for all your pages.

It seems that it is not faster than manually filtering you table with the Table Filter macro.

Have I got your case right? You need to have different values for every page?

If you want to show the same filtered table on all of your pages, then you'll need to set your filter once and wrap both your source table and the Table Filter macro on top of it into the Table Excerpt macro.

Emily Berg June 25, 2021

Thank you @Katerina Kovriga {Stiltsoft} ,

Yes, this is what I was going for, but I was hoping to be able to reference a variable outside of the table transformer macro - like maybe within the page's properties macro?  I am trying to prevent an error when typing text and then having to select the proper filter (duplicate entries).

Emily

Katerina Kovriga {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.
June 25, 2021

You may create several mini-tables with variables and wrap every one into the Table Excerpt with a corresponding name.

Then you'll be able to include it into the Table Transformer macro via the Table Excerpt Include macro.

Emily Berg June 25, 2021

@Katerina Kovriga {Stiltsoft} 

Thank you for this comment - it allowed me to be a bit more creative about how I approach this.  I found a solution that works beautifully, using multiple table excerpts across various pages.  Awesome, thank you!

Emily

Katerina Kovriga {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.
June 25, 2021

And thank you for reaching out!

Emily Berg November 10, 2021

@Katerina Kovriga {Stiltsoft}  - Hello!

I am now using this in a different use case and am wondering about accessing the column of interest as a variable too.

2021-11-10_11-24-57.png

With something like this...

SELECT * FROM T1
WHERE (SELECT T2.'Column' FROM T2) = (SELECT T2.'Variable' FROM T2)

Thank you for your assistance!

Emily

Katerina Kovriga {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 10, 2021

Hi @Emily Berg , I'm afraid it is not possible to introduce a column name as a variable.

Emily Berg November 10, 2021

Thank you for the fast response @Katerina Kovriga {Stiltsoft} !

Anna Szabó November 27, 2021

Hi @Emily Berg you can try to transpose the table with a Table Transfo macro fisrt, then after the filtering transfpose it back.

@Katerina Kovriga {Stiltsoft} Thank you for the great answer! Do you know if there is a way to use it for multiple Variables on the same table? Like when I add an extra line on the Variable table (want to filter on B and C too) it only filters on the first one.

Katerina Kovriga {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 27, 2021

Hi @Anna Szabó ,

You may add additional columns for your extra variables to the second table:

Sat 1-1.png

Here is your modified SQL query:

Sat 1-2.png

SELECT T1.* FROM T1
WHERE T1.'Name' = (SELECT T2.'Variable 1' FROM T2)
OR T1.'Name' = (SELECT T2.'Variable 2' FROM T2)

And here is the result:

Sat 1-3.png

Hope this helps your case.

Anna Szabó November 27, 2021

@Katerina Kovriga {Stiltsoft} It answers my question! Thank you for the unbeliavebly quick answer!!!

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events