iMoshi
October 15, 2019, 7:15am
#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
sanjay
October 15, 2019, 8:47am
#2
Develop a script report as you can’t provide filter in a Query report.
iMoshi
October 15, 2019, 9:31am
#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
joelios
October 15, 2019, 12:53pm
#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
adnan
October 15, 2019, 1:09pm
#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.
iMoshi
October 15, 2019, 1:39pm
#6
I do have it installed on my server, running v12.1.15
joelios
October 15, 2019, 1:42pm
#7
Good
Do you have enabled developer_mode?
iMoshi
October 15, 2019, 2:00pm
#10
Did so. Copied pasted .py and .js from employee birthday report, and replaced some fields but to no avail.
joelios
October 15, 2019, 2:02pm
#11
Can you show me the code that you already have and explain me what else do you need?
iMoshi
October 15, 2019, 2:17pm
#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.
joelios
October 15, 2019, 2:22pm
#13
So you want the exactly same report as Employee Birthday, just switch date of birth with date of joining?
joelios
October 15, 2019, 2:31pm
#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
iMoshi
October 16, 2019, 5:00am
#15
No no, I just want to show how many employees the company had on a given date.
adnan
October 16, 2019, 5:14am
#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
adnan
October 16, 2019, 5:20am
#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,
iMoshi
October 16, 2019, 5:48am
#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
adnan
October 16, 2019, 6:04am
#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.
iMoshi
October 16, 2019, 6:05am
#20
Ahh, I thought date of joining would’ve been sufficient.
If you have the time, will you please share the script?