Script Report not working

ERPNext Reports
1

While creating a script report, the Report result is not displaying. filters are working,
I’m in Developer mode, Report window result is No data to display. I need a help for creating script report

2

Please post your code here so we can help you fix errors

3

Report is not triggering while refresh

from __future__ import unicode_literals
import frappe
from frappe.utils import getdate, add_days, today, cint
from frappe import _

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

def get_columns():
	columns = [
		{
			"fieldname": "territory",
			"fieldtype": "Link",
			"label": _("Territory1"),
			"options": "Territory1",
			"width": 150
		},
		{
			"fieldname": "item_group",
			"fieldtype": "Link",
			"label": _("Item Group"),
			"options": "Item Group",
			"width": 150
		},
		{
			"fieldname": "item",
			"fieldtype": "Link",
			"options": "Item",
			"label": "Item",
			"width": 150
		},
		{
			"fieldname": "item_name",
			"fieldtype": "Data",
			"label": _("Item Name"),
			"width": 150
		},

		{
			"fieldname": "customer",
			"fieldtype": "Link",
			"label": _("Customer"),
			"options": "Customer",
			"width": 200
		},
		{
			"fieldname": "last_order_date",
			"fieldtype": "Date",
			"label": _("Last Order Date"),
			"width": 100
		},
		{
			"fieldname": "qty",
			"fieldtype": "Float",
			"label": _("Quantity"),
			"width": 100
		},
		{
			"fieldname": "days_since_last_order",
			"fieldtype": "Int",
			"label": _("Days Since Last Order"),
			"width": 100
		},
	]

	return columns


def get_data(filters):
	data = []
	items = get_items(filters)
	territories = get_territories(filters)
	sales_invoice_data = get_sales_details(filters)

	for territory in territories:
		for item in items:
			row = {
				"territory": territory.name,
				"item_group": item.item_group,
				"item": item.item_code,
				"item_name": item.item_name
			}

			if sales_invoice_data.get((territory.name,item.item_code)):
				item_obj = sales_invoice_data[(territory.name,item.item_code)]
				if item_obj.days_since_last_order > cint(filters['days']):
					row.update({
						"territory": item_obj.territory,
						"customer": item_obj.customer,
						"last_order_date": item_obj.last_order_date,
						"qty": item_obj.qty,
						"days_since_last_order": item_obj.days_since_last_order
					})
				else:
					continue

			data.append(row)

	return data


def get_sales_details(filters):
	data = []
	item_details_map = {}

	date_field = "s.transaction_date" if filters["based_on"] == "Sales Order" else "s.posting_date"

	sales_data = frappe.db.sql("""
		select s.territory, s.customer, si.item_group, si.item_code, si.qty, {date_field} as last_order_date,
		DATEDIFF(CURDATE(), {date_field}) as days_since_last_order
		from `tab{doctype}` s, `tab{doctype} Item` si
		where s.name = si.parent and s.docstatus = 1
		order by days_since_last_order """ #nosec
		.format(date_field = date_field, doctype = filters['based_on']), as_dict=1)

	for d in sales_data:
		item_details_map.setdefault((d.territory,d.item_code), d)

	return item_details_map

def get_territories(filters):

	filter_dict = {}
	if filters.get("territory"):
		filter_dict.update({'name': filters['territory']})

	territories = frappe.get_all("Territory", fields=["name"], filters=filter_dict)

	return territories

def get_items(filters):
	filters_dict = {
		"disabled": 0,
		"is_stock_item": 1
	}

	if filters.get("item_group"):
		filters_dict.update({
			"item_group": filters["item_group"]
		})

	if filters.get("item"):
		filters_dict.update({
			"name": filters["item"]
		})

	items = frappe.get_all("Item", fields=["name", "item_group", "item_name", "item_code"], filters=filters_dict, order_by="name")

	return items
4

Hi,
Please check the data that you are returning to the report, and its format.
Format of data should be in list of list and not list of dictionary.

2 Likes