Custom Query Report Totals Row

Frappe Framework
1

I would like to add a Totals row to a Script Report I am making, but I haven’t been able to figure out how to do it short of editing the Frappe framework itself.

I have “Add Total Row” selected in the report so that it displays the table row. However, the totaling function is greedy, and it appears to total any row that looks like a number (even if I set the fieldtype to ‘String’), and some of those rows should not have a total. Furthermore, I have a column that contains an average, which doesn’t make sense to total - that column should be averaged in the total row instead.

I’ve looked at questions like this one that seem to be trying to do the same thing as me, but the last response isn’t helpful for me; in fact, I’m not even sure the add_total_row is the function that is running (considering that all of my number columns are totaled - compare with append_totals_row() here: frappe/reportview.py at 6128123eed56a2492de0440d8fabc6bb1c572fba · frappe/frappe · GitHub)

To sum up my situation:

  • I have a Script Report that is successfully displaying.
  • In execute(), I return the following:
    • columns (report query definitions, including fieldtype and options as appropriate
    • data (the results of my query)
    • message (doesn’t appear to do anything)
    • chart (to display a pretty chart)
    • report_summary (also doesn’t appear to do anything)
    • skip_total_row (just for fun; it’s set to False)
  • All number columns are totaled, regardless of the fieldtype I specify in columns
  • one of my columns contains an average, which should display an average in the Totals row
  • add_totals_row() or append_totals_row() added to the [report].py file don’t run (I didn’t really expect them to)
  • I don’t want to append my own Totals row to data, because the report won’t work properly.

Here are my execute and get_columns functions from the [report].py file so you can see what I’m talking about:

def execute(filters=None):                                                                                                                                                                                                                      
    columns = get_columns()
    data = query(filters)
    chart = get_chart(data)
    message = ''
    report_summary = [{
        'Value': 'test',
        'Indicator': 'green',
        'label': _('test l'),
        'datatype': 'String',
    }]
    skip_total_row = False

    report = (columns, data, message, chart, report_summary, skip_total_row)
    return report

def get_columns():
    return [
        {
            'fieldname' : 'sales_partner',
            'fieldtype' : 'Link',
            'options'   : 'Sales Partner',
            'label'     : 'Sales Partner',
        }, {
            'fieldname' : 'count_of_stores',
            'fieldtype' : 'Int',
            'label'     : 'Stores Count',
        }, {
            'fieldname' : 'sum_of_sides',
            'fieldtype' : 'Int',
            'label'     : 'Total Sides',
        }, {
            'fieldname' : 'period',
            'fieldtype' : 'String',
            'label'     : 'Period',
        }, {
            'fieldname' : 'length',
            'fieldtype' : 'String',
            'label'     : 'Length',
        }, {
            'fieldname' : 'avg_period_price',
            'fieldtype' : 'Currency',
            'label'     : 'Avg Period Price',
            'options'   : 'Average',
        }, {
            'fieldname' : 'sales_persons',
            'fieldtype' : 'String',
            'label'     : 'Sales Persons',
        }, {
            'fieldname' : 'grand_total',
            'fieldtype' : 'Currency',
            'label'     : 'Grand Total',
        }, {
            'fieldname' : 'distinct_store_banners',
            'fieldtype' : 'String',
            'label'     : 'Store(s)',
            'width'     : 150,
        }, {
            'fieldname' : 'active_continuation_count',
            'fieldtype' : 'String',
            'label'     : 'Rsvns In Continuation',
        }, {
            'fieldname' : 'sales_order',
            'fieldtype' : 'Link',
            'options'   : 'Sales Order',
            'label'     : 'Sales Order',
            'width'     : 150,
        }
    ]

The two columns I’m having trouble with are “Length” and “Avg Period Price”. How can I solve my problem?

2

bump.

3

I’ve discovered that even if I alter the frappe python code so that it can average the averages column(s), it won’t display on the frontend due to this bug: Reporting Front-end and back-end disagree on totals · Issue #13696 · frappe/frappe · GitHub. Basically python totals don’t match javascript front-end totals.

4

Hey. Did you ever come up with a solution for a custom totals_row?

5

No. Well, nothing that integrates with the table. MariaDB implements SELECT WITH ROLLUP (SELECT WITH ROLLUP - MariaDB Knowledge Base) which helps, but it isn’t really a total in the results table.

6

Interesting, this could possibly be useful if your report relied on a single query, however it seems to offer the same data that the built in totals_row offers without the benefit of it being excluded from the scroll area of the report.

7

You can also average or perform other calculations, while the totals_row is only capable of a SUM IIRC. Since the totals_row is an interface feature, it makes the SELECT WITH ROLLUP option look very hacky, and can be confused with the rest of the report.

8

I think I found a way to do this. In your script report JS file add a “formatter” function. In the formatted function test for

if (!row) {
    // This is the totals row
    if (column.fieldname == "some_column_to_change_total_value") {
        // Do some calculation
        value = new_value;  
   }
}

To access the data in the table use frappe.query_report.data, which is an array of all the table data.

9

Here is a full example of the “formatted” function:

	formatter: function (value, row, column, data, default_formatter) {
        value = default_formatter(value, row, column, data);
        if (!row && !["units", "extended_price", "revenue"].includes(column.fieldname)) {
            value = "";
        }
        if (data && data.bold) {
            value = value.bold();
        }
		return value;
	},

This example only displays the sums for “units”, “extended_price” and “revenue” columns.