[Tutorial] Script Reports in ERPNext: A Step-by-Step Guide

[Antony_Praveenkumar]

Summary:

Unlock the power of custom reporting in ERPNext with this comprehensive step-by-step guide. Dive into the world of script reports, where you’ll learn to harness scripting capabilities to create tailored reports that provide valuable insights for your business. From setting up the environment to writing scripts and designing dynamic reports, this guide covers it all. Elevate your reporting game and gain full control over your ERPNext data.

Reference Documentation

• How to change the colours on Report shell
• How To Color Cell in Any Reports Based on Values Like >10
• How to add css file report?

Create a report

I’m going to create a simple report with simple filters using the Employee Checkin Doctype data.

Python

employee_checkin_sheet.py

import frappe
from frappe import _


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



def get_columns(filters=None):
    return [
  		{
			"label": _("Employee"),
        		"fieldname": "employee",
          		"fieldtype": "Link",
	           	"options": "Employee",
	           	"width": 100
	        },
  		{
			"label": _("Employee Name"),
	        	"fieldname": "employee_name",
	          	"fieldtype": "Data",
	           	"width": 150
	        },
  		{
			"label": _("Log Type"),
	        	"fieldname": "log_type",
	          	"fieldtype": "Data",
	           	"width": 100,
			"align": 'center',
			"dropdown": False
	        },
  		{
			"label": _("Time"),
	        	"fieldname": "time",
	          	"fieldtype": "Datetime",
	           	"width": 200
	        },
  		{
			"label": _("Auto Attenadnce"),
	        	"fieldname": "auto_attendance",
	          	"fieldtype": "Check",
	           	"width": 150
	        },
	]


def get_datas(filters=None):
    
	data = frappe.get_all(
		'Employee Checkin',
		filters={
			'employee': filters.employee
		},
		fields=['employee', 'employee_name', 'log_type', 'time', 'skip_auto_attendance']
	)

	return data

JS File

employee_checkin_sheet.js

frappe.query_reports["Employee Checkin Sheet"] = {
	"filters": [
		{
			"label": "Employee",
        		"fieldname": "employee",
          		"fieldtype": "Link",
			"options":'Employee',
           		"width": 200,
			"reqd": 1
        	}
	]
}

What are all the events or functions we can use in JS

• onload
  Basically onload you can used to make a server call and fetch data and you can set in filters

  • onload:function(){
    alert('onload')
     }
    

• formatter
  Formatter used to change the format and look of the report

  formatter:function (value, row, column, data, default_formatter) {
    value = default_formatter(value, row, column, data);
  	return value
  }
  

• get_chart_data
  This function gets triggered when the data is rendered in the report

  get_chart_data: function (_columns, result) {
    console.log(_columns, result)
  }
  

Column Settings

You can set some settings in the columns like some features.
Here a small example.

check in the python file I mentioned above.

{
	"label": _("Employee"),
	"fieldname": "employee",
	"fieldtype": "Link",
	"options": "Employee",
	"width": 100
}

Here you can see label, fieldname, fieldtype, options & width but there is other more options is there here some of the options

Options	Description
align	It's used to align the content in the cell left, right, center
colIndex	It's used to change the column position Integer value
column	To give a name for a column to handle in diffrent way same like fieldname
focusable	When you click a cell its show which cell you clicked but its in False it won't show
resizable	Won't allow you to resize the report
sortOrder	String type 'asc', 'desc', 'none'

Change the color of the cell

we can achieve this through formatter

In the report you can see the IN & OUT log type i want to make the IN bold green and OUT bold red. the column field name is log_type

formatter:function (value, row, column, data, default_formatter) {
	value = default_formatter(value, row, column, data);

	if(column.fieldname == 'log_type'){
		if(value == 'IN'){
			value = `<b style="color:green">${value}</b>`
		}else if(value == 'OUT'){
			value = `<b style="color:red">${value}</b>`
		}
	}
}

• value → Argument Gives the current cell value
• row → This argument gives all the details of the current cell row
• column → This argument gives the column details of the current cell
• data → This argument gives the current row data
• default_formatter it’s a formatter funtion
  

Button In Report

Model 1

Add one button in filters in the js file like this

{
	"label": "Take Action",
	"fieldname": "custom_take_action",
	"fieldtype": "Button",
	"width": 200,
}

and add this code at the bottom of the js file

$(document).on("click", "button[data-fieldname='custom_take_action']", function() {
    // Your code to be executed when the element is clicked
    alert("Taking Action");
});

The final output

Basically, you can use this feature for any other operations for example load the css file
or send mail. some custom operations for these things you can use.

Model 2

Button in report cell. you can perform any operation from that. First create a column like this
in python file employee_checkin_sheet.py

{
	"label": _("Send Mail"),
	"fieldname": "send_mail",
	"fieldtype": "Data",
	"width": 150,
	"align": "center"
}

employee_checkin_sheet.js

In the formatter change add this line

try{
	if (column.id == 'send_mail'){
	value = `<button class="btn-primary" id="sent-email-${row[0].rowIndex}" data-employee="${data.employee}" data-employee_name="${data.employee}" data-log_type="${data.log_type}" onclick="send_mail(${row[0].rowIndex})">Send Mail</button>`
	return value
}
}catch(err){
	console.log('Error throwing because of last column its -> Total Row')
	console.log(err)
}

After adding the above line in the formatter add this send_mail function in the botom of the JS filr

const send_mail = (row_index) => {


	button = document.getElementById(`sent-email-${row_index}`)

	var employee = button.getAttribute('data-employee');
        var employee_name = button.getAttribute('data-employee_name');
    	var log_type = button.getAttribute('data-log_type');

	frappe.msgprint({
		title: __('Notification'),
		indicator: 'green',
		message: __(`Mail Sent successfully to <b>${employee_name}</b> Log Type <b>${log_type}</b>`)
	});
}

The final output like this

Change the background color

I want to make the all row background color as a antiquewhite for log type is OUT

Actually changing the background color of the row is not that much easy. Inspect the row you can see the structure of the row in html like this

You can see the class="dt-row dt-row-0 vrow" in that class dt-row-0 is unique and we can apply the CSS for each row we have to find the what are all the row contains the log_type OUT which means the rowIndex.

Row inside a row every cell contains class like this class="dt-cell dt-cell--col-0 dt-cell--0-4 dt-cell--row-4" here the sample image.

Here you can see lots of class but im chosing the class is this dt-cell. note be carefull while using this class it will fetch all the cell.

I’m Creating a button in js file in filters

{
	"label": '<b style="color:blue">Apply CSS</b>',
	"fieldname": "custom_apply_css",
	"fieldtype": "Button",
	"width": 150,
}

Finding rowIndex OUT for the rows. At the top of the JS file I’m putting a global variable like this

var log_type_out_rowIndex = []

in the formatter, pushing the rowIndex in the log_type_out_rowIndex variable

try{
	if(column.fieldname == 'log_type'){
		if(value == 'OUT'){
			log_type_out_rowIndex.push(row[0].rowIndex)
		}
	}
}catch(err){
	console.log('Total row table error')
	console.log(err)
}

At the bottom of the JS file im creating a event listner

$(document).on("click", "button[data-fieldname='custom_apply_css']", function() {

	console.log(log_type_out_rowIndex)

	if(log_type_out_rowIndex.length){
		for(index of log_type_out_rowIndex){
			const element = document.getElementsByClassName(`dt-row-${index}`);
			element[0].style.background = 'antiquewhite';
		}
	}
	const rowCell = document.getElementsByClassName(`dt-cell`);
	for(cell of rowCell){
		cell.style.background = 'inherit'
	}
});

Final output

Editable Column or Cell

Adding one more column in the report in the Python file

{
	"label": _('<b style="color:blue">Edit Me</b>'),
	"fieldname": "edit_me",
	"fieldtype": "Data",
	"width": 150,
	"align": "center"
}

Add this code in the formatter

// Edit the column
try{
	if(column.fieldname == 'edit_me'){
		console.log(row, column)
		column.editable = true
	}
}catch(err){
	console.log('Total row table error')
	console.log(err)
}

Here is the final output

I can able to edit but while exporting in pdf, excel or print it’s not showing. if anyone has an idea please suggest.

Kindly share your thoughts and please support with your likes and comments

Github: Antony-M1 · GitHub
Linkedin: Antony
Source Code: https://github.com/Antony-M1/erpnext-doc/tree/main/employee_checkin_sheet

[Krishn]

Hello, @Antony_Praveenkumar! Thank you for providing this information. However, I’m interested in learning more about Dynamic Link, specifically child table items.

[Antony_Praveenkumar]

@Krishn

Dynamic Link

For Dynamic Link you need one Link field with options of DocType which means in the options you have to mention this DocType, then create another field with Dynamic Link in that field Options mention the Link field name

for example:

Field 1
Label Name: Voucher Type
fieldname: voucher_type
Data Type: Link
Options: Doctype

Field 2
Label Name: Voucher No
fieldname: voucher_no
Data Type: Dynamic Link
Options: voucher_type

[Krishn]

@Antony_Praveenkumar

Options: voucher_type

but there is a child table like item

[Antony_Praveenkumar]

@Krishn

but there is a child table like item

Child Table you can’t able to integrate in Dynamic Link

[Krishn]

Ok Thank you