Need help in creating a custom report

ERPNext Reports
#1

Hello ERPNext, and community. Hope everyone at the Conference is having a blast.

Meanwhile, I would like to ask for a help from you guys in helping me in building a custom report for HR.

UI does not matter, though it should look similar to Employee Birthday report’s month selection etc.

It’s just “We had this many employees on this date”. That’s it.

Sum of employee count, on a given date. I know I could choose date range, but the solution I’m looking for is very counter-intuitive and I know I can expand that report for other reports as well.

Thank you guys, hoping to get a solution for this :slight_smile:

#2

Develop a script report as you can’t provide filter in a Query report.

#3

That’s the problem. I’ve got zero knowledge in that area.

I’m actually willing to pay, accept bids or something for the implementation :sweat_smile:

#4

Do you have a self hosted ERP with an own app? so i could guide you to ceate your own script report?

1 Like
#5

Joelios question is very valid. if you are hosted on ERPNext Server, you cannot have custom report.

but if you have your own hosting, query report will work and it’s not something that you need to pay.

#6

I do have it installed on my server, running v12.1.15 :slight_smile:

#7

Good :+1:
Do you have enabled developer_mode?

#8

Definitely do.

#9

Good, then:

  1. loggin as “Administrator” and got to “Developer > Report > New”
  2. Set “Report Name”, “Ref DocType”, “Report Type” → “Script Report”, “Is Standard” → Yes and “Module” → “Your App”
#10

Did so. Copied pasted .py and .js from employee birthday report, and replaced some fields but to no avail.

#11

:+1:
Can you show me the code that you already have and explain me what else do you need?

#12

Now I want that report to show the count employees we have had on that time. For example, we’ve had 120 employees on January 2019.

Use case would be to choose March 2019, and show how many employees we’ve had as well, and just count the difference ourselves.

#13

So you want the exactly same report as Employee Birthday, just switch date of birth with date of joining?

#14

If yes:

the .py should look like something like that:

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") + ":Link/Employee:120", _("Name") + ":Data:200", _("Date of Joining")+ ":Date:100",
		_("Branch") + ":Link/Branch:120", _("Department") + ":Link/Department:120",
		_("Designation") + ":Link/Designation:120", _("Gender") + "::60", _("Company") +     ":Link/Company:120"
	]

def get_employees(filters):
	conditions = get_conditions(filters)
	return frappe.db.sql("""select name, employee_name, date_of_joining,
	branch, department, designation,
	gender, company from tabEmployee where status = 'Active' %s""" % conditions, as_list=1)

def get_conditions(filters):
	conditions = ""
	if filters.get("date"):
		date = filters["date"]
		conditions += " and date_of_joining <= '%s'" % date

	if filters.get("company"): conditions += " and company = '%s'" % \
		filters["company"].replace("'", "\\'")

	return conditions

and your .js file somethine like that:

frappe.query_reports["Employee Birthday"] = {
"filters": [
	{
		"fieldname":"date",
		"label": __("Date"),
		"fieldtype": "Date",
		"default": frappe.datetime.get_today()
	},
	{
		"fieldname":"company",
		"label": __("Company"),
		"fieldtype": "Link",
		"options": "Company",
		"default": frappe.defaults.get_user_default("Company")
	}
]
}
1 Like
#15

No no, I just want to show how many employees the company had on a given date.

#16

hi iMoshi,

if you can draw your report on Excel and share it’s picture here will help community to understand batter about your requirements. so far according to your requirements there are multiple ways to do this.

once you share, community will understand about columns and filters as well for your report.
Regards,
Adnan

#17

for this Requirment, you need to consider Joining Date and Relieving Date Fields from Employee form.

if you have data in these fields. i can write a query for query report and you can simply paste it to get desired result.

Even the comparison can be done in same report with number of current employees.

Regards,

#18

I do have data in Date of Joining, but no data has been recorded for the Relieving dates. I would really really appreciate it if you can help me on this :slight_smile:

#19

the reason you need relieving date is, there is no other way to calculate current employees without this date.

if you’ll insert Relieving date, i’ll send you query considering these two dates.

#20

Ahh, I thought date of joining would’ve been sufficient.

If you have the time, will you please share the script? :smiley: