How to create a minimalist Script Report CHART?

Quick Note: Script Reports are only available to self-hosted ERPNext.
If you pay for a hosted account you are out of luck.

If you run self-hosted V13 and want to learn how to add a chart to a Script Report see below.


Meanwhile, pardon me while I rant for a moment

It took me several days and help from @max_morais_dmm to pick through the skimpy documentation and finally create a nice clean, understandable example of a Script Report.

I was appalled to see the long list of people being completely ignored in this forum for asking basic getting started questions about reports and especially about charts. Googling for “ERPNext charts” leads to pages and pages of unanswered questions here.

Really the information on this topic is more like Qanon Drops than serious professional documentation.

Finally, I tried googling simply “erpnext documentation”. In the “User Manual” I looked through every one of the “cards” trying to find something about charts, took a guess at customization and found a “Documents and Reports” section with no cards leading to creating reports, let alone charts. However, clicking “View Full contents” got me to “Customize ERPNext >> 1.2 Documents and Reports >> 2. Making Custom Reports”. Part 3 of that page has the section on “Script Reports”, but it does little more than tell you to go to this page after telling you that the horrendously complicated “Financial Analytics” source code has everything you need. So I go to that page and find it introduces the topic with a brief overview and scattered code fragments grounded in no clearly defined context, before telling you that the horrendously complicated “Balance Sheet” source code has everything you need.

However … finally … right down at the very bottom of that page I find that there is nothing at all about adding a chart to a Script Report. The message is clear, if you cannot read dense undocumented source code while also running the business you created that could benefit from ERP, then you don’t matter, we don’t need you, you’re not welcome, get lost.


Adding a chart to a Script Report chart.

To create a Script report with an embedded chart follow the steps below.

You will end up with a self-contained and self-standing report and chart that have no internal dependencies on any other ERPNext data or functionality. Hopefully the steps and data structures are clear enough that you can then begin to add your own functionality and data dependencies in small enough increments that you can address one small issue at a time, rather than “it didn’t work”

#1 Open list of reports

#2 Click on “New”

#3 Fill out the form

Fill the fields as shown in the image (if the image is not big enough, click on it) and click “Save”. Note that you may refer to your own Module and DocType instead of Stock >> Warehouse. You can learn to do that in these instructions. Also note that, for this example, the Module you refer to is never accessed in any way. Specifying it does one thing: indicate where on the server the report source files should be located.

The result should look like this:

#4 Find the server side code

On your server visit the directory:

${YOUR_BENCH}/apps/erpnext/erpnext/stock/report

Listing directory contents should show you:

erpdev@erpserver:YOUR_BENCH/apps/erpnext/erpnext/stock/report$ ls -la
total 148
drwxr-xr-x 37 erpdev erpdev 4096 Sep 30 11:31 .
drwxr-xr-x 16 erpdev erpdev 4096 Aug 29 11:55 ..
      :       :       :       :       :       : 
drwxr-xr-x  3 erpdev erpdev 4096 Aug 28 13:04 material_requests_for_which_supplier_quotations_are_not_created
drwxr-xr-x  2 erpdev erpdev 4096 Sep 30 11:31 minimal_script_report_with_chart
drwxr-xr-x  3 erpdev erpdev 4096 Aug 29 11:55 ordered_items_to_be_delivered
      :       :       :       :       :       : 
erpdev@erpserver:YOUR_BENCH/apps/erpnext/erpnext/stock/report$ 

Step down into, and list the content of:

minimal_script_report_with_chart

#5 Edit the server side code

These are the files you need to edit

erpdev@erpserver:YOUR_BENCH/apps/erpnext/erpnext/stock/report/minimal_script_report_with_chart$ ls -la
total 20
drwxr-xr-x  2 erpdev erpdev 4096 Sep 30 11:31 .
drwxr-xr-x 37 erpdev erpdev 4096 Sep 30 11:31 ..
-rw-r--r--  1 erpdev erpdev    0 Sep 30 11:31 __init__.py
-rw-r--r--  1 erpdev erpdev  225 Sep 30 11:31 minimal_script_report_with_chart.js
-rw-r--r--  1 erpdev erpdev  720 Sep 30 11:31 minimal_script_report_with_chart.json
-rw-r--r--  1 erpdev erpdev  250 Sep 30 11:31 minimal_script_report_with_chart.py
erpdev@erpserver:YOUR_BENCH/apps/erpnext/erpnext/stock/report/minimal_script_report_with_chart$ 

In the JavaScript file, minimal_script_report_with_chart.js, replace this code …

// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
// For license information, please see license.txt
/* eslint-disable */

frappe.query_reports["Minimal Script Report with Chart"] = {
        "filters": [

        ]
};

… with this code …

// Copyright (c) 2016, Warehouseman and contributors
// For license information, please see license.txt
/* eslint-disable */

frappe.query_reports["Minimal Script Report with Chart"] = {
  "filters": [
    {
      fieldname: "xAxisField",
      label: "Range Selection Filter",
      fieldtype: "Select",
      options: "30\n45\n60",
      default: 30
    },
  ]
};

In the Python file, minimal_script_report_with_chart.py, replace this code …

# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
# import frappe

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

… with this code …

# Copyright (c) 2013, Warehouseman and contributors
# For license information, please see license.txt

def query():
  return [
    { "xAxisField":  30, "yAxisField": 4156.78 },
    { "xAxisField":  45, "yAxisField": 3908.34 },
    { "xAxisField":  60, "yAxisField": 5632.87 },
    { "xAxisField":  75, "yAxisField": 5698.21 },
    { "xAxisField":  90, "yAxisField": 2321.54 },
    { "xAxisField": 105, "yAxisField": 4446.88 }
  ]

def get_columns():
  return [
    {
      "fieldname": "xAxisField",
      "fieldtype": "Int",
      "label": "X-Axis",
      "width": 100
    },
    {
      "fieldname": "yAxisField",
      "fieldtype": "Currency",
      "label": "Y-Axis",
      "width": 100
    },
  ]

def get_data(data, fltr):
  return [ value for value in data if value["xAxisField"] > int(fltr.xAxisField) ]

def get_chart(data, columns, fltr):
  attributes = [d.get("fieldname") for d in columns]

  dimensions = [
    [
      value.get(attr) for value in data if value["xAxisField"] > int(fltr.xAxisField)
    ] for attr in attributes
  ]

  L = 0
  V = L + 1

  chart = {
    'data': {
      'labels': dimensions[L],
      'datasets': [
        {
          'name': 'Y Value',
        'values': dimensions[V]
        }
      ]
    },
    'isNavigable': 1,
    'type': 'bar'
  }

  return chart

def execute(filters=None):
  query_result = query()

  columns = get_columns()
  data = get_data(query_result, filters)
  message = "Here is a message"
  chart = get_chart(query_result, columns, filters)

  return columns, data, message, chart

#6 Show the report

Back at the “Minimal Script Report with Chart” metadata form, click on “Show Report”.

You should see :

#7 Mess around with it

You can then:

  • Float your mouse cursor over a bar to see its exact values and where it comes from.
  • Click on a bar, and then use your keyboard arrow keys to scroll left and right.
  • Change the filter value in the box at the top-left to select a narrower range.

If you don’t get those results please do let me know!

Observations:

  1. You will see that the Python code never refers to the report by name, however, the referenced Query Report name “Minimal Script Report with Chart” in the Javascript file, must be exactly the same as the “report_name” attribute of the JSON file, minimal_script_report_with_chart.json

  2. Unlike ERPNext, Frappe Charts is very well documented here

  3. The chart parameter returned from execute() is a single JSON object (“yourServerSideJSONobject”) that must conform to the specifications of Frappe Charts. So! Without you needing to do anything more, your chart is displayed on the client side like this:

  let chart = new frappe.Chart( "#your-chart", yourServerSideJSONobject);
  chart.export();
  1. A good example of a feature from Frappe Charts is 'isNavigable': 1,. Adding that, makes it possible for your users to use their keyboard to scroll around charts that are to large to fit on the page. See Navigation
12 Likes

Brilliant! Thanks Martin

Cheers,
-Tiho

1 Like

You’re very welcome. Please let me know of any inconvenience.