It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Page Properties Report CQL on date? Edited

I can see that the include text filter is very close to what I need but not sure how to finalize my query.

I have page properties that include a custom "Completed" date field that I populate using the // calendar.  If Completed is empty (null), it is not complete but if it is populated with a date then it is complete.

So how do I write the query to delineate between entries that have and do not have a complete date?

Your assistance will be very much appreciated!  If there is another way to accomplish the same - I am interested in that too.  I am a space admin so no user macros for me...

 

 

1 answer

Hi @tbellmr ,

I think our Table Filter and Charts for Confluence app can help you resolve your use case.

The first option is to filter table data. Place the Page Properties Report macro (or a manually created table or other macros generating tables) in the Table Filter macro body.

2019-12-18_13h26_01.png

Filter all the not empty values in the Completed column.

2019-12-18_13h26_21.png

The second option is to add colorful statuses as indicators.

Place the Page Properties Report macro in the Table Transformer macro body. Add a small table with the Status macros.

2019-12-18_13h31_16.png

Use the following SQL query that meets your conditions:

SELECT
T1.'Task',
T1.'Completed',
CASE WHEN T1.'Completed' IS NULL THEN "INCOMPLETE" ELSE "COMPLETE" END AS 'Completion'
FROM T1

2019-12-18_13h31_33.png

Get the following result:

2019-12-18_13h31_00.png

You can further filter, calculate and even build charts based on the Page Properties Report macro.

HI Natalie, your table filter macro looks like it would do the trick.  Unfortunately we do not have that licensed and looks like it would be very difficult to get anything purchased.

 

Just curious on the use of the T1 table in the SQL query in the Table Transformer macro - how did that table name get assigned?

Hi @tbellmr ,

T1, T2, T3, etc. means the ordinal number of the table inside the Table Transformer macro. The macro provides the ability to merge multiple tables that's why there may be multiple tables within the macro body and we name them by their ordinal number.

Like themaxiyt2 likes this

la vida es mas ermosa que nada

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Confluence

How is your team having fun and bonding, remotely, utilizing Confluence?

Thanks everyone for answering last week’s question. The winner of the random drawing from those who commented is: @LarryBrock I’ll contact you separately with your prize details. This wee...

306 views 9 7
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you