Frappe Cloud Support Partners Foundation Frappe School

[Tutorial] Script Report / Chart

Hello all

This tutorial is meant to help you get to a working Script Report / Script Chart as quickly as possible and to then build more complex features on that.

It’s not intended for seasoned developers, but the average power-user or SME business owner who want’s to do more than just configure their ERP system via the GUI. Although I’m not a developer myself, the below content and insights were gained from trial-and-error and many hours spent reading this Forum. The coding is of such a nature that you simply have to add single lines or small sections of code to preexisting templates, making it fairly easy to follow along and eventually create your own reports. In the process you’ll learn a bit about Python and JavaScript, just enough to become dangerous! :alien:

It’s also specifically for v13. From v11, ERPNext uses the Frappe Datatable, which replaces the former SlickGrid, to define and render reports. Hence many of the older code snippets on the Forum does not work because the invocation syntax of the various methods have changed, even though they are very similar.

  1. The official documentation ( and is very sparse, and frankly, borderline useless.

  2. In contrast, the Frappe Datatable ( and Charts ( documentation is very good, however the information is not readily usable to a beginner. As a general observation, the code examples are in the library’s native JavaScript, while the scripts to define a report / chart is to be provided in Python.

  3. It’s also worthwhile to have a look at the Script API (

Script Reports can only be created by an Administrator while in Developer Mode.

The report / chart we’ll build is based on static data, not data from a specific Module or DocType, which allows everyone to complete this tutorial without any additional configuration or data. However, once we have a working report / chart, then we’ll replace the static data with data from the User DocType which is part of the Core Module. Hence we’ll define our Script Report to belong to the User DocType.

We’ll create a “Standard” Script Report which, to my mind, means it’s defined in it’s entirety in files on the file-system with nothing persisted in the database. And if something happens to be saved to the database, then the contents of the files supersedes it anyhow. Since all the metadata and scripting logic of the report / chart is saved in files, access to the server’s file-system is mandatory in order to participate in this tutorial.

So, let’s start

  1. Type new rep in the awesome-bar > select New Report

  2. Before we define anything a few words on what is needed to create a Script Report. The purpose of three of the four mandatory fields, Report Name, Is Standard and Report Type, are obvious. However, although mandatory, the Ref DocType is not used at all in defining a Script Report. It’s only used to determine the Module should no Module be specified. So it’s absolutely fine to specify a Ref DocType such as User and Module such as HR or leave it blank to default to the User DocType’s module which is Core. Bear in mind that Core does not have an entry on the Modules list, whereas HR does and you could define your report to appear there. In either case your report will be accessible from the Report List or awesome-bar.

    Provide the following and click Save:

    Report Name : Script Report Tutorial
    Ref DocType : User
    Is Standard : Yes
    Report Type : Script Report

  1. Find the newly created directory and file templates on your server’s file-system with the following CLI command:

    find ~/frappe-bench -iname "script_report_tutorial*"

  1. Note that 4 files were created, including:
    4.1. a .json containing the report configuration metadata
    4.2. a .js and a .py containing the scripting logic

  2. Note that the directory is in .../frappe-bench/apps/frappe/frappe/core/report/ This is where the Core module’s report definitions live, irrespective of the DocType such as User in this case. For another Script Report based on say the HR module, the directory will be in .../frappe-bench/apps/erpnext/erpnext/hr/report/. Notice that the Core module is part of the Frappe app and the HR module part of the ERPNext app.

  3. View the metadata of the newly created report in the json file with this CLI command:

    cat ~/frappe-bench/apps/frappe/frappe/core/report/script_report_tutorial/script_report_tutorial.json

  1. Note that all the configuration detail we provided in step 2 above is contained in this file. You can edit and save the configuration in this file should you wish to do so, provided you invoke cd ~/frappe-bench/; bench restart to ensure your modifications are picked up. A consequence of the report being declared to be “Standard” is that the metadata in this file supersedes the definitions in the GUI form view of the report.

    PS: Not entirely true, the add_total_row setting in the .json file has no impact and the only place to set this is in the GUI.

  2. Just like a custom DocType, a custom Script Report is by default searchable in the awesome-bar: start typing tut and you should find the report.

  3. To get an overview of all the reports on your system, suffix the URL with .../app/report or simply type report list into the awesome-bar.

Static data: .py file

To make it as easy as possible, and to assist learning, we’re not going to use data from the User DocType as yet, but very elementary static data. Later we’ll enhance our report to access data from the User DocType.

  1. Open the file in your favorite script editor.

  2. Amend the script to reflect the following:

def execute(filters=None):
    columns = ["Letter","Number"]
    data = [['c',2],['a',2],['t',8],['s',7]]
    return columns, data
  1. Save the file.

  2. Go to the Script Report Tutorial (search tut in the awesome-bar) and click Show Report. Well done on creating your first Script Report! :tada:


  1. Play with the column Sorting and Filters.
  • Click on the column heading to activate the sort order functionality.
  • Apply text and use the operators, as indicated at the bottom of the report, to the filter boxes below each heading. Note that the filter on the Number column does not work as it does on the Letter column since it’s by implication of fieldtype Int. We have to prefix “=” to filter for a specific value.
  1. To add a message to the report, we augment the .py file by adding the following message variable to the execute function and include it in the return statement. Note that the order in which you return the columns, data and message parameters is crucial for correct execution.
message = ["The equivalent of the letters 'cats' in numbers is '2287'"]
return columns, data, message


  1. Any amendments to the .py should be picked up automatically, provided you either reload the page using the browser’s reload / hard reload button or the ERPNext > User Icon > Reload. I do find on occasion that a reload is not sufficient, for what reason I do not know, in which case I have to invoke

    cd ~/frappe-bench/; bench clear-cache

    or even

    cd ~/frappe-bench/; bench restart

  2. To add a chart, we once again augment the .py file with a chart variable. This time, we opt not to include the message. In order to retain the correct sequence of the returned parameters, we have to return a [] or None instead of the message.

chart = {'data':{'labels':['d','o','g','s'],'datasets':[{'values':[3,6,4,7]}]},'type':'bar'}
return columns, data, None, chart

  1. Okay, we’ve successfully created both a report and a chart. Easy as pie! :partying_face:

    We’ve achieved our overarching objective in just a few steps with minimal scripting. Next we’ll build on this rudimentary script to add more features. Go through each line of code and make sure you understand it’s impact.

  2. To add a report summary, in fact 2 side-by-side summaries, we augment the .py file with:

report_summary = [
return columns, data, message, chart, report_summary


  1. We also add a bit of mascara and lipstick (HTML/CSS) to our rather dull message. For a screenshot of all the following amendments, please view the image at Static data: .js file step 6.
message = [
    "The letters '<b>cats</b>' in numbers is <span style='color:Red;'>2287</span>",
    "The letters '<b>dogs</b>' in numbers is <span style='color:Blue;'>3647</span>"
  1. Let’s add a second data set to our chart, being an indicator whether the letter is a vowel or not, also shown as a bar. Note that, unlike for the above single data set, a legend is automatically displayed, indicating which color represents which data set.
chart = {

  1. But what if we want to show the Vowel as a line and not a bar on the chart? Well, then we have to restructure each data set to indicate which type it is, and define the chart type as a whole as axis-mixed.
chart = {
            #In axis-mixed charts you have to list the bar type first
  1. To add formatting to the data grid, we have to change the columns variable from a list of character strings to a list of dictionaries. A list contains comma separated elements, whereas a dictionary contains comma separated key:value pairs. However, as is in this case, the list contains dictionaries as it’s elements.
columns = [
  1. To structure the data grid as a Tree View, we have to change the nature of the data variable, similar to what we’ve done to the above columns variable. Change the data variable from a list of lists to a list of dictionaries so we can include attributes such as the indent level.
data = [
  1. Note that the tree view works fine as long as you do not change the sort order on any of the columns. To remove the sort functionality, I found that setting sortable had no effect, so I simply deactivate the entire dropdown.
columns = [
  1. To add a Total Line at the base of the grid, which will total all the numerical (eg our Number column which is of type Int) columns by default, we have to ensure both "add_total_row": 1, in the .json file and skip_total_row = False in the .py file is set. Then add skip_total_row to the return statement. Unfortunately, there’s a bug, as it does not work for DocTypes in the Core module, such as User (part of Frappe), however it does work for modules such as HR (part of ERPNext).
return columns, data, None, chart, report_summary, skip_total_row

Static data: .js file

  1. All of the above was achieved with Python scripting in the .py file. So now it’s time to look at the JavaScript file. Open the script_report_tutorial.js file in your favorite script editor. In this file we’ll essentially configure filters. These filters are applied dataset-wide and not per column as we saw in the above step 5. By now you should be familiar with the concept of a list of dictionaries in Python. However, we’re now editing a JavaScript file. JavaScript has similar data structures to a list and dictionary, but they go by different names, array and object respectively, and they differ slightly in the way we use them. So, we’ll define each filter as an object in the filters array. Let’s create a filter for the number column in our data set.
"filters": [{
    fieldname: "number_filter",
    label: "Number Filter",
    fieldtype: "Select",
    //You can supply the options as a string of new-line (\n) separated values,
    //    or as an array of strings such as options: ["1","2","3","4","5","6","7"],
    options: "1\n2\n3\n4\n5\n6\n7",
    default: 3
  1. Save this file and ensure it replaces the current version of it in the cache by invoking any of the methods we discussed above, such as a browser reload or even bench restart.

  2. View the placement of the filter right at the top-left of the form. However, right now the filter has no effect on anything as yet. Let’s change that. In the .py file, add the following code below the line where we initially assign the message variable with it’s list of strings. Note that we’re not editing the existing messages list, although we could do that, but appending to it.

message.append("<br>The value of the Number Filter is : " + filters.get("number_filter"))
  1. So, now that we know how to access the filter, let’s use it to filter the cats data set. Add the following code below the line where the data variable is initially assigned it’s values, as a list of dictionaries. This code creates a new list variable of the same name, data, by retaining only dictionaries which has an attribute of number with a value greater than the filer’s value. Also note that we are accessing the filter differently as filters.number_filter and not with the .get() method as above. The choice is yours, but I prefer the get method.
data = [dic for dic in data if dic["number"] > int(filters.number_filter)]
  1. Save this file and reload the cache to see how a filter value of 1 is the only option which shows all 4 letters in the data grid.

  2. Below is a collection of interesting filters. Try to include them in the array of filters in the .js file, remembering to separate each object with a comma. For the absolute beginner, it might be insightful to add them one by one so you can see the effect of each as you learn what the code does.

    fieldname: "check_filter",
    label: "Check Filter",
    fieldtype: "Check",
    default: 1,
    fieldname: "date_filter",
    label: "Date Filter",
    fieldtype: "Date",
    //Note the following default attribute, which contains an API call
    default: frappe.datetime.get_today()
//Note the fieldtype which links to the User DocType, as defined in the options attribute.
//Also, the reqd attribute which causes this filter selection to be mandatory before the report / chart renders.
    fieldname: "user_filter",
    label: "User Filter",
    fieldtype: "Link",
    options: "User",
    reqd: 1,

Dynamic data: Single table

  1. It’s time to amend our script to access real-time, in-production data from the User DocType. Replace the entire contents of the .py file with the following, including the initial import statements. Note that since we access the database directly, the “ID” field is actually the “name” column. Furthermore, we have access to columns which are not ordinarily exposed in the GUI as fields, such as the record’s creation date and time.
import frappe
import datetime
def execute(filters=None):
    columns = [
        {'fieldname':'first_name','label':'First Name','fieldtype':'Data'},
        {'fieldname':'last_name','label':'Last Name','fieldtype':'Data'},
    data = frappe.db.get_all('User', ['name','first_name','last_name','creation'])
    frappe.msgprint("<span style='color:Red;'>Once this popup has served it's purpose, then comment out it's invocation, viz. #frappe.msgprint...</span><br><br>" + "<pre>{}</pre>".format(frappe.as_json(data)))
    datefilter = datetime.datetime.strptime(filters.date_filter,"%Y-%m-%d").date()
    today =
    data = [dic for dic in data if > datefilter]
    data = sorted(data, key=lambda k: k['first_name'])
    chart = {
        'title':"Script Chart Tutorial : Days since the user's database record was created",
            'labels':[str(dic.first_name) + " " + str(dic.last_name) for dic in data],
            'datasets':[{'values':[(today - for dic in data]}]
        'lineOptions':{'hideDots':0, 'dotSize':6, 'regionFill':1}
    report_summary = [{"label":"Count","value":len(data),'indicator':'Red' if len(data) < 10 else 'Green'}]
    return columns, data, None, chart, report_summary
  1. There’s no need to amend the .js file. Save the changes to the .py file and reload. Note that the frappe.msgprint API call will cause a popup to display the data returned from the database. The intention of this is to familiarize you with the nature of the data, in this case a list of dictionaries (strictly not true as it is JSONified). Since you already know how to work with data structures such as a list and a dictionary, the rest of the code should hopefully make more sense.

    By un-commenting (deleting the ‘#’) the frappe.msgprint("<pre>{}</pre>".format(filters)), being the first line of code within the execute function of the .py file, you’ll be able to investigate the filters variable, but also notice that a filter with a reqd attribute requires a value before this execute function executes. Furthermore, notice that we have not JSONified the output this time.

  2. Apply the Date Filter to the creation date of both the grid and the chart. You might have to go back a week or so until you have sufficient data to display. The further back in time you go the more data you’ll receive, and once you have 10 or more records the count will turn green.

  1. Note that the grid and chart displays are related to the same data set, data, unlike in the previous cats and dogs data sets. Unfortunately we cannot have multiple (related or unrelated) grids / charts other than what we’ve done with cats and dogs. On a related note, you cannot only have a chart without a grid, i.e. return None, None, None, chart does not work.

  2. Note that the dotsize attribute has no effect, which might be indicative of a bug or me not understanding how to use it :sleepy:

Dynamic data: Multiple tables

  1. The above frappe.db.get_all API call is fine for accessing a single table’s data. However, in order to join data from multiple tables, we have to use yet another coding language called SQL. For this we have to use frappe.db.sql. In fact we are going to use frappe.db.sql twice in the below code segment. First to join data from the User and User Type tables, specifically to find the Owner of the User Type. Then secondly to extract all the activities incurred by each user from the Activity Log table. We cannot join the activity log data in the same way we join the user type data, since we’re going to build a hierarchy (tree view) of all the activities by each user, and that we’ll do in Python once we have both datasets. This very same approach can be followed for any parent - child DocType combination. In order the define the hierarchical indent level of each row of data, we have to add the indent attribute to each row, both the parent row and for each child row.

  2. We need to replace the current execute function by either commenting it out in it’s entirety, or simply just renaming it to single_execute so that it does not get invoked automatically. Then copy the following execute function to the bottom of the file.

def execute(filters=None):
	columns = [
		{'fieldname':'full_name','label':'Full Name'},
		{'fieldname':'user_type','label':'User Type'},
	data = []
	parent = frappe.db.sql("SELECT, t1.full_name, t1.user_type, t2.owner FROM `tabUser` AS t1 JOIN `tabUser Type` AS t2 ON t1.user_type =", as_dict=1)
	for dic_p in parent:
		dic_p["indent"] = 0
		child = frappe.db.sql("SELECT subject, status, creation FROM `tabActivity Log` WHERE user = '" + dic_p["name"] + "'", as_dict=1)
		for dic_c in child:
			dic_c["indent"] = 1
	return columns, data
  1. Note that the tree view works fine as long as you do not change the sort order on any of the columns. I presume the treeView: true attribute will solve this but I do not know where and how to set it :sweat: The treeView and all of it’s related attributes, such as name_field, parent_field and has_value, we read about on this forum might be remnants of the former SlickGrid?

  2. The second SELECT invocation, which supplies the child variable with it’s data, contains a WHERE clause. A WHERE clause is used to filter the data during the extraction from the database. The condition we’ve applied to the WHERE is to return only data related to a specific individual user, namely dic_p["name"].

  3. I’ve commented out all the frappe.msgprint invocations, however you’re welcome to un-comment them to investigate the data assigned to the respective variables. Please feel free to add your own frappe.msgprint statements wherever you deem necessary to investigate the data.

  4. An alternative to printing the variable’s data after the SQL statement executed is to run the SQL statement in bench mariadb. This allows you to build the SQL statement until it’s error free and then add it to the .py file. Note that we cannot simply copy the second SQL statement to bench mariadb as it contains a variable reference, dic_p["name"], which makes no sense outside of the .py script. In cases such as these, replace the variable with a literal name, such as Administrator in this case. Remember to terminate the SQL statement with a semicolon, ;. Once the SQL statement executed, you might have to press Page Down or Right arrow to view a large collection of data, and eventually press q to return to the SQL prompt. Press Ctrl-C to exit.

cd ~/frappe-bench/; bench --site site1.local mariadb
SELECT, t1.full_name, t1.user_type, t2.owner FROM `tabUser` AS t1 JOIN `tabUser Type` AS t2 ON t1.user_type =;
SELECT subject, status, creation FROM `tabActivity Log` WHERE user = 'Administrator';
  1. Other useful statements while investigating and building your SQL, are
SHOW tables;
DESC tabUser;
SELECT * FROM tabUser;
  1. We did not apply any filters to the data, either as part of the SQL at the time of extraction from the database, or as part of the Python code, however, in practice it’s a good approach to do so in the SQL WHERE clause so that we’re not returning the entire table of data to our front-end.

Dynamic data: Working with filters

  1. Now finally, let’s incorporate filters into our report. This is a little more technical, and I’ve run into problems myself. Due to my limited coding skill, this report is working but still needs refinement, lots of it. It’s not intended to be a functional report other than trying out a few filters.

    1.1. A User filter which is visible if the user is a member of the Administrator or System Manager role, otherwise hidden and it’s value defaults to the logged-in user.

    1.2. A Subject filter of MultiSelectList type, with it’s options built dynamically subject to the value of the User filter. Unlike other filters, we cannot simply link it to a table since we do not want the primary key to be the options, but the subject column, hence we have to build the list of options programatically.

    1.3. Two date filters, with coding to ensure they work correctly.

  2. Copy the following to your .js and .py files.

frappe.query_reports["Script Report Tutorial"] = {
	"treeView": true,
	"name_field": "name",
	"parent_field": "name",
	"initial_depth": 1,			//The level to which the initial rendering will expand to
	//onload: function () {
		//var filter = frappe.query_report.get_filter("name_filter");
		//if (frappe.user.has_role("Administrator") || frappe.user.has_role("System Manager")) {
		//} else {
	"filters": [
			fieldname: "name_filter",
			label: "Name Filter",
			fieldtype: "Link",
			options: "User",
			reqd: 1,
			default: (frappe.user.has_role("Administrator") || frappe.user.has_role("System Manager")) ? "" :,
			hidden: !(frappe.user.has_role("Administrator") || frappe.user.has_role("System Manager")),
			on_change: function(query_report){
				query_report.set_filter_value('subject_filter', []);
			fieldname: "subject_filter",
			label: "Subject Filter",
			fieldtype: "MultiSelectList",
			get_data: function(txt) {
				if (frappe.query_report.get_filter_value('name_filter')) {
					var name = frappe.query_report.get_filter_value("name_filter");
					return frappe.db.get_list("Activity Log", {fields: ['subject AS value', 'subject AS label', 'subject AS description'], filters: {"user": name}, distinct: 1, order_by: "subject"});
					//return frappe.db.get_link_options("Activity Log", txt, {"user":name});
				else {
					return [];
			on_change: function(query_report) {
			fieldname: "date_from_filter",
			label: "Date From Filter",
			fieldtype: "Date",
			default: frappe.datetime.add_months(frappe.datetime.get_today(), -1)
			fieldname: "date_to_filter",
			label: "Date To Filter",
			fieldtype: "Date",
			default: frappe.datetime.get_today()

def execute(filters=None):
	if filters.date_from_filter and filters.date_to_filter:
		if filters.date_from_filter > filters.date_to_filter:
			frappe.throw("The 'From Date' ({}) must be before the 'To Date' ({})".format(filters.date_from_filter, filters.date_to_filter))
	if filters.date_from_filter == None:
		filters.date_from_filter = "2000-01-01"
	if filters.date_to_filter == None:
		filters.date_to_filter = frappe.datetime.get_today()
	conditions = " WHERE creation BETWEEN '" + filters.date_from_filter + "' AND '" + filters.date_to_filter + "'"
	if filters.get("name_filter"):
		name = filters.get("name_filter")
		conditions += f" AND user = '{name}'"
	# if len(filters.get("subject_filter")) > 0:
		# subject = ','.join("'{0}'".format(x) for x in filters.get("subject_filter"))
		# conditions += " AND subject IN (" + subject + ")"
	columns = [
		{'fieldname':'full_name','label':'Full Name','width':'250'},
	data = []
	users = frappe.get_list("User", fields=["name","full_name","'0' AS indent"], filters=[{"name":filters.get("name_filter")}], order_by='full_name ASC')
	for user in users:
		user["has_value"] = True
		activities = frappe.db.sql("SELECT subject, status, creation, '1' AS indent FROM `tabActivity Log`" + conditions, as_dict=1)
		for activity in activities:
			activity["has_value"] = False
	return columns, data


There are many other enhancements you can add to either the grid or the chart, such as:

  1. Change the color of either the value or the background of a cell or an entire row in the grid subject to some threshold(s) in the data.
  2. Add advanced features such as a button in a grid cell to invoke a js/py script, eg a modal popup.
  3. Customizing the Tooltip on the chart
  4. Add an Export to SVG button

As I’ve mentioned earlier, I prefer the business angle of things, however I’d appreciate developers to comment on the so called bugs and dead-ends I’ve experienced. While a competent developer might cringe at my coding, I also invite you to help me understand how to do things better, however please keep the intended audience in mind when you do so.

We have only scratched the surface, and a very particular itch. You now have the bulk of what is required to build your very own awesome Script Report / Script Chart. But, do not be fooled, as for all coding endeavors, the devil is in the detail! Happy learning.


Hi @EugeneP

Thanks a lot for this very insightful piece


1 Like

Very good initiative, will give a try, thx

1 Like

AWESOME! :partying_face:
Thank you @EugeneP!!!

1 Like

Thanks for your time, effort and Helpfull Contribution towards community. Appreciate :+1::+1:


This is super cool, and the details are extremely helpful.

It’s also possible to do most things on the client-side without a custom app. There are a few limitations regarding the available python functions, but it’s been amazing to see how developer-friendly Frappe continues to become:

1 Like

Due to the great response this topic has had, I’ve been updating it frequently as I myself learn more. Should you uncover something yourself then please share that.

Last but certainly not least, thanks for the likes and words of encouragement. It’s good to know you found benefit from reading it.

Thanks @EugeneP for the excellent write up!
Can this be done also on sites hosted on Frappe Cloud or only for self-hosted sites?

1 Like

I’m not sure. I presume not, since you have to have access to the file system.

1 Like

Yes, you can. Frappe cloud allows you to install arbitrary apps hosted on GitHub to your bench.

Thanks @peterg! Are you referring to this:
I am fairly new to ERPnext and Frappe Cloud. Do you have any links at hand that would describe the process of wrapping what @EugeneP has described above into a custom app?

Yes, exactly. You need to create an app on a development bench (instructions here), then follow EugeneP’s excellent instructions. Then, follow those instructions to deploy to frappe cloud.

Alternately, it’s possible you can do what you need client side. See the link I posted a few messages back for details.

1 Like

Thanks @peterg! I’ll check that out.

I’ve updated the tutorial with additional content as I’ve learned more myself and added a section on advanced filters. I hope it’ll come in handy to someone…

1 Like