Create a Script report with 'is standard' as 'no'

ERPNext
1

Hi! I’m trying to create a report that shows some KPIs. I’m doing a test with just one KPI wich divides Current assets by current liabilities.

Table must include the following columns: Çategoria, Indicador, Valor al periodo, valor de referencia

To create the report I’m using the following configurations:
Report type: Script
Ref DocType: Account
Running: ERPNext v15.23 self-hosted.

image
image1836×1348 148 KB

As far as I undertand with a script report that is not standard you can in v15 add the script and java in the report UI but after adding the script and filters whatever I do the report always says ‘Nothing to show’

Script I’m using is:

def execute(filters=None):
    if not filters or not filters.get("fecha"):
        frappe.throw("Por favor selecciona una fecha para el reporte.")

    fecha = filters.get("fecha")
    empresa = filters.get("empresa") or frappe.defaults.get_user_default("Company")

    def obtener_balance(cuenta_padre, es_pasivo=False):
        cuenta_info = frappe.get_all("Account", filters={"name": cuenta_padre}, fields=["lft", "rgt"])
        if not cuenta_info:
            return 0

        limites = cuenta_info[0]
        cuentas = frappe.get_all("Account", filters={
            "lft": [">", limites.lft],
            "rgt": ["<", limites.rgt],
            "is_group": 0,
            "company": empresa
        }, fields=["name"])

        cuentas_hoja = [c.name for c in cuentas]
        if not cuentas_hoja:
            return 0

        resultado = frappe.db.sql("""
            SELECT SUM(debit - credit) AS balance
            FROM `tabGL Entry`
            WHERE account IN %(cuentas)s
              AND posting_date <= %(fecha)s
              AND company = %(empresa)s
        """, {
            "cuentas": tuple(cuentas_hoja),
            "fecha": fecha,
            "empresa": empresa
        }, as_dict=True)

        balance = resultado[0]["balance"] if resultado and resultado[0]["balance"] is not None else 0
        return abs(balance) if es_pasivo else balance

    # Indicadores base
    indicadores = []

    activos_corrientes = obtener_balance("1.1 - Activos corriente - IP")
    pasivos_corrientes = obtener_balance("2.1 - Pasivo corriente - IP", es_pasivo=True)
    razon_corriente = activos_corrientes / pasivos_corrientes if pasivos_corrientes else 0

    indicadores.append({
        "categoria": "Liquidez",
        "indicador": "Razón corriente",
        "valor": razon_corriente,
        "referencia": 1.2
    })

    # Filtros dinámicos
    if filters.get("categoria"):
        indicadores = [i for i in indicadores if i["categoria"] == filters["categoria"]]
    if filters.get("indicador"):
        indicadores = [i for i in indicadores if i["indicador"] == filters["indicador"]]

    columns = [
        {"label": "Categoría", "fieldname": "categoria", "fieldtype": "Data", "width": 150},
        {"label": "Indicador", "fieldname": "indicador", "fieldtype": "Data", "width": 200},
        {"label": "Valor al período", "fieldname": "valor", "fieldtype": "Float", "width": 150},
        {"label": "Valor de referencia", "fieldname": "referencia", "fieldtype": "Float", "width": 150}
    ]

    return columns, indicadores or [{"categoria": "N/A", "indicador": "Sin datos", "valor": 0, "referencia": 0}]

I’d like to know what I should do since I have tested a couple of things and I don’t seems to have the report to read the script and generate the report.

I’m using chatgpt to help me with the code but since documentations is so lacky I couldn’t figure out what is wrong of feed the AI with proper guidance.

Thanks in advance!

2

are you calling execute after your method definition… like

def execute(filters=None):
    ........


result = execute(filters)

You screenshot is clipped, so can’t tell.

If you are setting the result and still getting nothing, then check the function in bench console, or the ‘System Console’ page

3

Try:
Send output as result = [result] , or for old style data = [columns], [result]

4

I managed to get the right result with this script in the system console:

def obtener_balance(cuenta_padre, fecha, empresa, es_pasivo=False):
    cuenta_info = frappe.get_all("Account", filters={"name": cuenta_padre}, fields=["lft", "rgt"])
    if not cuenta_info:
        log(f"Cuenta no encontrada: {cuenta_padre}")
        return 0

    limites = cuenta_info[0]
    cuentas = frappe.get_all("Account", filters={
        "lft": [">", limites["lft"]],
        "rgt": ["<", limites["rgt"]],
        "is_group": 0,
        "company": empresa
    }, fields=["name"])

    cuentas_hoja = [c["name"] for c in cuentas]
    if not cuentas_hoja:
        log(f"Sin cuentas hoja: {cuenta_padre}")
        return 0

    resultado = frappe.db.sql("""
        SELECT SUM(debit - credit) AS balance
        FROM `tabGL Entry`
        WHERE account IN %(cuentas)s
          AND posting_date <= %(fecha)s
          AND company = %(empresa)s
    """, {
        "cuentas": tuple(cuentas_hoja),
        "fecha": fecha,
        "empresa": empresa
    }, as_dict=True)

    balance = resultado[0]["balance"] if resultado and resultado[0]["balance"] is not None else 0
    return abs(balance) if es_pasivo else balance

# Parámetros
fecha = "2025-05-31"
empresa = "Isy Pisy"

# Cálculo
activos_corrientes = obtener_balance("1.1 - Activos corriente - IP", fecha, empresa)
pasivos_corrientes = obtener_balance("2.1 - Pasivo corriente - IP", fecha, empresa, es_pasivo=True)

razon_corriente = (
    activos_corrientes / pasivos_corrientes
    if isinstance(activos_corrientes, (int, float)) and isinstance(pasivos_corrientes, (int, float)) and pasivos_corrientes != 0
    else "N/A"
)

# Mostrar resultados
log(f"Activos Corrientes: {activos_corrientes}")
log(f"Pasivos Corrientes: {pasivos_corrientes}")
log(f"Razón Corriente: {razon_corriente}")

Values are correct here:

image
image1177×664 31.8 KB

Nonetheless when I used the version for the report it still says ‘Nothing to show’. This is the script in the report section:

def execute(filters=None):
    if not filters or not filters.get("fecha"):
        frappe.throw("Por favor selecciona una fecha para el reporte.")
    
    fecha = filters.get("fecha")
    empresa = filters.get("empresa") or frappe.defaults.get_user_default("Company")

    def obtener_balance(cuenta_padre, es_pasivo=False):
        cuenta_info = frappe.get_all("Account", filters={"name": cuenta_padre}, fields=["lft", "rgt"])
        if not cuenta_info:
            return 0

        limites = cuenta_info[0]
        cuentas = frappe.get_all("Account", filters={
            "lft": [">", limites["lft"]],
            "rgt": ["<", limites["rgt"]],
            "is_group": 0,
            "company": empresa
        }, fields=["name"])

        cuentas_hoja = [c["name"] for c in cuentas]
        if not cuentas_hoja:
            return 0

        resultado = frappe.db.sql("""
            SELECT SUM(debit - credit) AS balance
            FROM `tabGL Entry`
            WHERE account IN %(cuentas)s
              AND posting_date <= %(fecha)s
              AND company = %(empresa)s
        """, {
            "cuentas": tuple(cuentas_hoja),
            "fecha": fecha,
            "empresa": empresa
        }, as_dict=True)

        balance = resultado[0]["balance"] if resultado and resultado[0]["balance"] is not None else 0
        return abs(balance) if es_pasivo else balance

    # Calcular indicadores
    activos_corrientes = obtener_balance("1.1 - Activos corriente - IP")
    pasivos_corrientes = obtener_balance("2.1 - Pasivo corriente - IP", es_pasivo=True)
    razon_corriente = (
        activos_corrientes / pasivos_corrientes
        if pasivos_corrientes else 0
    )

    indicadores = [{
        "categoria": "Liquidez",
        "indicador": "Razón corriente",
        "valor": razon_corriente,
        "referencia": 1.2
    }]

    # Filtros opcionales
    if filters.get("categoria"):
        indicadores = [i for i in indicadores if i["categoria"] == filters["categoria"]]
    if filters.get("indicador"):
        indicadores = [i for i in indicadores if i["indicador"] == filters["indicador"]]

    # Columnas del reporte
    columns = [
        {"label": "Categoría", "fieldname": "categoria", "fieldtype": "Data", "width": 150},
        {"label": "Indicador", "fieldname": "indicador", "fieldtype": "Data", "width": 200},
        {"label": "Valor al período", "fieldname": "valor", "fieldtype": "Float", "width": 150},
        {"label": "Valor de referencia", "fieldname": "referencia", "fieldtype": "Float", "width": 150}
    ]

    return columns, indicadores or [{"categoria": "N/A", "indicador": "Sin datos", "valor": 0, "referencia": 0}]

Filters per image:

image
image934×214 12 KB

I’m still getting ‘Nothing to show’

image
image1851×826 55.9 KB

5

@enavash

use the data = columns, rows syntax

move the code out of the execute method and just return columns , rows. Also don’t use data or result as variable names in your code! Thats why I have used ‘rows’

image
image2438×1402 244 KB

image
image2754×646 60.1 KB

1 Like
6

To use the result = [{...},{....}] syntax, you have to define columns in the columns table, and assign only the rows to result!! :astonished:

image
image2124×1430 194 KB

1 Like
7

Thanks for that suggestion. It worked! Now I’m having problems with filters. I set a couple of custom filters but they are not working as expected. I can select them but filter won’t apply

This is my current code:

# Validación de filtros
fecha = filters.get("fecha")
empresa = filters.get("empresa") or frappe.defaults.get_user_default("Company")
categoria = frappe.form_dict.get("categoria")
indicador = frappe.form_dict.get("indicador")

if not fecha:
    frappe.throw("Por favor selecciona una fecha para el reporte.")

# Función para obtener el balance contable desde una cuenta padre
def obtener_balance(cuenta_padre, fecha, empresa, es_pasivo=False):
    cuenta_info = frappe.get_all("Account", filters={"name": cuenta_padre}, fields=["lft", "rgt"])
    if not cuenta_info:
        frappe.msgprint(f"Cuenta no encontrada: {cuenta_padre}")
        return 0

    limites = cuenta_info[0]
    cuentas = frappe.get_all("Account", filters={
        "lft": [">", limites["lft"]],
        "rgt": ["<", limites["rgt"]],
        "is_group": 0,
        "company": empresa
    }, fields=["name"])

    cuentas_hoja = [c["name"] for c in cuentas]
    if not cuentas_hoja:
        frappe.msgprint(f"Sin cuentas hoja para: {cuenta_padre}")
        return 0

    resultado = frappe.db.sql("""
        SELECT SUM(debit - credit) AS balance
        FROM `tabGL Entry`
        WHERE account IN %(cuentas)s
          AND posting_date <= %(fecha)s
          AND company = %(empresa)s
    """, {
        "cuentas": tuple(cuentas_hoja),
        "fecha": fecha,
        "empresa": empresa
    }, as_dict=True)

    balance = resultado[0]["balance"] if resultado and resultado[0]["balance"] is not None else 0
    return abs(balance) if es_pasivo else balance

# Cálculo de indicadores
activos_corrientes = obtener_balance("1.1 - Activos corriente - IP", fecha, empresa)
pasivos_corrientes = obtener_balance("2.1 - Pasivo corriente - IP", fecha, empresa, es_pasivo=True)

razon_corriente = (
    activos_corrientes / pasivos_corrientes
    if isinstance(activos_corrientes, (int, float)) and isinstance(pasivos_corrientes, (int, float)) and pasivos_corrientes != 0
    else 0
)

rows = [{
    "categoria": "Liquidez",
    "indicador": "Razón corriente",
    "valor": razon_corriente,
    "referencia": 1.2
}]

# Columnas
columns = [
    {"label": "Categoría", "fieldname": "categoria", "fieldtype": "Data", "width": 150},
    {"label": "Indicador", "fieldname": "indicador", "fieldtype": "Data", "width": 200},
    {"label": "Valor al período", "fieldname": "valor", "fieldtype": "Float", "width": 150},
    {"label": "Valor de referencia", "fieldname": "referencia", "fieldtype": "Float", "width": 150}
]

# Retorno esperado por el reporte
data = columns, rows

As you see here I selected ‘Solvencia’ which is the category and the only indicator I have has the category ‘Liquidez’ so it shouldn’t be shown

image
image1452×487 30.4 KB

This is my filters set up

image
image938×283 17.6 KB

I tried this other version but it always says that ‘categoria’ and ‘indicador’ are not defined

# Validación de filtros
fecha = filters.get("fecha")
empresa = filters.get("empresa") or frappe.defaults.get_user_default("Company")
categoria = filters.get("categoria")
indicador = filters.get("indicador")

if not fecha:
    frappe.throw("Por favor selecciona una fecha para el reporte.")

# Función para obtener el balance contable desde una cuenta padre
def obtener_balance(cuenta_padre, fecha, empresa, es_pasivo=False):
    cuenta_info = frappe.get_all("Account", filters={"name": cuenta_padre}, fields=["lft", "rgt"])
    if not cuenta_info:
        frappe.msgprint(f"Cuenta no encontrada: {cuenta_padre}")
        return 0

    limites = cuenta_info[0]
    cuentas = frappe.get_all("Account", filters={
        "lft": [">", limites["lft"]],
        "rgt": ["<", limites["rgt"]],
        "is_group": 0,
        "company": empresa
    }, fields=["name"])

    cuentas_hoja = [c["name"] for c in cuentas]
    if not cuentas_hoja:
        frappe.msgprint(f"Sin cuentas hoja para: {cuenta_padre}")
        return 0

    resultado = frappe.db.sql("""
        SELECT SUM(debit - credit) AS balance
        FROM `tabGL Entry`
        WHERE account IN %(cuentas)s
          AND posting_date <= %(fecha)s
          AND company = %(empresa)s
    """, {
        "cuentas": tuple(cuentas_hoja),
        "fecha": fecha,
        "empresa": empresa
    }, as_dict=True)

    balance = resultado[0]["balance"] if resultado and resultado[0]["balance"] is not None else 0
    return abs(balance) if es_pasivo else balance

# Cálculo de indicadores
activos_corrientes = obtener_balance("1.1 - Activos corriente - IP", fecha, empresa)
pasivos_corrientes = obtener_balance("2.1 - Pasivo corriente - IP", fecha, empresa, es_pasivo=True)

razon_corriente = (
    activos_corrientes / pasivos_corrientes
    if isinstance(activos_corrientes, (int, float)) and isinstance(pasivos_corrientes, (int, float)) and pasivos_corrientes != 0
    else 0
)

rows = [{
    "categoria": "Liquidez",
    "indicador": "Razón corriente",
    "valor": razon_corriente,
    "referencia": 1.2
}]

# Aplicar filtros (correctamente)
if categoria:
    rows = [r for r in rows if r["categoria"] == categoria]
if indicador:
    rows = [r for r in rows if r["indicador"] == indicador]

# Columnas del reporte
columns = [
    {"label": "Categoría", "fieldname": "categoria", "fieldtype": "Data", "width": 150},
    {"label": "Indicador", "fieldname": "indicador", "fieldtype": "Data", "width": 200},
    {"label": "Valor al período", "fieldname": "valor", "fieldtype": "Float", "width": 150},
    {"label": "Valor de referencia", "fieldname": "referencia", "fieldtype": "Float", "width": 150}
]

# Retorno esperado
data = columns, rows
8

Can you check if your selected filters appear in the url? There is report bug going around currently, just to confirm its not related. Make a url with your filters and try that url directly

9

and filters.get is the correct way

10

I writting the URL directly with this code and it showed the same error

# Validación de filtros
fecha = filters.get("fecha")
empresa = filters.get("empresa") or frappe.defaults.get_user_default("Company")
categoria = filters.get("categoria")
indicador = filters.get("indicador")

if not fecha:
    frappe.throw("Por favor selecciona una fecha para el reporte.")

# Función para obtener el balance contable desde una cuenta padre
def obtener_balance(cuenta_padre, fecha, empresa, es_pasivo=False):
    cuenta_info = frappe.get_all("Account", filters={"name": cuenta_padre}, fields=["lft", "rgt"])
    if not cuenta_info:
        frappe.msgprint(f"Cuenta no encontrada: {cuenta_padre}")
        return 0

    limites = cuenta_info[0]
    cuentas = frappe.get_all("Account", filters={
        "lft": [">", limites["lft"]],
        "rgt": ["<", limites["rgt"]],
        "is_group": 0,
        "company": empresa
    }, fields=["name"])

    cuentas_hoja = [c["name"] for c in cuentas]
    if not cuentas_hoja:
        frappe.msgprint(f"Sin cuentas hoja para: {cuenta_padre}")
        return 0

    resultado = frappe.db.sql("""
        SELECT SUM(debit - credit) AS balance
        FROM `tabGL Entry`
        WHERE account IN %(cuentas)s
          AND posting_date <= %(fecha)s
          AND company = %(empresa)s
    """, {
        "cuentas": tuple(cuentas_hoja),
        "fecha": fecha,
        "empresa": empresa
    }, as_dict=True)

    balance = resultado[0]["balance"] if resultado and resultado[0]["balance"] is not None else 0
    return abs(balance) if es_pasivo else balance

# Cálculo de indicadores
activos_corrientes = obtener_balance("1.1 - Activos corriente - IP", fecha, empresa)
pasivos_corrientes = obtener_balance("2.1 - Pasivo corriente - IP", fecha, empresa, es_pasivo=True)

razon_corriente = (
    activos_corrientes / pasivos_corrientes
    if isinstance(activos_corrientes, (int, float)) and isinstance(pasivos_corrientes, (int, float)) and pasivos_corrientes != 0
    else 0
)

rows = [{
    "categoria": "Liquidez",
    "indicador": "Razón corriente",
    "valor": razon_corriente,
    "referencia": 1.2
}]

# Aplicar filtros (correctamente)
if categoria:
    rows = [r for r in rows if r["categoria"] == categoria]
if indicador:
    rows = [r for r in rows if r["indicador"] == indicador]

# Columnas del reporte
columns = [
    {"label": "Categoría", "fieldname": "categoria", "fieldtype": "Data", "width": 150},
    {"label": "Indicador", "fieldname": "indicador", "fieldtype": "Data", "width": 200},
    {"label": "Valor al período", "fieldname": "valor", "fieldtype": "Float", "width": 150},
    {"label": "Valor de referencia", "fieldname": "referencia", "fieldtype": "Float", "width": 150}
]

# Retorno esperado
data = columns, rows

Error is:

App Versions

{
	"erpnext": "15.23.0",
	"frappe": "15.23.0",
	"print_designer": "1.2.0"
}

Route

query-report/KPIs

Traceback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 110, in application
    response = frappe.api.handle(request)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
           ^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
           ^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/__init__.py", line 1718, in call
    return fn(*args, **newargs)
           ^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/__init__.py", line 878, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/desk/query_report.py", line 220, in run
    result = generate_report_result(report, filters, user, custom_columns, is_tree, parent_field)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/__init__.py", line 878, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/desk/query_report.py", line 81, in generate_report_result
    res = get_report_result(report, filters) or []
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/desk/query_report.py", line 62, in get_report_result
    res = report.execute_script_report(filters)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/core/doctype/report/report.py", line 164, in execute_script_report
    res = self.execute_script(filters)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/core/doctype/report/report.py", line 184, in execute_script
    safe_exec(self.report_script, None, loc, script_filename=f"Report {self.name}")
  File "apps/frappe/frappe/utils/safe_exec.py", line 100, in safe_exec
    exec(
  File "<serverscript>: report_kpis", line 64, in <module>
  File "<serverscript>: report_kpis", line 64, in <listcomp>
NameError: name 'categoria' is not defined

Request Data

{
	"type": "GET",
	"args": {
		"report_name": "KPIs",
		"filters": "{\"fecha\":\"2025-05-30\",\"empresa\":\"Isy Pisy\",\"categoria\":\"Solvencia\"}",
		"ignore_prepared_report": false,
		"are_default_filters": false
	},
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.query_report.run",
	"request_id": null
}

Response Data

{
	"exception": "NameError: name 'categoria' is not defined",
	"exc_type": "NameError",
	"_exc_source": "Server Script"
}
11

@enavash You have really hit gold :wink:. For reasons I cannot fathom variables in list comprehension do not work in safe_exec, which is what is used here and in server script etc… so instead of the elegant pythonic way, filter in the ugly way :grin:

rows = [{
    "categoria": "Liquidez",
    "indicador": "Razón corriente",
    "valor": razon_corriente,
    "referencia": 1.2
}]

filtered = []

# Aplicar filtros (correctamente)
if categoria:
    for r in rows:
        if r["categoria"] == categoria:
            filtered.append(r)
1 Like
12

So says chatgpt:

:white_check_mark: Root of the Problem

In Frappe’s safe_exec, list comprehensions have their own scope. This is a known Python quirk, but it gets worse inside safe_exec, because of how variables are whitelisted and sandboxed.

So even though categoria is defined above the list comprehension, it’s not visible inside the list comprehension due to scope restrictions imposed by safe_exec.

Good to know…as I do use server script a lot!

13

Brooo you are the man! This solved the issue for filters :saluting_face: Thanks a lot.

Would you mind if I bother you when I start doing graphics? hahaha