Looking for instructions for Script Reports standard=no

1

I can’t find any documentation on how to formulate script reports in the GUI.
Both ChatGPT and Gemini don’t “know” how, either.

Can someone offer some basics, like how are defined functions called and what syntax is required.

Here are two unsuccessful conversations with ChatGPT and Gemini

For example what seems like a basic bit of code yeilds no results.

def execute(filters=None):
    data = [{"col1": "value1", "col2": "value2"}, {"col1": "value3", "col2": "value4"}]
    columns = [{"fieldname": "col1", "label": "Column 1", "fieldtype": "Data"}, {"fieldname": "col2", "label": "Column 2", "fieldtype": "Data"}]
    return columns, data

Thanks in advance!

EDIT:
I tried

def me_get():
    myUser = frappe.db.get_all('User', ['first_name', 'last_name'])
    return myUser
result = me_get()

The above code works and returns data in the report! Progress is great. I started with the single line mentioned here. With the error messages I was able to come up with the code above.

If anyone can let me know if there’s more info or perhaps how I can get accurate output from chatGPT.

2

Hi,

Please find below working code:

def get_data(filters=None):
    if not filters:
        filters = {}

    from_date = filters.get("from_date")
    to_date = filters.get("to_date")
    branch = (filters.get("branch") or "") + "%"

    query = """
        SELECT gle.posting_date, gle.account, gle.against, gle.branch, gle.cost_center,  gle.voucher_no, mo.name AS expense_no, d.group_1 AS marque, d.group_2 AS type_action, gle.remarks,
			CASE WHEN mo.purchase_invoice IS NULL THEN gle.debit - gle.credit ELSE d.amount END AS amount,
			mo.mois, gle.name AS gl_entry, s.supplier_name AS against_name, mo.description
		FROM `tabGL Entry` gle LEFT JOIN `tabBPM Marketing Operations` mo ON mo.purchase_invoice = gle.voucher_no
			LEFT JOIN `tabBPM Expense Details` d ON d.parent = mo.name 
			LEFT JOIN tabSupplier s ON gle.against = s.name
		WHERE gle.account = '62720700 - Marketing Expenses - MCO'  AND gle.posting_date BETWEEN %(from_date)s AND %(to_date)s AND gle.branch LIKE %(branch)s
    """

    result = frappe.db.sql(query, {
        "from_date": from_date,
        "to_date": to_date,
        "branch": branch,
    }, as_dict=True)

    return result
result = get_data(filters)

Please set up the filters and column in GUI. Also replace the SQL script with yours.

Thanks,

Divyesh