Frappe Script Reports: Treeview and Extensions
Frappe’s script report feature is incredibly powerful, offering immense flexibility to tailor your reporting needs. What’s even better is how straightforward it is to extend existing reports to fit your specific requirements. In this guide, we’ll walk through two key aspects:
Part I: Building a Script Report with Treeview
Several ERPNext reports within the Accounts module, like ‘Trial Balance’ and ‘Dimension-wise Accounts Balance Report,’ elegantly utilize a treeview structure. Implementing this is surprisingly easy.
For a detailed guide on how to get started with reports refer these excellent posts:
- [Tutorial] Script Report / Chart
- [Tutorial] Script Reports in ERPNext: A Step-by-Step Guide
- How to create a Script Report in V13 - #6 by mohamed-ameer
Let’s create a treeview report specifically for Sales Persons.
Create a new script report named ‘Accounts Receivable Sales Person’. Then, add the following code to your report controller file (e.g., accounts_receivable_sales_person.py):
# accounts_receivable_sales_person.py
import frappe
def execute(filters=None):
return get_columns(filters), get_data(filters)
def get_data(filters):
sales_person_tree = {}
for d in frappe.db.sql("""
SELECT
tsp.name sales_person,
GROUP_CONCAT(tsp2.name ORDER BY tsp2.lft) AS parents ,
tsp.commission_rate
FROM `tabSales Person` tsp
LEFT OUTER JOIN `tabSales Person` tsp2
ON tsp2.lft < tsp.lft AND tsp2.rgt > tsp.rgt
GROUP BY tsp.name
ORDER BY tsp.lft;""", as_dict=True):
row = sales_person_tree.setdefault(d.sales_person, d)
parents = d.parents and d.parents.split(",") or []
row.update({
"parents": parents,
"indent": len(parents),
"has_value": True,
"parent_sales_person": d["parents"] and d["parents"][-1] or None
})
row.pop("parents")
return list(sales_person_tree.values())
def get_columns(filters):
return [
{
'label': 'Sales Person',
'fieldname': 'sales_person',
'fieldtype': 'Link',
'options': "Sales Person",
'width': 350
}, {
'label': 'Commission Rate',
'fieldname': 'commission_rate',
'fieldtype': 'Float',
'options': "",
'width': 120
}
]
For the treeview functionality to work correctly, you’ll also need to add specific treeview properties to your report’s JavaScript file (e.g., accounts_receivable_sales_person.js):
// accounts_receivable_sales_person.js
frappe.query_reports["Sales Person"] = {
"filters": [
],
initial_depth: 1,
tree: true,
parent_field: "parent_sales_person",
name_field: "sales_person",
};
And that’s it! You should now have a functional script report with a clear treeview, similar to this:
Part II: Extending an Existing ERPNext Report
The Accounts Receivable Summary (ARS) report in ERPNext is excellent for viewing outstanding amounts customer-wise. However, it lacks information about the associated Sales Person. Let’s enhance this report by integrating Sales Person data.
Our strategy is to merge data from the ARS report into our Sales Person tree report. This will allow us to display sales person-wise outstanding amounts, along with a detailed list of customers under each sales person.
We’ll modify the accounts_receivable_sales_person.py file from Part I. Let’s break down the process into easy-to-follow steps:
1. Fetch Columns and Data from the AR Summary Report
First, we’ll import and execute the original accounts_receivable_summary report to get its columns and data.
from erpnext.accounts.report.accounts_receivable_summary.accounts_receivable_summary import execute as _execute
columns, accounts_receivable_summary, *_ = _execute(filters=frappe._dict(filters))
2. Add a Sales Person Column and remove columns that are not needed
Next, we’ll add a sales_person column to the ARS data and filter the original ARS columns to include only what’s necessary.
columns = get_columns(columns)
# ... rest of the code ...
def get_columns(columns):
INCLUDE_COLUMNS = [ 'sales_person', 'party', 'outstanding', 'range1', 'range2', 'range3', 'range4', 'range5', 'total_due', 'currency' ]
return [
{
'label': 'Sales Person',
'fieldname': 'sales_person',
'fieldtype': 'Data',
'options': None,
'width': 200}
] + [d for d in columns if d["fieldname"] in INCLUDE_COLUMNS]
3. Integrate ARS Data into the Sales Person Tree
Now, we’ll map customers to their respective sales persons and then embed the customer data from the ARS report into our sales person tree structure. Customers will appear as sub-rows under their assigned sales person.
# map customers to sales person
customer_sp_map = {}
for d in frappe.db.sql("""
select parent , sales_person from `tabSales Team` tst WHERE parenttype = 'Customer'
""", as_dict=True):
customer_sp_map[d.parent] = d.sales_person
# set customers data in sp_tree
for row in accounts_receivable_summary:
if row.party in customer_sp_map and customer_sp_map[row.party] in sp_tree:
customers = sp_tree[customer_sp_map[row.party]
].setdefault("customers", [])
customers.append(row)
4. Calculate and Propagate Totals
We need to calculate the sum of outstanding amounts for each sales person from their associated customers and then aggregate these totals up the sales person hierarchy (to parent sales persons).
for _, d in sp_tree.items():
for col in TOTAL_FIELDS:
d[col] = sum(item.get(col, 0) for item in d.get("customers", [])) # Use .get() with default for safety
# add totals to parents
for parent in d["parents"]:
if parent in sp_tree:
for col in TOTAL_FIELDS:
sp_tree[parent][col] = flt(
sp_tree[parent][col]) + flt(d[col])
5. Restructure Data for the Report Output
Finally, we’ll transform our sales person dictionary into a list, removing temporary properties like parents and correctly setting the indent and parent_sales_person for customer rows to ensure proper treeview display.
data = []
# extract data from sales person dict
for _, d in sp_tree.items():
if not d["outstanding"] and not d.get("customers"): # Check for customers too
continue
d.pop("parents", None)
customers = d.pop("customers", None) or []
data.append(d)
for customer in customers:
customer["indent"] = 1 + d.get("indent", 0) # Use .get() with default
customer["parent_sales_person"] = d.sales_person
data.append(customer)
With these modifications, your report will now display outstanding amounts by sales person, with customers nested underneath, as shown:
6. Incorporate filters from Accounts Receivable Summary
To leverage the filtering capabilities of the original “Accounts Receivable Summary” report, simply copy the contents of accounts_receivable_summary.js into your accounts_receivable_sales_person.js file.
Complete Controller Code
Here’s the complete, refined code for your accounts_receivable_sales_person.py controller:
import frappe
from frappe.utils import flt
from erpnext.accounts.report.accounts_receivable_summary.accounts_receivable_summary import execute as ars_execute
TOTAL_FIELDS = ('outstanding', 'range1', 'range2',
'range3', 'range4', 'range5', 'total_due') # Added total_due
def execute(filters=None):
if not filters:
filters = frappe._dict({})
if "party_type" not in filters:
filters["party_type"] = "Customer"
columns, ar_summary, * _ = ars_execute(filters=frappe._dict(filters))
new_columns = get_columns(columns)
new_data = get_data(ar_summary)
return new_columns, new_data
def get_data(ar_summary):
sp_tree = {}
for d in frappe.db.sql("""
SELECT
tsp.name sales_person,
GROUP_CONCAT(tsp2.name ORDER BY tsp2.lft) AS parents ,
0 outstanding, 0 range1, 0 range2, 0 range3, 0 range4, 0 range5, 0 total_due
FROM `tabSales Person` tsp
LEFT OUTER JOIN `tabSales Person` tsp2
ON tsp2.lft < tsp.lft AND tsp2.rgt > tsp.rgt
GROUP BY tsp.name
ORDER BY tsp.lft;""", as_dict=True):
sp_tree.setdefault(d.sales_person, d)
parents = d.parents and d.parents.split(",") or []
sp_tree[d.sales_person].update({
"parents": parents,
"indent": len(parents),
"has_value": True,
"parent_sales_person": d["parents"][-1] if d["parents"] else None # More concise
})
# map customers to sales person
customer_sp_map = {}
for d in frappe.db.sql("""
select parent , sales_person from `tabSales Team` tst WHERE parenttype = 'Customer'
""", as_dict=True):
customer_sp_map[d.parent] = d.sales_person
# set customers data in sp_tree
for row in ar_summary:
if row.party in customer_sp_map and customer_sp_map[row.party] in sp_tree:
customers = sp_tree[customer_sp_map[row.party]
].setdefault("customers", [])
customers.append(row)
# set totals for each node
for _, d in sp_tree.items():
for col in TOTAL_FIELDS:
d[col] = sum(item.get(col, 0) for item in d.get("customers", []))
# add totals to parents
for parent in d["parents"]:
if parent in sp_tree:
for col in TOTAL_FIELDS:
sp_tree[parent][col] = flt(
sp_tree[parent][col]) + flt(d[col])
data = []
# extract data from sales person dict
for _, d in sp_tree.items():
# Only include if there's outstanding or customers
if not d["outstanding"] and not d.get("customers"):
continue
d.pop("parents", None)
customers = d.pop("customers", None) or []
data.append(d)
for customer in customers:
customer["indent"] = 1 + d.get("indent", 0)
customer["parent_sales_person"] = d.sales_person
data.append(customer)
return data
def get_columns(columns):
INCLUDE_COLUMNS = ['sales_person', 'party', 'outstanding', 'range1',
'range2', 'range3', 'range4', 'range5', 'total_due', 'currency']
return [
{
'label': 'Sales Person',
'fieldname': 'sales_person',
'fieldtype': 'Data',
'options': None,
'width': 200
}
] + [d for d in columns if d["fieldname"] in INCLUDE_COLUMNS]