Create report with child table data

I have a doctype Lab Schedule in which i have a child table Lab schedule readings

i want to create a report to fetch all data of the child table as it store all the readings data

I have tried script report and made a script but its not seem to be working
I want to filter out the submitted documents and show its report

Script:

def execute(filters=None):
columns, data = get_columns(),

frappe.log_error("Starting Lab Workbook Report Execution", "Lab Workbook Report")

# Fetch Lab_Schedule documents that are submitted (docstatus=1)
lab_schedules = frappe.get_all(
    "Lab_Schedule",
    filters={"docstatus": 1},
    fields=[
        "name", "lab_template", "created_datetime", "submitted_datetime",
        "plant", "division", "department", "area", "subarea",
        "maintenance", "maintenance_type"
    ]
)

frappe.log_error(f"Fetched Lab_Schedule: {lab_schedules}", "Lab Workbook Report")

# Fetch the child table data for each Lab_Schedule document
for lab_schedule in lab_schedules:
    readings = frappe.get_all(
        "Lab_Schedule_Readings",
        filters={"parent": lab_schedule.name, "parenttype": "Lab_Schedule"},
        fields=[
            "machine_group", "heat_number", "machine", "crucible",
            "measure", "unit", "readings"
        ]
    )

    if readings:
        for reading in readings:
            data.append([
                lab_schedule.name,  # ID (expression generated field)
                lab_schedule.lab_template,
                lab_schedule.created_datetime,
                lab_schedule.submitted_datetime,
                lab_schedule.plant,
                lab_schedule.division,
                lab_schedule.department,
                lab_schedule.area,
                lab_schedule.subarea,
                lab_schedule.maintenance,
                lab_schedule.maintenance_type,
                reading.machine_group,
                reading.heat_number,
                reading.machine,
                reading.crucible,
                reading.measure,
                reading.unit,
                reading.readings
            ])
    else:
        data.append([
            lab_schedule.name,  # ID (expression generated field)
            lab_schedule.lab_template,
            lab_schedule.created_datetime,
            lab_schedule.submitted_datetime,
            lab_schedule.plant,
            lab_schedule.division,
            lab_schedule.department,
            lab_schedule.area,
            lab_schedule.subarea,
            lab_schedule.maintenance,
            lab_schedule.maintenance_type,
            None, None, None, None, None, None, None
        ])

frappe.log_error(f"Final Report Data: {data}", "Lab Workbook Report")

return columns, data

def get_columns():
return [
{“fieldname”: “id”, “label”: “ID”, “fieldtype”: “Data”, “width”: 100},
{“fieldname”: “lab_template”, “label”: “Lab Template”, “fieldtype”: “Link”, “options”: “Lab_Template”, “width”: 150},
{“fieldname”: “created_datetime”, “label”: “Created Datetime”, “fieldtype”: “Datetime”, “width”: 150},
{“fieldname”: “submitted_datetime”, “label”: “Submitted Datetime”, “fieldtype”: “Datetime”, “width”: 150},
{“fieldname”: “plant”, “label”: “Plant”, “fieldtype”: “Link”, “options”: “Plant”, “width”: 100},
{“fieldname”: “division”, “label”: “Division”, “fieldtype”: “Link”, “options”: “Division”, “width”: 100},
{“fieldname”: “department”, “label”: “Department”, “fieldtype”: “Link”, “options”: “Department”, “width”: 100},
{“fieldname”: “area”, “label”: “Area”, “fieldtype”: “Link”, “options”: “Area”, “width”: 100},
{“fieldname”: “subarea”, “label”: “Subarea”, “fieldtype”: “Link”, “options”: “Subarea”, “width”: 100},
{“fieldname”: “maintenance”, “label”: “Maintenance”, “fieldtype”: “Link”, “options”: “Maintenance”, “width”: 100},
{“fieldname”: “maintenance_type”, “label”: “Maintenance Type”, “fieldtype”: “Link”, “options”: “Maintenance_Type”, “width”: 100},
{“fieldname”: “machine_group”, “label”: “Machine Group”, “fieldtype”: “Link”, “options”: “Machine_Group”, “width”: 100},
{“fieldname”: “heat_number”, “label”: “Heat Number”, “fieldtype”: “Data”, “width”: 100},
{“fieldname”: “machine”, “label”: “Machine”, “fieldtype”: “Link”, “options”: “Machine_Master”, “width”: 100},
{“fieldname”: “crucible”, “label”: “Crucible”, “fieldtype”: “Link”, “options”: “Crucible_Master”, “width”: 100},
{“fieldname”: “measure”, “label”: “Measure”, “fieldtype”: “Link”, “options”: “UOM_Measure”, “width”: 100},
{“fieldname”: “unit”, “label”: “Unit”, “fieldtype”: “Link”, “options”: “UOM”, “width”: 100},
{“fieldname”: “readings”, “label”: “Readings”, “fieldtype”: “Float”, “precision”: 4, “width”: 100}
]

this is a sample report that i want to show as a report

Script :

def execute(filters=None):
from_date = filters.get(“from_date”)
to_date = filters.get(“to_date”)
plant = filters.get(“plant”, “RIPL”)
division = filters.get(“division”, “SMS”)
department = filters.get(“department”, “QC”)
area = filters.get(“area”, “FURNACE”)
docstatus = filters.get(“docstatus”, “1”)

# Conditions for the SQL query
conditions = ""
if from_date and to_date:
    conditions += f" AND ls.created_datetime BETWEEN '{from_date}' AND '{to_date}'"
if plant:
    conditions += f" AND ls.plant = '{plant}'"
if division:
    conditions += f" AND ls.division = '{division}'"
if department:
    conditions += f" AND ls.department = '{department}'"
if area:
    conditions += f" AND ls.area = '{area}'"
if docstatus:
    conditions += f" AND ls.docstatus = '{docstatus}'"

# Log the formed conditions
frappe.log_error(f"SQL Conditions: {conditions}", "Lab Workbook Report")

# Define the columns for the report
columns = [
    {"fieldname": "id", "fieldtype": "Data", "label": "ID", "width": "150px"},
    {"fieldname": "lab_template", "fieldtype": "Data", "label": "Lab Template", "width": "150px"},
    {"fieldname": "created_datetime", "fieldtype": "Datetime", "label": "Created Datetime", "width": "150px"},
    {"fieldname": "submitted_datetime", "fieldtype": "Datetime", "label": "Submitted Datetime", "width": "150px"},
    {"fieldname": "plant", "fieldtype": "Data", "label": "Plant", "width": "150px"},
    {"fieldname": "division", "fieldtype": "Data", "label": "Division", "width": "150px"},
    {"fieldname": "department", "fieldtype": "Data", "label": "Department", "width": "150px"},
    {"fieldname": "area", "fieldtype": "Data", "label": "Area", "width": "150px"},
    {"fieldname": "subarea", "fieldtype": "Data", "label": "Subarea", "width": "150px"},
    {"fieldname": "maintenance", "fieldtype": "Data", "label": "Maintenance", "width": "150px"},
    {"fieldname": "maintenance_type", "fieldtype": "Data", "label": "Maintenance Type", "width": "150px"},
    {"fieldname": "machine_group", "fieldtype": "Data", "label": "Machine Group", "width": "150px"},
    {"fieldname": "heat_number", "fieldtype": "Data", "label": "Heat Number", "width": "150px"},
    {"fieldname": "machine", "fieldtype": "Data", "label": "Machine", "width": "150px"},
    {"fieldname": "crucible", "fieldtype": "Data", "label": "Crucible", "width": "150px"},
    {"fieldname": "measure", "fieldtype": "Data", "label": "Measure", "width": "150px"},
    {"fieldname": "unit", "fieldtype": "Data", "label": "Unit", "width": "150px"},
    {"fieldname": "readings", "fieldtype": "Float", "label": "Readings", "width": "150px"}
]

# Fetch the data
sql_query = f"""
    SELECT 
        ls.name as id,
        ls.lab_template,
        ls.created_datetime,
        ls.submitted_datetime,
        ls.plant,
        ls.division,
        ls.department,
        ls.area,
        ls.subarea,
        ls.maintenance,
        ls.maintenance_type,
        lsr.machine_group,
        lsr.heat_number,
        lsr.machine,
        lsr.crucible,
        lsr.measure,
        lsr.unit,
        lsr.readings
    FROM
        `tabLab_Schedule` ls
    LEFT JOIN
        `tabLab_Schedule_Readings` lsr ON lsr.parent = ls.name
    WHERE
        1=1
        {conditions}
"""

try:
    frappe.log_error(f"SQL Query: {sql_query}", "Lab Workbook Report")

    result = frappe.db.sql(sql_query, as_dict=True)

    # Log the result count
    frappe.log_error(f"Result Count: {len(result)}", "Lab Workbook Report")
    
except Exception as e:
    frappe.log_error(f"Error: {str(e)}", "Lab Workbook Report")
    result = []

data = columns, result

Client Code:

frappe.query_reports[“Lab Workbook Report”] = {
filters: [
{
“fieldname”: “from_date”,
“fieldtype”: “Date”,
“label”: “From Date”,
“default”: frappe.datetime.add_months(frappe.datetime.get_today(), -1)
},
{
“fieldname”: “to_date”,
“fieldtype”: “Date”,
“label”: “To Date”,
“default”: frappe.datetime.get_today()
},
{
“fieldname”: “plant”,
“fieldtype”: “Link”,
“options”: “Plant_Master”,
“label”: “Plant”,
“default”: “RIPL”,
“read_only”: 1
},
{
“fieldname”: “division”,
“fieldtype”: “Link”,
“options”: “Division_Master”,
“label”: “Division”,
“default”: “SMS”,
“read_only”: 1
},
{
“fieldname”: “department”,
“fieldtype”: “Link”,
“options”: “Department”,
“label”: “Department”,
“default”: “QC”,
“read_only”: 1
},
{
“fieldname”: “area”,
“fieldtype”: “Link”,
“options”: “Area_Master”,
“label”: “Area”,
“default”: “FUR”,
“read_only”: 1
},
{
“fieldname”: “docstatus”,
“fieldtype”: “Select”,
“options”: [“”, “Draft”, “Submitted”, “Cancelled”],
“label”: “Docstatus”,
“default”: “Submitted”,
“read_only”: 1
}
]
}

Network Response:

{
“message”: {
“result”: null,
“columns”: ,
“message”: null,
“chart”: null,
“report_summary”: null,
“skip_total_row”: 0,
“status”: null,
“execution_time”: 0.005825,
“add_total_row”: 0
},
“_debug_messages”: “["method:frappe.desk.query_report.run"]”
}

as shown in above image data already exist

Are you trying to create a multipage report (one page for each parent record)?

No ,

I want a single report with child table data as headers

I have the data report filled
for headers i want to show the fields
plant,division,department,area,subarea

then from child table data i want to show the headers as

machine group,heat number,machine,crucible
and measure as headers
carbon%, mangenese%, sulphur%, phosphorus%, silica%
.10 .15 .23 .25 .35

i want to data like this in the report
as in the same format we need to submit this for audit and records
so i want to create this type of report

I assume you are looking for something like the second result below, not the first example, yes?

Want to create like the first one
all the measures are in a column and the readings will below them

the second one dont need to be created it can be achieved by the frappe default report

Gotcha.

You should be able to do this with a Query Report.
You’ll need a SQL CASE statement for the measurement’s values.

This is a basic sample to help get you started.

select item_code,uom,qty
FROM `tabSales Invoice Item`
where parent like "2403-0046"

result:
Screenshot 2024-06-29 at 1.23.18 PM

Now with case statement:

select item_code as 'aItem',
case when uom ='x10' then qty else '' end as x10,
case when uom = 'ea' then qty else '' end as ea
FROM `tabSales Invoice Item`
where parent like "2403-0046"

Screenshot 2024-06-29 at 1.25.41 PM

Of course you’ll need to join your parent table with the child table in the query.