CQL filter - reporting pages that have been approved or expired in a time period
- Robert Alexander Marshall
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 the 2 week period before the current day
report on content set to expire in the next 4 weeks
The CQL Filter parameter now accepts Confluence CQL format functions and keywords and can include OR and comparison operators. 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 of Approved.
The report can be filtered to a specific destination state for a workflow approval. In our example, this is the workflow’s 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 all the state changes in the report in the last two weeks.
In addition,
The report displays content transitioned to the Approved state (workflow final state) in the last 2 weeks before 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 of 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 before the current day
stateexpiry> startOfDay("-7d")
The CQL filter option is just one of the filter options in the document states report macro.