Page Properties Report CQL on date?

tbellmr

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

 

 

Natalie Paramonova _Stiltsoft_

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.

Filter all the not empty values in the Completed column.

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.

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

Get the following result:

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