CQL filter - reporting pages that have been approved or expired in a time period

Owned by Robert Alexander Marshall
Dec 14, 2023
2 min read
1 Overview | 2 Pages published in the last 2 weeks | 3 Content about to expire | 4 Related links

Overview

These are two examples of using a CQL filter of the document states report macro to create a dynamic report

  • report content state changes to a specific state in a 2 week period prior to the current day

  • report on content set to expire in the next 4 weeks

The CQL Filter parameter now accepts Confluence CQL format functions, keywords, and can include OR and comparison operators. Simply add the CQL format filter to the CQL filter dialogue box in the document states report macro editor.

Pages published in the last 2 weeks

 In this simple example, we have one workflow applied in the space. The workflow has a final state, Approved.

The report can be filtered to a specific destination state for a workflow approval. In our example, this is the workflow final state, Approved.

We can add the following CQL to the CQL Filter dialogue box in the document states report macro editor

  • statechange>=now(“-2w”)

This CQL filter will list in the report all the state changes that have occurred in the last two weeks.

In addition,

The report displays content transitioned to the Approved state (workflow final state) in the last 2 weeks prior to the current day.

This report example may include content that transitioned to the Approved state in the period set by the CQL filter but is currently in a different workflow state

The report columns have been configured to include the content version on the transition to the final state, Approved.

Content about to expire

You can report on content whose current state (with a due date) is due to expire in the next 4 weeks from the current day by using the following CQL filter.

  • stateexpiry<=now("4w")

This filter will not display content that has already expired.

 A rolling report can be generated to the end of the current period using a CQL function reference, for example 

  • stateexpiry<endOfMonth()

Or from the beginning a period using for example since the beginning of the month

  • stateexpiry>=startofmonth()

Or simply up to the current point in time

  • created<= now()

You can set periods for these CQL functions, for example in the 7 days prior to the current day

  • stateexpiry> startOfDay("-7d")

The CQL filter option is just one of the filter options available in the document states report macro.

Comala Document Management CQL filter examples

Related documentation

Atlassian documentation