ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Script Report empty rows


#1

hello,
i’m making a script report with date input conditions from user (From and To date), but when now after finishing the query… when i open the report the data is fetched, i mean the rows appears but with empty values check picture below. What could be the problem?


#2

and this is my Python file:

from __future__ import unicode_literals
import frappe
from frappe import _

def execute(filters=None):
	columns, data = [], []
	columns=get_columns()
	data=get_data(filters,columns)
	return columns, data

def get_columns():
	return [
		{
			"label": _("Name"),
			"fieldname": "name",
			"fieldtype": "Link",
			"options": "Sales Invoice",
			"width": 80
		},
		{
			"label": _("Status"),
			"fieldname": "status",
			"fieldtype": "Data",
			"width": 100
		},
		{
			"label": _("Customer Name"),
			"fieldname": "customer_name",
			"fieldtype": "Link",
			"options": "Customer",
			"width": 120
		},
		{
			"label": _("Territory"),
			"fieldname": "territory",
			"fieldtype": "Data",
			"width": 100
		},
		{
			"label": _("Union Europeen"),
			"fieldname": "union_europeen",
			"fieldtype": "Check",
			"width": 80
		},
		{
			"label": _("Tax ID"),
			"fieldname": "tax_id",
			"fieldtype": "Float",
			"width": 80
		},
		{
			"label": _("Shipping Rule"),
			"fieldname": "shipping_rule",
			"fieldtype": "Float",
			"width": 80
		},
		{
			"label": _("Total Taxes and Charges"),
			"fieldname": "total_taxes_and_charges",
			"fieldtype": "Currency",
			"width": 80
		},
		{
			"label": _("Total HT"),
			"fieldname": "total_ht",
			"fieldtype": "Currency",
			"width": 80
		},
		{
			"label": _("Grand Total"),
			"fieldname": "grand_total",
			"fieldtype": "Currency",
			"width": 80
		}

	]

def get_data(filters, columns):
	sales_invoice_data = []
	sales_invoice_data = get_sales_invoice_data(filters)
	return sales_invoice_data

def get_sales_invoice_data(filters):
	conditions = ""
	if filters.get("from_date" and "to_date"):
		conditions += "where a.posting_date>=%(from_date)s and a.posting_date<=%(to_date)s"

		item_results = frappe.db.sql("""select a.name as Name, a.status as Status,
			a.customer_name as `Customer Name`, a.territory as Territory, a.union_europeen as `Union Europeen`, a.tax_id as` Tax ID`, a.shipping_rule as `Shipping Rule`, a.total_taxes_and_charges as `Total T&C`,
			a.total_ht as `Total HT`, a.grand_total as `Grand Total`
			from `tabSales Invoice` a
			{conditions}"""
			.format(conditions=conditions), filters, as_dict=1)



	return item_results

#3

remove as_dict=1 from sql query.


#4

@Maheshwari_Bhavesh when i remove it, even the empty rows disappear.


#5

what if you use as_list=1 instead? At least this will help when trying to get content to Excel…


#6

@lasalesi thank you!


#7

You are welcome :wink:


#8