Developing custom reports in ERPNext

Its easy to develop custom reports in ERPNext. All it requires is good bit of knowledge of Python and some knowledge of SQL.

Note : It is required that you have installed ERPNext in developer mode. And a custom app is created. 

1. Create a new report

In the awesome bar type new report to start designing new report.

Its important to note that report type is 'Script Report'.

This will create a folder called "skillmap-report" in reports folder of your app. In this case its is custom app - "Credence HR". The scaffolding created by ERPNext includes skillmap_report.js and skillmap_report.py.

skillmap_report.js looks like this.

frappe.query_reports["Skillmap Report"] = {
    "filters": [
        {
            fieldname: "department",
            label: __("Department"),
            fieldtype: "Link",
            reqd: 0,
            options : 'Department',

          },
          {
            fieldname: "grade",
            label: __("Grade"),
            fieldtype: "Link",
            reqd: 0,
            options : 'Employee Grade',

          },
          {
            fieldname: "from_date",
            label: __("From date"),
            fieldtype: "Date",
            reqd: 0,
          },
          {
            fieldname: "to_date",
            label: __("To date"),
            fieldtype: "Date",
            reqd: 0,
          },

    ]
};

The filters provided are displayed in the report for the user to select.

And the skillmap_report.py looks like this.

from __future__ import unicode_literals
import frappe
from frappe import _
import pandas as pd

def execute(filters=None):
    columns, data = [], []
    deptsql = "E.department = '{dept}'".format(dept=filters.department) if filters.department else "1 = 1"
    gradesql = "E.grade = '{grade}'".format(grade=filters.grade) if filters.grade else '1=1'
    fromdatesql = "S.evaluation_date >= DATE('{from_date}')".format( from_date=filters.from_date) if filters.from_date else '1=1'
    todatesql = "S.evaluation_date <= DATE('{to_date}')".format( to_date=filters.to_date) if filters.to_date else '1=1'
    skillsql = """
    SELECT E.employee_name,  S.skill, S.proficiency
    FROM `tabEmployee Skill Map` M
    JOIN `tabEmployee Skill` S ON M.employee = S.parent
    JOIN `tabEmployee` E ON E.name = M.employee
    WHERE ({deptsql}) AND ({gradesql}) AND ({fromdatesql}) AND ({todatesql})
    """.format( deptsql=deptsql, gradesql=gradesql, todatesql = todatesql, fromdatesql = fromdatesql)
    data = frappe.db.sql(skillsql, as_dict=1)
    if len(data) == 0:
        frappe.msgprint('No data!!!')
        return [], []

    dataframe = pd.DataFrame.from_records(data)

    skills = dataframe.skill.unique().tolist()
    dataframe = dataframe.pivot(index="employee_name", columns="skill", values="proficiency")
    dataframe.fillna(0, inplace = True)
    dataframe['total']=dataframe.loc[:,skills].sum(axis=1)

    skills = [{"fieldname": skill, "label": _(skill), "fieldtype": "Int", "width": 150, } for skill in skills]
    columns = [ { "fieldname": "employee_name", "label": _("Name"), "fieldtype": "Data", "width": 200 }]
    columns += skills
    columns+=[ { "fieldname": "total", "label": _("Total"), "fieldtype": "Int", "width": 100 }]

    data = dataframe.reset_index().to_dict('records')

    return columns, data

The python file is expected to have one function called execute which should return columns, data.

The above code uses Pandas and pivots the data from the skill map to provide a report which looks like below.