How to create tree structure in script report

ERPNext
1

Hello
How can I create a tree structure report via a script report?

I know the Trial Balance report is a tree structure report but I am not able to create the same report.

can anyone explain to me this basic structure

		"formatter": erpnext.financial_statements.formatter,
		"tree": true,
		"name_field": "what_should_come_here",
		"parent_field": "what_should_come_here",
		 "initial_depth": 3
2

Anyone you can help??

3

Any Update on this ?

4

did you solve ?

5

Yes for reference please see trial balance report structure

6

Thanks for your reply

my case i want to build tree structure for Tasks

so in Trial balance report i found below line which responsible to create this structure

accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)

how can i change it to be task not account

thanks in Advance

7

Hi,
Adapt your report_name.js similar to this

frappe.query_reports["Report_name"] = {
	"filters": [
        {
            fieldname: 'company',
            label: __('Company'),
            fieldtype: 'Link',
            options: 'Company',
        },
	],
	"treeView": true,
	"name_field": "task",
	"parent_field": "parent_task",
	"initial_depth": 2
};
1 Like
8

Hey @Nader_Adel, Sorry for the late reply I was busy with other tasks.

So what I have done is

frappe.query_reports["Query Report"] = {
    "filters": [
        {}  ],
     "formatter": function (row, cell, value, columnDef, dataContext, default_formatter) {
        if (columnDef.df.fieldname == "account") {
            value = dataContext.account;
            columnDef.df.is_tree = true;
        }

        value = default_formatter(row, cell, value, columnDef, dataContext);
        if (!dataContext.parent_account) {
            var $value = $(value).css("font-weight", "bold");
            if (dataContext.warn_if_negative && dataContext[columnDef.df.fieldname] < 0) {
                $value.addClass("text-danger");
            }

            value = $value.wrap("<p></p>").parent().html();
        }
        return value
    },
    "tree": true,
    "name_field": "account",
    "parent_field": "parent_account",
    "initial_depth": 1
}

So now from the python file make dict structure like

 {'account': first_account,  'parent_account': parent_key,
                                 'indent': 0, 'has_value': True,}

If Parent has a value then children then has_value should be True otherwise False.
indent is the level how the tree structure will be shown (means with how be tabs spaces zero means no space)

I hope this solve this Query

Thanks & Regards
Rohan Jain

9

Hi @ROHAN_JAIN1 can you help me with tree structure view in script report.
I followed what you say but cant able generate tree structure in report.

import frappe

def execute(filters=None):
    columns, data = [], []

    # Define columns
    columns = [
        {"fieldname": "name", "label": "Name", "fieldtype": "Data", "width": 150},
        {"fieldname": "parent_sample_tree", "label": "Parent", "fieldtype": "Data", "width": 150},
        {"fieldname": "full_name", "label": "Full Name", "fieldtype": "Data", "width": 200}
    ]

    # Fetch data from Sample Tree Doctype
    data = frappe.db.sql("""
        SELECT 
            name1 as name,
            parent_sample_tree,
            full_name
        FROM 
            `tabSample Tree`
        ORDER BY
            parent_sample_tree, name1
    """, as_dict=1)

    return columns, data

frappe.query_reports[“Tree test”] = {
“filters”: ,
tree: true,
name_field: “name”,
parent_field: “parent_sample_tree”,
initial_depth: 3
};

10

add in your standard report python file

import frappe
from frappe import _

def execute(filters=None):
columns, data = ,
columns = get_columns()
data = get_data()
return columns, data

def get_accounts():
return frappe.get_all(
“Account”,
fields=[“name”, “parent_account”, “is_group”, “disabled”],
order_by=“lft”,
)

def get_data():
accounts = get_accounts()
update_indent(accounts)

return accounts

def update_indent(accounts):
for account in accounts:

	def add_indent(account, indent):
		account.indent = indent
		for child in accounts:
			if child.parent_account == account.name:
				add_indent(child, indent + 1)
				print("d",child)

	if account.is_group:
		add_indent(account, account.indent or 0)

def get_columns():
columns = [
{
“label”: _(“Accounts”),
“fieldname”: “name”,
“fieldtype”: “Link”,
“options”: “Account”,
“width”: 200,
},

]


return columns

add in your javascript file

frappe.query_reports[“Accounts Tree”] = {
“filters”: [

{
    "fieldname":"company",
    "label": __("Company"),
    "fieldtype": "Link",
    "options": "Company",
    "reqd": 1,
    "default": frappe.defaults.get_user_default("Company")
},

],
“initial_depth”: 0,
“tree”: true,
“parent_field”: “parent_account”,
“name_field”: “account”
};