Query report with no backend access

ERPNext User Forum
1

Good day all

V13.48.1

I have written a custom query-report under build>report>new>query-report

I would like to add a “check” button which modifies the query parameters slightly.
And from reading up about this it seems I can only do this by making the report
standard and having backend access to add the script conditionals.

Is my understanding correct ?

2

Try Script report. From V13 you can create script report without making it standard.
You can have greater control on filters and visible columns based on the filter selection.

3

Good day @gsarunk

Thank you for your post. Apologies for only responding now. My other servers are all running V13
but I am busy setting up a V14 machine. Got stuck with a problem. I digress.

Let me try this script report. I have been steering clear of it only because my perception
is that it is going to create code on the server which will cause problems on the next update.

4

good luck.
start with simple steps like below

# Begin of execute    
data = []
data_table = get_result(filters)
data_table = get_filtered_records_for_date_type(data_table, filters)
chart_data = get_chart_data(data_table, filters)
data = get_columns(filters), data_table, None, chart_data

your get_columns will be like below

def get_columns(filters):
    columns = []
    columns = [
            {"fieldname": "sales_order", "label" : _("Sales Order"), "fieldtype": "Link", "options": "Sales Order", "width": 140},
            {"fieldname": "customer", "label" : _("Customer"), "fieldtype": "Link", "options": "Customer", "width": 140},
            {"fieldname": "item_code", "label" : _("Item Code"), "fieldtype": "Link", "options": "Item", "width": 180},
            {"fieldname": "id_number", "label" : _("Id Number"), "fieldtype": "Data", "width": 160},
]

Determine your columns based on the filters
your data_table will be an array of python dict corresponding to the columns.

If require charts please refer to https://github.com/frappe/erpnext/blob/ca10e2bb9ff61bc1c9ffcd1e7a2a14d6ec4f4d51/erpnext/support/report/issue_summary/issue_summary.py#L290-L327

5

Update

So I am trying to implement suggestions by @gsarunk and in the process I had a look at
existing reports I just need to clarify that my understanding is correct…

I understand many of you are beyond this point and my questions may seem trivial but I would
like to get this under my belt.

So if I create my report … Build->Report->New
Select-> script reportt

Then the code below is placed in the Client Code block, just below the “query/Sccript” block??

def execute(filters=None):

        return columns, data

def get_columns():

        return columns


def get_results(filters):

        return query.run()
6

That’s not right.
Client code is where you put all javascript for your report. You can define filters and do other frontend related stuffs. You can refer to any standard report.js for this.

frappe.query_reports['Report Name'] = {

"filters": [{
			"fieldname":"BOM",
			"label": __("BOM/Design"),
			"fieldtype": "Link",
			"options": "BOM",
			"reqd": 1,
			
		},
		{
			"fieldname":"cbom",
			"label": __("Compare BOM/Design"),
			"fieldtype": "Link",
			"options": "BOM",
		},
]

Query/Script section is where you prepare data and send for output.
typically it is equivalent to report.py

in this section you should return data like below

data = get_columns(filters), data_table, None, chart_data

and put all your python function above this code. Important. something like below

def get_columns(filters):
   ..
   ..
   return columns

def get_data_table(filters):
   ..
   return data_table

data = get_columns(filters), get_data_table(filters)

note: you don’t have to define columns and filters using the filters and columns section if you are going to populate through code.

7

Thank you very much @gsarunk

I had it wrong on many counts. Let me work through this info.

As I mentioned, my first query report is working … now need to get the script report
under my belt !

Let me dig into this.

8

Update
Bingo !

Not quite finished yet … want to add a check-button and a few more columns
but so far its working…



def get_results(filters):

    bom_item_table = "BOM Item"
    bin = frappe.qb.DocType("Bin")
    bom = frappe.qb.DocType("BOM")
    bom_item = frappe.qb.DocType(bom_item_table)

    query = (
            frappe.qb.from_(bom)
            .inner_join(bom_item)
            .on(bom.name == bom_item.parent)
            .left_join(bin)
            .on(bom_item.item_code == bin.item_code)
            .select(
                    bom_item.parent,
                    bom_item.item_code,
                    bom_item.item_name,
            )
            .where(bom_item.parenttype == 'BOM') 
            .groupby(bom_item.item_code)
    )


    return query.run()

message = "This report has been generated automatically."



results = get_results(filters)



## Finally, define your columns. Many of the usual field definition properties are available here for use.
## If you wanted to, you could also specify these columns in the child table above.
columns = [
    {
        'fieldname': 'bom_item.parent',
        'label': _('Parent BOM'),
        'fieldtype': 'Data',
        'align': 'left',
        'width': 200
    },
    {
        'fieldname': 'bom_item.item_code',
        'label': _('Item Code'),
        'fieldtype': 'Link',
        'options': 'item',
        'width': 100
    },
    {
        'fieldname': 'bom_item.item_name',
        'label': _('Item_name'),
        'fieldtype': 'Data',
        'align': 'left',
        'width': 200
    },
]

data = columns, results, message

@gsarunk , please allow me to express my gratitude. I have been reading many posts as well
and non-standard script reports seems to be a slippery one.

My little report is nothing fancy but it is a pint from where I can build from.

1 Like