Transforming query report to script report

ERPNext User Forum
1

Hi
I have a working query report that I want to transform to a script report.

Here is a snip of the query report …

SELECT 
  bi.parent as "Parent BOM", 
  COALESCE(bi.item_code, 'All Items') as "Item Code", 
  bi.item_name as "Item Name", 
  bi.qty_consumed_per_unit * %(nr)s as "Qty",

I would like to make sure that I handle the following two lines together with their
column definitions correctly …

  COALESCE(bi.item_code, 'All Items') as "Item Code",

and

  bi.qty_consumed_per_unit * %(nr)s as "Qty",

This is how I propose to handle the function … ( snip of proposed script report … )

from pypika import Case, functions as fn
import frappe

def get_data(filters):
    b = frappe.qb.DocType("BOM")
    bi = frappe.qb.DocType("BOM Explosion Item")
    bn = frappe.qb.DocType("Bin")
    i = frappe.qb.DocType("Item")

    nr = filters.get("nr", 1)
    round_val = filters.get("round", 2)
    ignore_neg = filters.get("ignore_neg", "No")
    parent_bom = filters.get("parent_bom")

    query = (
        frappe.qb
        .from_(b)
        .inner_join(bi).on(b.name == bi.parent)
        .left_join(bn).on(bi.item_code == bn.item_code)
        .left_join(i).on(bi.item_code == i.item_code)
        .select(
            bi.parent.as_("Parent BOM"),
            fn.Coalesce(bi.item_code, "All Items").as_("Item Code"),
            bi.item_name.as_("Item Name"),
            (bi.qty_consumed_per_unit * nr).as_("Qty"),

And then I want to do the column definitions …

columns = [
    
        {
            "fieldname": "bi.parent",    #
            "fieldtype": "Link",
            "label": "Parent BOM",
            "options": "BOM",
            "align": "left",
            "width": 180
        },
        {
            "fieldname": "bi.item_code",   #
            "fieldtype": "Link",
            "label": "Item Code",
            "options": "Item",
            "align": "left",
            "width": 150
        },
        {
            "fieldname": "bi.item_name",   #
            "fieldtype": "Data",
            "label": "Item Name",
            //"options": "Sales Order",
            "width": 120
        },
       {
            "fieldname": "bi.qty_consumed_per_unit * nr",
            "fieldtype": "Data",
            "label": "Qty",
            "align": "left",
            "width": 150
        },

Would appreciate some guidance.

2

Are you trying to do this in the web GUI or in custom app using backend .py and .js files?

3

Thank you for your redponse @volkswagner

I was, at first trying to do this on client side, but eventually I decided its best to do this server side. So I have now moved to server-side.

Client side doesn’t seem to support “import” so its difficult to call functions that has
to be imported. And now that I am working on server-side, I am progressing very
well.

4

The assumption is that you are trying to create a report with the script report type (sandbox) .
You can use a simple script with a structure like this:

columns = [
    
        {
            "fieldname": "bi.parent",    #
            "fieldtype": "Link",
            "label": "Parent BOM",
            "options": "BOM",
            "align": "left",
            "width": 180
        },
        {
            "fieldname": "bi.item_code",   #
            "fieldtype": "Link",
            "label": "Item Code",
            "options": "Item",
            "align": "left",
            "width": 150
        },
        {
            "fieldname": "bi.item_name",   #
            "fieldtype": "Data",
            "label": "Item Name",
            //"options": "Sales Order",
            "width": 120
        },
       {
            "fieldname": "bi.qty_consumed_per_unit * nr",
            "fieldtype": "Data",
            "label": "Qty",
            "align": "left",
            "width": 150
        }
]

sql_query = """
    ***##You can write your query here***
    SELECT 
      bi.parent as "Parent BOM", 
      COALESCE(bi.item_code, 'All Items') as "Item Code", 
      bi.item_name as "Item Name", 
      bi.qty_consumed_per_unit * %(nr)s as "Qty",
      ***#Continue***
"""

lett = frappe.db.sql(query, as_dict=True)

data = columns, lett
5

Thank you @Gembira_IT_Tech for your response.

Let me work through your suggestions and revert.