How to group a column when the column type is an expression?

is it possible to group a column (“aggregation”: "simple”) of an expression type?

I tried the following and its not working.

{

"format": "default",

"expression": {      

   "test": {

//ASSIGN STATUS TEXT BASED ON DOC COUNT

}

  "expression_if_true": { },

  "type": "conditional",

  "expression_if_false":  {      }

},

"aggregation": "count_unique",  //NOT Working

"column_id": "status",

"visible": true,

"type": "expression",

"display": "status"

},
and Here is the current report

j

I wanted the report to be like this

Health center Name , Status , Numb of Users

Adecharke , Least Active , 3

Gacheno ,Least Active , 2

Is there any other way to achieve this?

This is currently not possible since the expressions run off the compiled row and the aggregations happen in the database.

I think the simplest thing to do would be to port the expression column to the data source and then use a normal field column off that. Is that an option for you?

The Expression column(status) is computed from another report column(doc count) which is already an aggregation of count_unique.
The status column can also be done via conditional aggregation , but conditional aggregation only works for static reports and also, I don`t know if aggregation by count works on conditional aggregation type column.

I believe that the aggregation should work for conditional aggregation columns.

It appears that the aggregation property is not supported for expression columns. Looking at https://github.com/dimagi/commcare-hq/blob/1726b530f751ba9bf8e2296290284361b1299e0e/corehq/apps/userreports/reports/specs.py#L472 it appears that expressions inherit from the BaseReportColumn, but aggregations are only supported on columns that inherit from FieldColumn https://github.com/dimagi/commcare-hq/blob/1726b530f751ba9bf8e2296290284361b1299e0e/corehq/apps/userreports/reports/specs.py#L129

1 Like