Try ERPNext Buy Support Partners Foundation

How to create an "Absolute Minimum Script Report"?

So I have been trying for several days to create my first Script Report in V13.

I have examined the Balance Sheet example, and I have tried to distill out of it a bare minimum example that actually works and can be made more complex in small increments.

Here’s the the JavaScript…

frappe.query_reports["Absolute Minimum Script Report"] = {};

… here’s the Python code…

def get_columns():
  return [
    {
      "fieldname": "someField",
      "fieldtype": "Int",
      "label": "Some Field",
      "width": 100
    },
  ]


def get_data():
  return 
    [ { "someField": 30 }, { "someField": 60 } ]


def execute():
  return get_columns(), get_data()

… and here’s the database record:

MariaDB [_100fd7dc74a5feb0]> 
select * 
  from `tabReport`
 where name = 'Absolute Minimum Script Report'
\G;
*************************** 1. row ***************************
                   name: Absolute Minimum Script Report
               creation: 2020-09-27 08:50:20.859575
               modified: 2020-09-27 09:23:12.971847
            modified_by: Administrator
                  owner: Administrator
              docstatus: 0
                 parent: NULL
            parentfield: NULL
             parenttype: NULL
                    idx: 0
            report_name: Absolute Minimum Script Report
            ref_doctype: Warehouse
       reference_report: NULL
            is_standard: No
                 module: Stock
            report_type: Script Report
            letter_head: NULL
          add_total_row: 0
               disabled: 0
disable_prepared_report: 0
        prepared_report: 0
                  query: NULL
             javascript: frappe.query_reports["Absolute Minimum Script Report"] = {};

          report_script: def get_columns():
  return [
    {
      "fieldname": "someField",
      "fieldtype": "Int",
      "label": "Some Field",
      "width": 100
    },
  ]


def get_data():
  return 
    [ { "someField": 30 }, { "someField": 60 } ]


def execute():
  return get_columns(), get_data()

According to older documentation, creating the report should create files on disk. I tried a recursive grep on my entire system and found no files containing the text “Absolute Minimum Script Report”

I am guessing that in V13 this is no longer the case and that the database record now stores everything.

My questions:

  1. Why do I get no result at all?
  2. What must I change to get the bare minimum data output: 1 column, 2 rows?
  3. Where in GitHub can I find the source code for the “Script Report” generator?
  4. Must I create the directory and files myself, or am I right that V13 handles all of it from the database? Where in an app hierarchy is the report directory supposed to appear? Sister to the doctype directory?
2 Likes

1 - the execute function should expect a filters argument

Try this def execute(filters):

2 - Your code looks ok, with exception of the step 1

3 - Here https://github.com/frappe/frappe/blob/develop/frappe/desk/reportview.py

4 - if you are in developer mode, and you set that your report is “Standard”, frappe will create a folder called reports sibling to the doctype folder into the module you selected

2 Likes

With a huge vote of gratitude to @max_morais_dmm, here’s what I have been able to put together.

Hopefully this will be helpful to others in the future.


Before

Beginning with the state of the system before trying to create a Script Report, here is the initial content of the directory hierarchy of my new app “returnables”:

├── LICENSE
├── license.txt
├── MANIFEST.in
├── __pycache__
├── README.md
├── requirements.txt
├── returnable
│   ├── config
│   ├── fixtures
│   ├── hooks.py
│   ├── __init__.py
│   ├── modules.txt
│   ├── patches.txt
│   ├── __pycache__
│   ├── returnable
│   │   ├── doctype
│   │   │   ├── __init__.py
│   │   │   ├── __pycache__
│   │   │   ├── returnable
│   │   ├── __init__.py
│   │   └── __pycache__
│   └── templates
├── returnable.egg-info
└── setup.py

Here’s the database query to get the Script Report:

MariaDB [_100fd7dc74a5feb0]> select * from `tabReport` where name = 'Minimal Script Report' \G
Empty set (0.000 sec)

I prepare a new report form like this:

After saving it and adding the minimal code shown in the first post it looks like this:

Re-examining my app directory hierarchy I find that it is unchanged from before. This would be because is_standard is not yet selected (as @max_morais_dmm pointed out).

Meanwhile, the database query now returns:

MariaDB [_100fd7dc74a5feb0]> select * from `tabReport` where name = 'Minimal Script Report' \G
*************************** 1. row ***************************
                   name: Minimal Script Report
               creation: 2020-09-28 19:32:39.672696
               modified: 2020-09-28 19:37:32.407572
            modified_by: Administrator
                  owner: Administrator
              docstatus: 0
                 parent: NULL
            parentfield: NULL
             parenttype: NULL
                    idx: 0
            report_name: Minimal Script Report
            ref_doctype: Returnable
       reference_report: NULL
            is_standard: No
                 module: Returnable
            report_type: Script Report
            letter_head: NULL
          add_total_row: 1
               disabled: 0
disable_prepared_report: 0
        prepared_report: 0
                  query: NULL
             javascript: frappe.query_reports["Minimal Script Report"] = {
  "filters": [
    {
      fieldname: "someField",
      label: "Some Field",
      fieldtype: "Select",
      options: "30\n45\n60",
      default: 30
    },
  ]
};

          report_script: def get_columns():
  return [
    {
      "fieldname": "someField",
      "fieldtype": "Int",
      "label": "Some Field",
      "width": 100
    },
  ]

def get_data(fltr):
  values = [
    { "someField":  30 },
    { "someField":  45 },
    { "someField":  60 },
    { "someField":  75 },
    { "someField":  90 },
    { "someField": 105 }
  ]

  return [ v for v in values if v["someField"] > int(fltr.someField) ]

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

                   json: NULL
             _user_tags: NULL
              _comments: NULL
                _assign: NULL
              _liked_by: NULL
1 row in set (0.000 sec)

Clearly, this is not meaningfully different from the result I showed in my original post.

Also, sadly, adding filters as a parameter to the execute function (def execute(filters=None):) made only a little difference; running the report produces:


The next thing I tried was to set is_standard to true, and save again. I discovered that:

  • The advantage of doing so is to 1) have the required files generated on disk 2) have a report that actually displays data!
  • The disadvantage of doing it is that the report metadata can no longer be altered. Only the newly created files can be altered.

With that done the app directory now contains:

     :     :     :     :     :
│   ├── patches.txt
│   ├── __pycache__
│   ├── returnable
│   │   ├── doctype
│   │   │   ├── __init__.py
│   │   │   ├── __pycache__
│   │   │   ├── returnable
│   │   ├── __init__.py
│   │   └── __pycache__
│   │   └── report
│   │       ├── __init__.py
│   │       └── minimal_script_report
│   │           ├── __init__.py
│   │           ├── minimal_script_report.js
│   │           ├── minimal_script_report.json
│   │           └── minimal_script_report.py
│   └── templates
     :     :     :     :     :

The previous database query result is unchanged except for …

     :     :     :     :     :
            is_standard: Yes
     :     :     :     :     :

… which is reflected in the contents of the json file :

erpdev@erpserver:~/frappe-bench-DYPW/apps$ jq -r '.' returnable/returnable/returnable/report/minimal_script_report/minimal_script_report.json 
{
  "add_total_row": 1,
  "creation": "2020-09-28 10:01:52.986921",
  "disable_prepared_report": 0,
  "disabled": 0,
  "docstatus": 0,
  "doctype": "Report",
  "idx": 0,
  "is_standard": "Yes",
  "javascript": "frappe.query_reports[\"Minimal Script Report\"] = {\n  \"filters\": [\n    {\n      fieldname: \"someField\",\n      label: \"Some Field\",\n      fieldtype: \"Select\",\n      options: [30, 60, 90],\n      default: 30\n    },\n  ]\n};\n",
  "modified": "2020-09-28 15:20:52.097912",
  "modified_by": "Administrator",
  "module": "Returnable",
  "name": "Minimal Script Report",
  "owner": "Administrator",
  "prepared_report": 0,
  "ref_doctype": "Returnable",
  "report_name": "Minimal Script Report",
  "report_script": "def get_columns():\n  return [\n    {\n      \"fieldname\": \"someField\",\n      \"fieldtype\": \"Int\",\n      \"label\": \"Some Field\",\n      \"width\": 100\n    },\n  ]\n\n\ndef get_data():\n  return [ { \"someField\": 30 }, { \"someField\": 60 } ]\n\n\ndef execute(filters=None):\n  return get_columns(), get_data()\n",
  "report_type": "Script Report",
  "roles": [
    {
      "role": "System Manager"
    },
    {
      "role": "Stock User"
    },
    {
      "role": "Stock Manager"
    },
    {
      "role": "Purchase User"
    },
    {
      "role": "Sales User"
    },
    {
      "role": "Sales Manager"
    },
    {
      "role": "Sales Master Manager"
    },
    {
      "role": "Purchase Manager"
    },
    {
      "role": "Purchase Master Manager"
    }
  ]
}
erpdev@erpserver:~/frappe-bench-DYPW/apps$

A critical fact is the lack of code in the JavaScript and the Python file!

You only get …

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

	]
};

… and …

from __future__ import unicode_literals
# import frappe

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

Getting it working

I replaced the above placeholder code snippets with the intended code …

minimal_script_report.js

frappe.query_reports["Minimal Script Report"] = {
  "filters": [
    {
      fieldname: "someField",
      label: "Some Field",
      fieldtype: "Select",
      options: "30\n45\n60",
      default: 30
    },
  ]
};

minimal_script_report.py

def get_columns():
  return [
    {
      "fieldname": "someField",
      "fieldtype": "Int",
      "label": "Some Field",
      "width": 100
    },
  ]

def get_data(fltr):
  values = [
    { "someField":  30 },
    { "someField":  45 },
    { "someField":  60 },
    { "someField":  75 },
    { "someField":  90 },
    { "someField": 105 }
  ]
  return [ v for v in values if v["someField"] > int(fltr.someField) ]

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

… which results in:

Curious fact

I deleted the javascript and reportscript field values both 1) in the database record and 2) in the file returnable/returnable/returnable/report/minimal_script_report/minimal_script_report.json.

They seem to serve no purpose at all. The file contents are everything.

2 Likes

You may also like:

I’ve always thought the “Is Standard” field’s name was awful. It feels like a trap, as it definitely does not indicate whether a report is “standard” or not.

Would be great if this field was an Enum type instead, with some more-accurate labels:

  • Defined in MySQL Database (0)
  • Defined in Files (1)

Btw, excellent sleuth-work and documentation, Martin. :100:
It’s very unfortunate that Report development isn’t well-documented outside of our personal wikis and blogs.

1 Like

Brian

Thanks for the supportive remarks.

While, I gripe rather loudly about the inadequacy of the documentation, I hope my efforts to fill in the blanks are seen as a net positive.

ERPNext is very good in many ways, so the defects are all the more shocking … like walking around a well designed and attractive building, then falling to the basement where the stairs have yet to be put in.

1 Like

You have obviously never visited the Winchester House. :grin:

Wow! What a thing!

You did inspire me to finally make my own report + graph
See here:

1 Like