CQL fields - NOT operator

[johan milio]

In the page properties report macro it is possible to add several filters to limit the result list.

One of these filters is "Including text" and another one is "With title".

The documentation illustrates how the NOT operator can be used in a "label" filter to exclude the mentioned label (e.g. -labelx means NOT labelx)

But can this NOT operator also be used in the text filters mentioned above? 

So can i for instance say something like "Including text" with value "NOT(closed)" as to exclude all result that contain the word "closed" in it (I can use that word for instance as a status)?

[Stephen Sifers · Accepted solution]

Hello Johan,

Looks like you have a solid understand of how CQL statements can and do work. And applying them to your Page Properties Report is a great use of CQL.

With that said, the Page Properties Report has standard operators for the filters that are predefined. The Label filter does accept CQL for AND, OR and NOT. The other are not as flexible as the label field. Such as the including text filter, this filter will only accept CONTAINS and will not respect a NOT or NOT LIKE statement.

I did go through and test to ensure this is expected and working properly. The including text filter did not respect NOT statements in testing. This is by design.

Here are the list of operators that are allowed by field:

** You can add these filters in CQL-powered macros but in search they're part of the standard search filters, so they don't appear in the Add a filter menu.

Source document: Page Properties Report Macro.

With all this said, I have created a feature request to add NOT and OR CQL statements as a feature. The Feature request can be found here CONFSERVER-57499, please vote and watch this feature request to see updated status.

I hope you find this helpful and it clarifies which operators are allowed per field.

Regards,
Stephen Sifers

[johan iojo]

Hello Stephen, 

your answer is what i expected :-)

I voted for the feature request, and in the meanwhile, I'll make do ..

[Brock Winstead]

You can use more advanced CQL searches and filter options outside of those available in the Page Properties Report's Edit dialog, if you have access to the source editor ("<>" button in the upper right of the page editor). 

Looking at the source for a Page Properties Report Macro, you'll see a parameter labeled "CQL":

 <ac:parameter ac:name="cql">label = "a_label_name"</ac:parameter>

You can put just about any valid CQL statement in there. For example, you can filter for pages that have a certain label but NOT another label:

 <ac:parameter ac:name="cql">label = "zoo_animals" AND label != "penguins"</ac:parameter>

You can also use label IN (...) and label NOT IN (...) to filter for/against a list of labels.

You can filter for pages that do NOT have certain text in them:

 <ac:parameter ac:name="cql">label = "zoo_animals" AND text !~ "eats bamboo"</ac:parameter>

You could apply similar logic to the other text fields, like title, parent, ancestor, creator, etc..

Also, you can use IN/NOT IN with many of the other string-type fields. E.g. filtering for pages whose parent is one of a list of pages, instead of just a single parent filter: parent IN ("Page 1","Page 2"...).

You can also extend the date filters beyond the options presented in the Edit dialog. One of the options there is Created -> within the last month. If you select that option, you'll see it generates an element in the CQL parameter like "created &gt;= now('-1M')". That can be edited to, for example, filter for pages that are OLDER than 3 months:

 <ac:parameter ac:name="cql">label = "a_label_name" AND created &lt; now('-3M')</ac:parameter>

Note that this requires you to &-escape the less-than and greater-than operators. You can use the following increment types: y: year, M: month; w: week; d: day; h: hour; m: minute. You could apply similar logic to lastmodified. 

You can use basically any valid CQL field and the operators/keywords that apply to it: https://developer.atlassian.com/server/confluence/advanced-searching-using-cql/. You'll want to test any changes to make sure they do what you want them to do, but you're not going to break anything by experimenting.

HUGE CAVEAT: If you source edit a Page Properties Report macro and then make changes using the regular Macro Edit dialog, your source changes will be erased/overwritten.

[johan milio]

thanks brock, a very detailed anwer!

i'm eager to try it out...

[Jason H]

This... should be linked in the issue. This is something can be done, now, today, instead of waiting for whenever they'll get around to maybe fixing something that seems so basic. Thank you, so very much, for taking the time to write this up.