Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

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

What do you think is the most *delightful* Confluence feature? Comment for a prize!

- Create your own custom emoji 🔥 - "Shake for Feedback" on mobile 📱 - An endless supply of GIFs via GIPHY 🤩 Is there anything quite as nice as a pleasant surprise? Comment below with what...

463 views 24 9
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