Need help in creating a custom report

To get only the quantity of employee between joining date and relieving date you can use the following:

in .js file:

frappe.query_reports["Your Report Name"] = {
	"filters": [
		{
			"fieldname":"date_of_joining",
			"label": __("Date of Joining"),
			"fieldtype": "Date",
			"default": frappe.datetime.get_today()
		},
		{
			"fieldname":"relieving_date",
			"label": __("Relieving Date"),
			"fieldtype": "Date",
			"default": frappe.datetime.get_today()
		},
		{
			"fieldname":"company",
			"label": __("Company"),
			"fieldtype": "Link",
			"options": "Company",
			"default": frappe.defaults.get_user_default("Company")
		}
	]
}

and in .py file:

from __future__ import unicode_literals
import frappe
from frappe import _
from frappe.utils import flt

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

	columns = get_columns()
	data = get_employees(filters)

	return columns, data

def get_columns():
	return [
		_("Employee QTY between Dates") + ":Data:120"
	]

def get_employees(filters):
	return frappe.db.sql("""SELECT COUNT(`name`) FROM `tabEmployee`
					WHERE `date_of_joining` >= '{date_of_joining}'
					AND (`relieving_date` <= '{relieving_date}' OR `relieving_date` IS NULL)
                                        AND `company` = '{company}'""".format(date_of_joining=filters['date_of_joining'], relieving_date=filters['relieving_date'], company=filters['company']), as_list=1)

but for this case, a script report is too much for my mind, you can easely just filter the list view of employee and see the qty of records :wink:

2 Likes

Here is Query Report. and Report Name should be “Active Employee”
Insert this in Query.

select 
count(name) as "Active Employees:Int:100",
company as "Company:Link/Company:200"
 

 from tabEmployee

where 

date_of_joining <=  %(from_date)s 
and (relieving_date >=  %(from_date)s or relieving_date is null)
group by company

and below code in JS Section

frappe.query_reports["Active Employee"] = {
    "filters": [

	{
		"fieldname":"from_date",
		"label": __("Date"),
		"fieldtype": "Date",
 		"reqd": 1	
	}    
 	
    ]
}
1 Like

this will also work but, it’ll miss Current employees who’s Relieving date is null. just need to put one more condition to handle that and this will work as well.

1 Like

Of course you are absolutely right :+1:
I’ve corrected my code…

Wow thank you guys, I’ll try the scripts asap and let you know how it goes :D.

Really appreciated!

1 Like

It works guys. And I’ve realized we have about a 100 Date of Joining details missing, and the report is just… Now, basically just sitting and waiting for the HR to update employee infos :slight_smile:

Thank you so much guys!

1 Like

Youre welcome!

1 Like