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.

[tbellmr]

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?

[Natalie Paramonova _Stiltsoft_]

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.

[themaxiyt2]

la vida es mas ermosa que nada