Script report Filter not showing

[Usman-Kanz]

Information about bug

Hi Community,

I have created a script report and it is not working, not generating any report. Also the filters do not appear on the report see images below.

JS:

// apps/soa/soa/report/statement_of_accounts/statement_of_accounts.js

frappe.query_reports["Statement Of Accounts"] = {
"filters": [
{
"fieldname": "customer",
"label": __("Customer"),
"fieldtype": "Link",
"options": "Customer",
"reqd": 1
},
{
"fieldname": "to_date",
"label": __("Statement Date"),
"fieldtype": "Date",
"default": frappe.datetime.get_today(),
"reqd": 1
}
]
};

JSON:

{
"report_name": "Statement Of Accounts",
"ref_doctype": "Sales Invoice",
"report_type": "Script Report",
"is_standard": "No",
"print_format": "Customer Statement Format",
"filters": [
{
"fieldname": "customer",
"label": "Customer",
"fieldtype": "Link",
"options": "Customer",
"reqd": 1
},
{
"fieldname": "to_date",
"label": "Statement Date",
"fieldtype": "Date",
"default": "Today",
"reqd": 1
}
]
}

Python:

`import frappe
from frappe.utils import date_diff

def execute(filters=None):
to_date = filters.get("to_date")
customer = filters.get("customer")

data = frappe.db.sql("""
    SELECT
      posting_date   AS invoice_date,
      due_date,
      name           AS invoice_number,
      DATEDIFF(%(to_date)s, posting_date) AS age,
      po_no          AS purchase_order,
      delivery_note  AS delivery_note,
      grand_total    AS invoice_amount,
      outstanding_amount AS balance_amount
    FROM `tabSales Invoice`
    WHERE customer = %(customer)s
      AND docstatus = 1
      AND posting_date <= %(to_date)s
    ORDER BY posting_date
""", {"to_date": to_date, "customer": customer}, as_dict=1)

columns = [
  {"label":"Invoice Date","fieldname":"invoice_date","fieldtype":"Date","width":100},
  {"label":"Due Date","fieldname":"due_date","fieldtype":"Date","width":100},
  {"label":"Invoice Number","fieldname":"invoice_number","fieldtype":"Data","width":120},
  {"label":"Age","fieldname":"age","fieldtype":"Int","width":60},
  {"label":"Purchase Order","fieldname":"purchase_order","fieldtype":"Data","width":100},
  {"label":"Delivery Note","fieldname":"delivery_note","fieldtype":"Data","width":100},
  {"label":"Invoice Amount","fieldname":"invoice_amount","fieldtype":"Currency","width":100},
  {"label":"Balance Amount","fieldname":"balance_amount","fieldtype":"Currency","width":100}
]

return columns, data`

I am trying to make statement report similar to the below photos.

Invoice and details ( | Invoice Date | Due Date | Invoice Number | Age | Purchase Order | Delivery Note | Invoice Amount | Balance Amount | )

Aging Summary ( | 0–30 Days | 31–60 Days | 61–90 Days | 91–120 Days | Above 120 Days |

Report I need without grid:

SQL (which i am currently using):
SELECT DATE_FORMAT(SI.posting_date, '%%d-%%m-%%Y') AS Invoice Date, DATE_FORMAT(SI.due_date, '%%d-%%m-%%Y') AS Due Date, SI.name AS Invoice Number, DATEDIFF(CURDATE(), SI.posting_date) AS Age, SI.po_no AS Purchase Order, IFNULL(DND.delivery_note, '') AS Delivery Note, FORMAT(SI.grand_total, 2) AS Invoice Amount, FORMAT(SI.outstanding_amount, 2) AS Balance AmountFROMtabSales InvoiceSI LEFT JOIN ( SELECT parent AS sales_invoice, GROUP_CONCAT(DISTINCT delivery_note SEPARATOR ', ') AS delivery_note FROMtabSales Invoice Item`
WHERE delivery_note IS NOT NULL
GROUP BY parent
) DND ON DND.sales_invoice = SI.name
WHERE
SI.docstatus = 1
AND SI.customer = %(customer)s
AND SI.posting_date <= COALESCE(%(to_date)s, CURDATE())
AND SI.outstanding_amount > 0

UNION ALL

-- Total row
SELECT
'Total' AS Invoice Date,
'' AS Due Date,
'' AS Invoice Number,
'' AS Age,
'' AS Purchase Order,
'' AS Delivery Note,
CONCAT('AED ', FORMAT(SUM(SI.grand_total), 2), '') AS Invoice Amount,
CONCAT('AED ', FORMAT(SUM(SI.outstanding_amount),2), '') AS Balance Amount
FROM tabSales Invoice SI
WHERE
SI.docstatus = 1
AND SI.customer = %(customer)s
AND SI.posting_date <= COALESCE(%(to_date)s, CURDATE())
AND SI.outstanding_amount > 0

UNION ALL

-- Empty spacer after Total
SELECT
'' AS Invoice Date,
'' AS Due Date,
'' AS Invoice Number,
'' AS Age,
'' AS Purchase Order,
'' AS Delivery Note,
'' AS Invoice Amount,
'' AS Balance Amount

UNION ALL

-- Aging Summary header
SELECT
'Aging Summary' AS Invoice Date,
'' AS Due Date,
'' AS Invoice Number,
'' AS Age,
'' AS Purchase Order,
'' AS Delivery Note,
'' AS Invoice Amount,
'' AS Balance Amount

UNION ALL

-- Aging category labels
SELECT
'0–30 Days' AS Invoice Date,
'31–60 Days' AS Due Date,
'61–90 Days' AS Invoice Number,
'91–120 Days' AS Age,
'Above 120 Days' AS Purchase Order,
'' AS Delivery Note,
'' AS Invoice Amount,
'' AS Balance Amount

UNION ALL

-- Aging amounts row
SELECT
CONCAT('AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 0 AND 30 THEN SI.outstanding_amount ELSE 0 END),2), ''),
CONCAT('AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 31 AND 60 THEN SI.outstanding_amount ELSE 0 END),2), ''),
CONCAT('AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 61 AND 90 THEN SI.outstanding_amount ELSE 0 END),2), ''),
CONCAT('AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) BETWEEN 91 AND 120 THEN SI.outstanding_amount ELSE 0 END),2), ''),
CONCAT('AED ', FORMAT(SUM(CASE WHEN DATEDIFF(CURDATE(), SI.posting_date) > 120 THEN SI.outstanding_amount ELSE 0 END),2), ''),
'' AS Delivery Note,
'' AS Invoice Amount,
'' AS Balance Amount
FROM tabSales Invoice SI
WHERE
SI.docstatus = 1
AND SI.customer = %(customer)s
AND SI.posting_date <= COALESCE(%(to_date)s, CURDATE())
AND SI.outstanding_amount > 0

ORDER BY
CASE
WHEN Invoice Date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' THEN 0 -- invoice rows
WHEN Invoice Date = 'Total' THEN 1 -- total
WHEN Invoice Date = '' THEN 2 -- spacer
WHEN Invoice Date = 'Aging Summary' THEN 3 -- header
WHEN Invoice Date IN (
'0–30 Days','31–60 Days','61–90 Days','91–120 Days','Above 120 Days'
) THEN 4 -- labels
ELSE 5 -- aging amounts
END,
CASE
WHEN Invoice Date REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$'
THEN STR_TO_DATE(Invoice Date, '%%d-%%m-%%Y')
ELSE NULL
END,
Invoice Number;`

Module

accounts

Version

ERPNext: v15.52.0
Frappe Framework: v15.56.0

Installation method

manual install

Relevant log output / Stack trace / Full Error Message.

[Usman-Kanz]

Created Issue on Frappe Forum: https://discuss.frappe.io/t/script-report-filter-not-showing/147352?u=usman-kanz