@TalhaButt2508
If you are using a Query Report, then you’ll need to follow the workaround I shared in my earlier response. However, if that doesn’t work well, I recommend switching to Report Type = “Script Report”. With Script Reports, you have full control through script files. You can add filters, handle empty values, and manage all possible conditions more effectively.
While you can refer to the official documentation for details, however, here are quick steps to create a sample Script Report for sales invoices in which I created several filters in it and shows a perfect result.
Hope this will help.
Example
Report Name: Custom Sales Invoice Report
Steps:
- Create a new report → Report name = “Custom Sales Invoice Report”, Report Type = “Script Report”, assign the desired roles, and select the app module.
- Modify/create the JS file located in
{yourapp}/report/custom_sales_invoice_report.js and define the filters (see sample JS code below).
- Modify/create the Python file located in
{yourapp}/report/custom_sales_invoice_report.py and implement the filter logic (see sample Python code below).
- Run:
bench migrate and bench clear-cache.
- Your report is ready.
Script References
Custom Report:
Custom Report View:
JS File (Sample):
// Copyright (c) 2025, MyCustomModule and contributors
// For license information, please see license.txt
frappe.query_reports["Custom Sales Invoice Report"] = {
filters: [
{
fieldname: "from_date",
label: __("From Date"),
fieldtype: "Date",
default: frappe.datetime.add_months(frappe.datetime.get_today(), -1),
reqd: 1,
width: "80"
},
{
fieldname: "to_date",
label: __("To Date"),
fieldtype: "Date",
default: frappe.datetime.get_today(),
reqd: 1
},
{
fieldname: "company",
label: __("Company"),
fieldtype: "Link",
options: "Company",
default: frappe.defaults.get_user_default("Company"),
reqd: 1
},
{
fieldname: "customer",
label: __("Customer"),
fieldtype: "Link",
options: "Customer"
},
{
fieldname: "customer_group",
label: __("Customer Group"),
fieldtype: "Link",
options: "Customer Group"
},
{
fieldname: "territory",
label: __("Territory"),
fieldtype: "Link",
options: "Territory"
},
{
fieldname: "sales_person",
label: __("Sales Person"),
fieldtype: "Link",
options: "Sales Person"
},
{
fieldname: "item_code",
label: __("Item Code"),
fieldtype: "Link",
options: "Item"
},
{
fieldname: "item_group",
label: __("Item Group"),
fieldtype: "Link",
options: "Item Group"
},
{
fieldname: "brand",
label: __("Brand"),
fieldtype: "Link",
options: "Brand"
},
{
fieldname: "status",
label: __("Status"),
fieldtype: "Select",
options: [
"",
"Draft",
"Submitted",
"Paid",
"Partly Paid",
"Unpaid",
"Overdue",
"Cancelled",
"Return"
]
},
{
fieldname: "payment_status",
label: __("Payment Status"),
fieldtype: "Select",
options: [
"",
"Outstanding",
"Paid",
"Overdue"
]
},
{
fieldname: "project",
label: __("Project"),
fieldtype: "Link",
options: "Project"
},
{
fieldname: "cost_center",
label: __("Cost Center"),
fieldtype: "Link",
options: "Cost Center"
},
{
fieldname: "warehouse",
label: __("Warehouse"),
fieldtype: "Link",
options: "Warehouse"
},
{
fieldname: "mode_of_payment",
label: __("Mode of Payment"),
fieldtype: "Link",
options: "Mode of Payment"
},
{
fieldname: "currency",
label: __("Currency"),
fieldtype: "Link",
options: "Currency"
},
{
fieldname: "is_return",
label: __("Is Return"),
fieldtype: "Check"
},
{
fieldname: "is_pos",
label: __("Is POS"),
fieldtype: "Check"
},
{
fieldname: "outstanding_only",
label: __("Outstanding Only"),
fieldtype: "Check"
},
{
fieldname: "overdue_only",
label: __("Overdue Only"),
fieldtype: "Check"
},
{
fieldname: "min_grand_total",
label: __("Min Grand Total"),
fieldtype: "Currency"
},
{
fieldname: "max_grand_total",
label: __("Max Grand Total"),
fieldtype: "Currency"
},
{
fieldname: "group_by",
label: __("Group By"),
fieldtype: "Select",
options: [
"",
"Customer",
"Customer Group",
"Territory",
"Item Group",
"Sales Person",
"Month",
"Quarter"
]
},
{
fieldname: "show_item_details",
label: __("Show Item Details"),
fieldtype: "Check",
default: 0
}
],
formatter: function(value, row, column, data, default_formatter) {
value = default_formatter(value, row, column, data);
// Highlight overdue invoices in red
if (column.fieldname == "days_overdue" && data && data.days_overdue > 0) {
value = "<span style='color:red;font-weight:bold'>" + value + "</span>";
}
// Highlight paid invoices in green
if (column.fieldname == "status" && data && data.status == "Paid") {
value = "<span style='color:green;font-weight:bold'>" + value + "</span>";
}
// Highlight cancelled invoices in gray
if (column.fieldname == "status" && data && data.status == "Cancelled") {
value = "<span style='color:gray;font-style:italic'>" + value + "</span>";
}
return value;
},
onload: function(report) {
// Add custom buttons
report.page.add_inner_button(__("Export to Excel"), function() {
frappe.utils.csvify(report.data, report.columns, report.report_name);
});
report.page.add_inner_button(__("Print Summary"), function() {
var print_format = "Standard";
frappe.utils.print(
"Report",
report.report_name,
print_format
);
});
}
};
JSON File (Sample):
{
"add_total_row": 1,
"apply_user_permissions": 1,
"creation": "2025-01-27 12:00:00.000000",
"disabled": 0,
"docstatus": 0,
"doctype": "Report",
"idx": 0,
"is_standard": "Yes",
"letter_head": "Standard Letter Head",
"modified": "2025-01-27 12:00:00.000000",
"modified_by": "Administrator",
"module": "MyModule",
"name": "Custom Sales Invoice Report",
"owner": "Administrator",
"prepared_report": 0,
"ref_doctype": "Sales Invoice",
"report_name": "Custom Sales Invoice Report",
"report_type": "Script Report",
"roles": [
{
"role": "Sales User"
},
{
"role": "Sales Manager"
},
{
"role": "Accounts User"
},
{
"role": "Accounts Manager"
},
{
"role": "System Manager"
}
],
"timeout": 300
}
Python File (Sample):
# Copyright (c) 2025, MyCustomModule and contributors
# For license information, please see license.txt
import frappe
from frappe import _
from frappe.utils import getdate, today, flt, cint
def execute(filters=None):
"""Execute the Custom Sales Invoice Report"""
if not filters:
filters = {}
# Validate required filters
validate_filters(filters)
# Get columns based on filters
columns = get_columns(filters)
# Get data based on filters
data = get_data(filters)
# Apply grouping if specified
if filters.get("group_by"):
data = apply_grouping(data, filters)
# Add summary row if needed
if filters.get("add_summary"):
data = add_summary_row(data, columns)
return columns, data
def validate_filters(filters):
"""Validate required filters"""
if not filters.get("from_date"):
frappe.throw(_("From Date is required"))
if not filters.get("to_date"):
frappe.throw(_("To Date is required"))
if not filters.get("company"):
frappe.throw(_("Company is required"))
# Validate date range
if getdate(filters.get("from_date")) > getdate(filters.get("to_date")):
frappe.throw(_("From Date cannot be greater than To Date"))
def get_columns(filters):
"""Define report columns based on filters"""
columns = [
{
"fieldname": "name",
"label": _("Invoice No"),
"fieldtype": "Link",
"options": "Sales Invoice",
"width": 140
},
{
"fieldname": "posting_date",
"label": _("Date"),
"fieldtype": "Date",
"width": 100
},
{
"fieldname": "customer",
"label": _("Customer"),
"fieldtype": "Link",
"options": "Customer",
"width": 150
},
{
"fieldname": "customer_name",
"label": _("Customer Name"),
"fieldtype": "Data",
"width": 200
},
{
"fieldname": "customer_group",
"label": _("Customer Group"),
"fieldtype": "Link",
"options": "Customer Group",
"width": 130
},
{
"fieldname": "territory",
"label": _("Territory"),
"fieldtype": "Link",
"options": "Territory",
"width": 120
}
]
# Add item details columns if requested
if filters.get("show_item_details"):
columns.extend([
{
"fieldname": "item_code",
"label": _("Item Code"),
"fieldtype": "Link",
"options": "Item",
"width": 120
},
{
"fieldname": "item_name",
"label": _("Item Name"),
"fieldtype": "Data",
"width": 150
},
{
"fieldname": "qty",
"label": _("Qty"),
"fieldtype": "Float",
"width": 80
},
{
"fieldname": "rate",
"label": _("Rate"),
"fieldtype": "Currency",
"width": 100
}
])
# Add standard financial columns
columns.extend([
{
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Link",
"options": "Currency",
"width": 80
},
{
"fieldname": "net_total",
"label": _("Net Total"),
"fieldtype": "Currency",
"width": 120
},
{
"fieldname": "tax_amount",
"label": _("Tax Amount"),
"fieldtype": "Currency",
"width": 120
},
{
"fieldname": "grand_total",
"label": _("Grand Total"),
"fieldtype": "Currency",
"width": 120
},
{
"fieldname": "outstanding_amount",
"label": _("Outstanding"),
"fieldtype": "Currency",
"width": 120
},
{
"fieldname": "paid_amount",
"label": _("Paid Amount"),
"fieldtype": "Currency",
"width": 120
},
{
"fieldname": "status",
"label": _("Status"),
"fieldtype": "Data",
"width": 100
},
{
"fieldname": "due_date",
"label": _("Due Date"),
"fieldtype": "Date",
"width": 100
},
{
"fieldname": "days_overdue",
"label": _("Days Overdue"),
"fieldtype": "Int",
"width": 100
}
])
# Add additional columns based on filters
if filters.get("sales_person"):
columns.append({
"fieldname": "sales_person",
"label": _("Sales Person"),
"fieldtype": "Link",
"options": "Sales Person",
"width": 120
})
if filters.get("project"):
columns.append({
"fieldname": "project",
"label": _("Project"),
"fieldtype": "Link",
"options": "Project",
"width": 120
})
if filters.get("cost_center"):
columns.append({
"fieldname": "cost_center",
"label": _("Cost Center"),
"fieldtype": "Link",
"options": "Cost Center",
"width": 120
})
# Add remarks column
columns.append({
"fieldname": "remarks",
"label": _("Remarks"),
"fieldtype": "Data",
"width": 150
})
return columns
def get_data(filters):
"""Get filtered data based on user inputs"""
# Base query for sales invoices
if filters.get("show_item_details"):
# Query with item details
query = """
SELECT
si.name,
si.posting_date,
si.customer,
si.customer_name,
si.customer_group,
si.territory,
si.currency,
si.net_total,
si.total_taxes_and_charges as tax_amount,
si.grand_total,
si.outstanding_amount,
(si.grand_total - si.outstanding_amount) as paid_amount,
si.status,
si.due_date,
si.project,
si.cost_center,
si.remarks,
si.is_return,
si.is_pos,
sii.item_code,
sii.item_name,
sii.qty,
sii.rate,
st.sales_person
FROM `tabSales Invoice` si
LEFT JOIN `tabSales Invoice Item` sii ON si.name = sii.parent
LEFT JOIN `tabSales Team` st ON si.name = st.parent AND st.parenttype = 'Sales Invoice'
WHERE si.docstatus = 1
"""
else:
# Query without item details
query = """
SELECT
si.name,
si.posting_date,
si.customer,
si.customer_name,
si.customer_group,
si.territory,
si.currency,
si.net_total,
si.total_taxes_and_charges as tax_amount,
si.grand_total,
si.outstanding_amount,
(si.grand_total - si.outstanding_amount) as paid_amount,
si.status,
si.due_date,
si.project,
si.cost_center,
si.remarks,
si.is_return,
si.is_pos,
st.sales_person
FROM `tabSales Invoice` si
LEFT JOIN `tabSales Team` st ON si.name = st.parent AND st.parenttype = 'Sales Invoice'
WHERE si.docstatus = 1
"""
# Build conditions
conditions = get_conditions(filters)
if conditions:
query += " AND " + conditions
# Add ordering
query += " ORDER BY si.posting_date DESC, si.name DESC"
# Execute query
data = frappe.db.sql(query, filters, as_dict=1)
# Post-process data
for row in data:
# Calculate days overdue
if row.get('due_date') and row.get('outstanding_amount', 0) > 0:
due_date = getdate(row['due_date'])
current_date = getdate(today())
if due_date < current_date:
row['days_overdue'] = (current_date - due_date).days
else:
row['days_overdue'] = 0
else:
row['days_overdue'] = 0
# Format currency fields
currency_fields = ['net_total', 'tax_amount', 'grand_total', 'outstanding_amount', 'paid_amount', 'rate']
for field in currency_fields:
if row.get(field):
row[field] = flt(row[field], 2)
# Format quantity
if row.get('qty'):
row['qty'] = flt(row['qty'], 3)
return data
def get_conditions(filters):
"""Build WHERE conditions based on filters"""
conditions = []
# Date range filters
if filters.get("from_date"):
conditions.append("si.posting_date >= %(from_date)s")
if filters.get("to_date"):
conditions.append("si.posting_date <= %(to_date)s")
# Company filter
if filters.get("company"):
conditions.append("si.company = %(company)s")
# Customer filters
if filters.get("customer"):
conditions.append("si.customer = %(customer)s")
if filters.get("customer_group"):
conditions.append("si.customer_group = %(customer_group)s")
if filters.get("territory"):
conditions.append("si.territory = %(territory)s")
# Sales person filter
if filters.get("sales_person"):
conditions.append("st.sales_person = %(sales_person)s")
# Item filters (only applicable when showing item details)
if filters.get("show_item_details") and filters.get("item_code"):
conditions.append("sii.item_code = %(item_code)s")
# Note: item_group and brand filters are not directly available in the query
# They would require additional joins to Item table if needed
# Status filters
if filters.get("status"):
conditions.append("si.status = %(status)s")
# Payment status filters
if filters.get("payment_status"):
if filters.get("payment_status") == "Outstanding":
conditions.append("si.outstanding_amount > 0")
elif filters.get("payment_status") == "Paid":
conditions.append("si.outstanding_amount = 0")
elif filters.get("payment_status") == "Overdue":
conditions.append("si.due_date < CURDATE() AND si.outstanding_amount > 0")
# Project and cost center filters
if filters.get("project"):
conditions.append("si.project = %(project)s")
if filters.get("cost_center"):
conditions.append("si.cost_center = %(cost_center)s")
# Currency filter
if filters.get("currency"):
conditions.append("si.currency = %(currency)s")
# Boolean filters
if filters.get("is_return"):
conditions.append("si.is_return = 1")
if filters.get("is_pos"):
conditions.append("si.is_pos = 1")
if filters.get("outstanding_only"):
conditions.append("si.outstanding_amount > 0")
if filters.get("overdue_only"):
conditions.append("si.due_date < CURDATE() AND si.outstanding_amount > 0")
# Grand total range filters
if filters.get("min_grand_total"):
conditions.append("si.grand_total >= %(min_grand_total)s")
if filters.get("max_grand_total"):
conditions.append("si.grand_total <= %(max_grand_total)s")
return " AND ".join(conditions) if conditions else ""
def apply_grouping(data, filters):
"""Apply grouping to the data if specified"""
group_by = filters.get("group_by")
if not group_by:
return data
# Implementation for different grouping options
# This is a simplified version - you can expand based on requirements
if group_by == "Customer":
# Group by customer and add subtotals
pass
elif group_by == "Month":
# Group by month and add subtotals
pass
return data
def add_summary_row(data, columns):
"""Add summary row with totals"""
if not data:
return data
# Calculate totals
total_net = sum(flt(row.get('net_total', 0)) for row in data)
total_tax = sum(flt(row.get('tax_amount', 0)) for row in data)
total_grand = sum(flt(row.get('grand_total', 0)) for row in data)
total_outstanding = sum(flt(row.get('outstanding_amount', 0)) for row in data)
total_paid = sum(flt(row.get('paid_amount', 0)) for row in data)
# Create summary row
summary_row = {
'name': 'Total',
'customer_name': f'Total ({len(data)} invoices)',
'net_total': total_net,
'tax_amount': total_tax,
'grand_total': total_grand,
'outstanding_amount': total_outstanding,
'paid_amount': total_paid
}
data.append(summary_row)
return data
