How to add total row in reports?

ERPNext Reports
1

I was trying to add a total row to my report.I have gone through those related posts here and is quite confused.

from __future__ import unicode_literals
import frappe
from frappe import _, msgprint

def execute(filters=None):
 if not filters: filters = {}

columns = get_columns()
last_col = len(columns)
data = get_entries(filters)
total=get_total()
paid_amount=get_paid_amount()
item_list = get_entries(filters)
   return columns, data

 def get_columns():
return [_("Sales Invoice") + ":Link/Sales Invoice:140", _("Customer") + ":Link/Customer:140", _(" Mode of Payment") + ":200",
     _("Total Amount") + ":200",
    _("Paid Amount") + ":120"
]

def get_conditions(filters):
conditions = ""
if not filters.get("Date"):
    msgprint(_("Please select Date"), raise_exception=1
    )
else:
    conditions += " and sale.posting_date = '%s'" % filters["Date"]
    # conditions += " and payment.mode_of_payment=%(Mode of Payment)s"

# if filters.get("Date"): conditions += " and sale.posting_date=%(Date)s"
if filters.get("Mode of Payment"): conditions += " and payment.mode_of_payment=%(Mode of Payment)s"

return conditions

def get_entries(filters):
conditions = get_conditions(filters)
return  frappe.db.sql("""select payment.parent, sale.customer,payment.mode_of_payment,
    sale.grand_total, payment.paid_amount
    from `tabSales Invoice` sale, `tabPayment` payment
    where payment.parent=sale.name  %s
    order by payment.parent DESC""" %
	conditions, filters, as_list=1)



# return entries

def get_total():
total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale""" )
return total_amount

def get_paid_amount():
paid_amount=frappe.db.sql("""SELECT SUM(payment.paid_amount) FROM `tabPayment` payment""" )
return paid_amount

Obviously I want the total of sale.grand_total and payment.paid_amount.I still a beginner.Can someone help me?

2

have you tried to thick total rows option when create a report ?

3

Yes,but it does not gave the totals.

4

You have to append your totals row to data

5

Now it shows :

OperationalError: (1054, "Unknown column 'payment.mode_of_payment' in 'where clause'")

Code after modification:

def execute(filters=None):
if not filters: filters = {}

columns = get_columns()
data = get_entries(filters)
if data:
    total=get_total(filters)
    paid_amount=get_paid_amount(filters)
    data.append(['Total Invoice Amount','','',total,paid_amount])

return columns, data
def get_columns():
return [_("Sales Invoice") + ":Link/Sales Invoice:140", _("Customer") + ":Link/Customer:140", _(" Mode of Payment") + ":200",
     _("Total Amount") + ":200",
    _("Paid Amount") + ":120"
]

def get_conditions(filters):
conditions = ""
if not filters.get("Date"):
    msgprint(_("Please select Date"), raise_exception=1
    )
else:
    conditions += " and sale.posting_date = '%s'" % filters["Date"]
if filters.get("Mode of Payment"): conditions += " and payment.mode_of_payment='%s'" % filters["Mode of Payment"]
# if filters.get("Mode of Payment"): conditions += " and payment.mode_of_payment=%(Mode of Payment)s"

return conditions

def get_entries(filters):
conditions = get_conditions(filters)
entries = frappe.db.sql("""select payment.parent, sale.customer,payment.mode_of_payment,
    sale.grand_total, payment.paid_amount
    from `tabSales Invoice` sale, `tabPayment` payment
    where payment.parent=sale.name  %s
    order by payment.parent DESC""" %
	conditions, filters, as_list=1)

return entries

def get_total(filters):
conditions = get_conditions(filters)
date1= filters.get("Date")
mode= filters.get("Mode of Payment")
total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale where sale.grand_total=sale.rounded_total %s """ % conditions, filters )
# total_amount=frappe.db.sql("""SELECT SUM(sale.grand_total) FROM `tabSales Invoice` sale , `tabPayment` payment  where sale.posting_date= %s """ % date1 )
print total_amount
return total_amount


def get_paid_amount(filters):
conditions = get_conditions(filters)
date1= filters.get("Date")
paid_amount=frappe.db.sql("""SELECT SUM(payment.paid_amount) FROM `tabSales Invoice` sale, `tabPayment` payment where payment.parent=sale.name %s """ % conditions, filters  )
return paid_amount
6

Not sure what is tabPayment - could be related your custom design.