Script report in Non-standard v12 on cloud hosting

I was fiddling with script report. But getting error. Please help me to find erro in following js and py files.

python:

def get_columns():
    return [
        "someField:Data:100",
        "Type:Data:60"
    ]


def get_data(filters):
    return [
        ['hello', 'world']
    ]


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

js file

frappe.query_reports["Receivable List"] = {
  "filters": [
    {
      fieldname: "someField",
      label: "Some Field",
      fieldtype: "Select",
      options: "30\n45\n60",
      default: 30
    },
  ]
};

But i am getting following server error log in python script

[ERROR] 2021-07-14 20:17:34,886 | /home/frappe/frappe-bench/apps/frappe/frappe/app.py:
Site: erpnext.localhost
Form Dict: {
“cmd”: “frappe.desk.query_report.run”,
“filters”: “{"someField":"30"}”,
“report_name”: “Receivable List”
}
Request Error
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 67, in application
response = frappe.api.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/api.py”, line 59, in handle
return frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 24, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 64, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 1075, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 539, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 205, in run
result = generate_report_result(report, filters, user, custom_columns)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 68, in generate_report_result
columns = [get_column_as_dict(col) for col in columns]
TypeError: ‘NoneType’ object is not iterable
[ERROR] 2021-07-14 20:17:34,892 | /home/frappe/frappe-bench/apps/frappe/frappe/utils/error.py:
New Exception collected with id: 2021-07-14 20:17:34.887294-172.19.0.1-fa5

Same code works fine in custom app. Can anybody tell me reason.???

get_columns should return a list of dictionaries.
Can you try this

    return [
        {"someField:Data:100"},
        {"Type:Data:60"}
    ]

Hi @zulfi007

Encountering same issue… did you find a solution ?

Kind regards,

Hi @mujeerhashmi

This didn’t work… any other ideas ?

I think it’s also important to mention again that this is a custom script report (ie is_standard = No) created in a frappe-hosted account as already reflected in the topic of this thread

Thanks

yes.
Answer was simple. always in plain sight. mention below every script report. use
column =[] # list of columns
result = [] # list of data
data = [columns, result]

Hi @zulfi007

Could you please share your updated code which worked eventually ? I think that would make it clearer for me

Thanks

Hi @zulfi007

Still hoping to get a sample script from you. I tried the script below and it still returned the same error:

def execute(filters=None):
    columns = [
        "somefield:Data:100"
    ]
    result = [
        ['hello']
    ]
    data = [columns, result]
    return data

Kindly let me know how you formatted your script to get it working

Thanks

This is wrong. columns and result expect an object:

def execute(filters=None):
    # frappe will use each column object i.e the data in-between the "{}" 
    # to figure out how to render a column in the report i.e a list of objects
    columns = [{
	    "fieldname": "customer",
	    "label": _("Customer"),
	    "fieldtype": "Link", # Or as per your example, "Data"
	    "options": "Customer",
	    "width": 200
    }]

    # note: a list of lists
    data = [["Customer PLC"]]
    ...

Hi @tundebabzy

Thanks a lot for responding. I’ve tried the following but still the same error message:

def execute(filters=None):
    columns = [{
	    "fieldname": "customer",
	    "label": _("Customer"),
	    "fieldtype": "Link",
	    "options": "Customer",
	    "width": 200
    }]
    data = [["Test Customer"]]
    return columns, data 

def execute(filters=None):
    columns = [{
	    "fieldname": "customer",
	    "label": _("Customer"),
	    "fieldtype": "Link",
	    "options": "Customer",
	    "width": 200
    }]
    result = [["Test Customer"]]
    data = [columns, result]
    return data

Any idea what the issue is ?

I’m not sure. Any errors? traceback in the browser console?

No need for def execute(filters=None):
or any return statement.

just simply use e.g

columns = [
      "voucher_no:Data:40",
      "posting_date:Date:80",
      "party:Data:100",
      "debit_in_account_currency:Currency:80",
      "credit_in_account_currency:Currency:100"
      
  ]

result= frappe.db.get_all('GL Entry',
      filters=[
          ['party_type','=' ,'Customer'],
      ],
      fields=['voucher_no','posting_date','party','debit_in_account_currency','credit_in_account_currency'],
      )

data = [columns, result]
1 Like

Wow! Thanks @zulfi007, you’re totally correct! The below worked:

columns = [{
	    "fieldname": "customer",
	    "label": _("Customer"),
	    "fieldtype": "Link",
	    "options": "Customer",
	    "width": 200
    }]
result = [["Test Customer"]]
data = [columns, result]

Will try to figure things out from here. Hope this helps others in future

Cheers

It was the same error mentioned at the beginning of the thread. Thanks a lot for your kind assistance

Hi @zulfi007

the frappe.db.sql api doesn’t seem to work in the custom script report… Any idea why ?

Kind regards,

Frappe Framework uses the RestrictedPython library to restrict access to methods available for server scripts. Only the safe methods are available in server scripts

https://docs.erpnext.com/docs/v12/user/manual/en/customize-erpnext/server-script#25-security

1 Like

Thanks a lot @zulfi007