When trying to ‘Aggregation columns’* for columns of "type": "expression", The report returns an error “There was an error rendering your report”
Can you check the django service logs to see if there's a more descriptive error there? I'd also confirm that you can preview the report data source on its own.
Thank you for the Reply, the domain exists on commcarehq so I don`t have the access to get the django Logs. I did preview the datasource and it do populate but the report works only if the aggregation type is field.
Ah, I see. Could you file a support ticket? You should get a more helpful error message than that.
I'm not sure you can us an expression column for aggregations in a report at all though. Those expressions are calculated at the last minute, whereas the aggregations are computed in the database query, limiting the data returned. For that to work, they'd need to happen in the reverse order. To aggregate by that value, you'd need to do the computation in the report data source or in the application.
Yes, That was the issue, I was not able to compute those calculation on the datasoure. The calculation takes a value (case prop) of a case and split it to take the 2nd value then Use Evaluate Function to do computation on the returned result and finally use iterator and reduce item to merge the computed result. I was not able to do that on the datasource, or the datasource dont allow me to compute this multiple computation on a case property(as far as I know).
Here is the computation I did
{
"comment": "----------------------------------MONTHLY REPORT DATE CALCULATOR-START-----------------------------------------",
"field": "report_date",
"description": null,
"format": "default",
"css_class": null,
"width": null,
"aggregation": "simple",
"column_id": "report_date_",
"visible": true,
"calculate_total": false,
"type": "field",
"is_primary_key": true,
"display": "Report Date",
"sortable": false,
"transform": {}
},
{
"field": "report_date",
"description": null,
"format": "default",
"css_class": null,
"width": null,
"aggregation": "simple",
"column_id": "report_date_ec",
"visible": false,
"calculate_total": false,
"type": "field",
"display": "Report Date E.C",
"sortable": false,
"transform": {
"type": "custom",
"custom_type": "gregorian_date_to_ethiopian_date"
}
},
{
"type": "expression",
"column_id": "mth_calc_report_day",
"display": "mth_calc_report_day",
"expression": {
"type": "split_string",
"string_expression": {
"type": "property_name",
"property_name": "report_date_ec",
"datatype": "string"
},
"index_expression": {
"type": "constant",
"constant": 2
},
"delimiter": "-"
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_report_month",
"display": "mth_calc_report_month",
"expression": {
"type": "split_string",
"string_expression": {
"type": "property_name",
"property_name": "report_date_ec",
"datatype": "string"
},
"index_expression": {
"type": "constant",
"constant": 1
},
"delimiter": "-"
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_report_year",
"display": "mth_calc_report_year",
"expression": {
"type": "split_string",
"string_expression": {
"type": "property_name",
"property_name": "report_date_ec",
"datatype": "string"
},
"index_expression": {
"type": "constant",
"constant": 0
},
"delimiter": "-"
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_year_deduct",
"datasource": "integer",
"transform": {},
"expression": {
"test": {
"type": "and",
"filters": [
{
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_report_month",
"datatype": "integer"
},
"operator": "eq",
"property_value": 1
},
{
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_report_day",
"datatype": "integer"
},
"operator": "lt",
"property_value": 21
}
]
},
"expression_if_true": {
"constant": 1,
"type": "constant"
},
"type": "conditional",
"expression_if_false": {
"constant": 0,
"type": "constant"
}
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_year",
"datatype": "integer",
"transform": {},
"expression": {
"type": "evaluator",
"statement": "a - b",
"context_variables": {
"a": {
"type": "property_name",
"property_name": "mth_calc_report_year",
"datatype": "integer"
},
"b": {
"type": "property_name",
"property_name": "mth_calc_year_deduct",
"datatype": "integer"
}
}
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_month",
"transform": {},
"expression": {
"test": {
"type": "and",
"filters": [
{
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_report_day",
"datatype": "integer"
},
"operator": "gte",
"property_value": 21
}
]
},
"expression_if_true": {
"type": "property_name",
"property_name": "mth_calc_report_month",
"datatype": "integer"
},
"type": "conditional",
"expression_if_false": {
"test": {
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_report_month",
"datatype": "integer"
},
"operator": "eq",
"property_value": 1
},
"expression_if_true": {
"type": "constant",
"constant": 12
},
"type": "conditional",
"expression_if_false": {
"type": "evaluator",
"statement": "a - b",
"context_variables": {
"a": {
"type": "property_name",
"property_name": "mth_calc_report_month",
"datatype": "integer"
},
"b": 1
}
}
}
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "report_month_date_start_ec",
"display": "Report Month Date Start E.C",
"transform": {},
"expression": {
"type": "reduce_items",
"items_expression": {
"datatype": "array",
"type": "iterator",
"expressions": [
{
"type": "property_name",
"property_name": "mth_calc_year"
},
{
"test": {
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_month",
"datatype": "integer"
},
"operator": "lt",
"property_value": 10
},
"expression_if_true": {
"type": "constant",
"constant": "-0"
},
"type": "conditional",
"expression_if_false": {
"type": "constant",
"constant": "-"
}
},
{
"type": "property_name",
"property_name": "mth_calc_month"
},
{
"type": "constant",
"constant": "-"
},
{
"type": "constant",
"constant": "21"
}
],
"test": {}
},
"aggregation_fn": "join"
},
"description": null,
"visible": true
},
{
"type": "expression",
"column_id": "mth_calc_report_day_end",
"display": "mth_calc_report_day_end",
"expression": {
"type": "split_string",
"string_expression": {
"type": "property_name",
"property_name": "report_month_date_start_ec",
"datatype": "string"
},
"index_expression": {
"type": "constant",
"constant": 2
},
"delimiter": "-"
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_report_month_end",
"display": "mth_calc_report_month_end",
"expression": {
"type": "split_string",
"string_expression": {
"type": "property_name",
"property_name": "report_month_date_start_ec",
"datatype": "string"
},
"index_expression": {
"type": "constant",
"constant": 1
},
"delimiter": "-"
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_report_year_end",
"display": "mth_calc_report_year_end",
"expression": {
"type": "split_string",
"string_expression": {
"type": "property_name",
"property_name": "report_month_date_start_ec",
"datatype": "string"
},
"index_expression": {
"type": "constant",
"constant": 0
},
"delimiter": "-"
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_year_deduct_end",
"datasource": "integer",
"transform": {},
"expression": {
"test": {
"type": "and",
"filters": [
{
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_report_month_end",
"datatype": "integer"
},
"operator": "eq",
"property_value": 12
}
]
},
"expression_if_true": {
"constant": 1,
"type": "constant"
},
"type": "conditional",
"expression_if_false": {
"constant": 0,
"type": "constant"
}
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_year_end",
"datatype": "integer",
"transform": {},
"expression": {
"type": "evaluator",
"statement": "a + b",
"context_variables": {
"a": {
"type": "property_name",
"property_name": "mth_calc_report_year_end",
"datatype": "integer"
},
"b": {
"type": "property_name",
"property_name": "mth_calc_year_deduct_end",
"datatype": "integer"
}
}
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "mth_calc_month_end",
"transform": {},
"expression": {
"type": "evaluator",
"statement": "a + b",
"context_variables": {
"a": {
"test": {
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_report_month_end",
"datatype": "integer"
},
"operator": "eq",
"property_value": 12
},
"expression_if_true": {
"type": "constant",
"constant": 0,
"datatype": "integer"
},
"type": "conditional",
"expression_if_false": {
"type": "property_name",
"property_name": "mth_calc_report_month_end",
"datatype": "integer"
}
},
"b": 1
}
},
"description": null,
"visible": false
},
{
"type": "expression",
"column_id": "report_month_date_end_ec",
"display": "Report Month Date End E.C",
"transform": {},
"expression": {
"type": "reduce_items",
"items_expression": {
"datatype": "array",
"type": "iterator",
"expressions": [
{
"type": "property_name",
"property_name": "mth_calc_year_end"
},
{
"test": {
"type": "boolean_expression",
"expression": {
"type": "property_name",
"property_name": "mth_calc_month_end",
"datatype": "integer"
},
"operator": "lt",
"property_value": 10
},
"expression_if_true": {
"type": "constant",
"constant": "-0"
},
"type": "conditional",
"expression_if_false": {
"type": "constant",
"constant": "-"
}
},
{
"type": "property_name",
"property_name": "mth_calc_month_end"
},
{
"type": "constant",
"constant": "-"
},
{
"type": "constant",
"constant": "21"
}
],
"test": {}
},
"aggregation_fn": "join"
},
"description": null,
"visible": true
},
{
"type": "expression",
"column_id": "report_month_date_ec",
"display": "Report Month Date E.C",
"transform": {},
"expression": {
"type": "reduce_items",
"items_expression": {
"datatype": "array",
"type": "iterator",
"expressions": [
{
"type": "property_name",
"property_name": "report_month_date_start_ec"
},
{
"type": "constant",
"constant": " to "
},
{
"type": "property_name",
"property_name": "report_month_date_end_ec"
}
],
"test": {}
},
"aggregation_fn": "join"
},
"description": null,
"visible": true,
"comment": "----------------------------------MONTHLY REPORT DATE CALCULATOR-END-----------------------------------------"
},
The Error Happens when trying to aggregate using the column report_month_date_ec i.e the last column
Hi @andyasne, could you submit this information via a support ticket so that someone can investigate why that didn't work for you to add the calculation on the data source?