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 frappe/frappe/desk/reportview.py at develop · frappe/frappe · GitHub

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.

5 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.

2 Likes

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.

2 Likes

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

Hi @MartinHBramwell

Trust you’re doing great. I’ve tried your code but it doesn’t work. It keeps returning the following error:

File "/home/frappe/benches/bench-version-12-2021-06-22/apps/frappe/frappe/desk/query_report.py", line 68, in generate_report_result
    columns = [get_column_as_dict(col) for col in columns]
TypeError: 'NoneType' object is not iterable 

I’ve also tried some other very simple scripts as reported here but the result is same. Unfortunately, there is not even a basic sample in the official documentation to help us know if we’re somehow missing something

Any help will be greatly appreciated

Thanks

Hi @MartinHBramwell and everyone else that contributed to this post.

a Huge thanks from my side as well. I have also started with my own , very first, script, this
weekend and I have also bumped my head against the same “walls”.

I found a video that I thought was the answer but I only got so far and got stuck. My code
only worked half-way. My selection windows came up at the top of my page, but that
was it.

I loaded your code and wola.

My goal is tp write a simple only-works-for-me bit of code to generate a custom P&L report.
I shall now use what I have and is working so far, to build intowhat I want. I have already
worked out the SQL query by hand ( under bench maraidb ) so I must just build that into
my .PY file.

So, thank you for carefully documenting you “journey”. It was very helpful and certainly
and example to us all.

Hello @johnlongland

A number of people have had the same frustrating experience, including myself. I decided to document all my learnings here : [Tutorial] Script Report / Chart

Hi @EugeneP

Absolutely brilliant work !!

I shall work through it slowly. I have just managed to get some data on the screen with
my own very first script. VERY basic I have to admit, but hey … .lets first crawl before
we walk and run !

As always, even though I have a very basic little script going, it has brought up many
questions and I shall work through your doc and I am sure I shall find answers there.

In the meantime, I have realised I have a flaw in my SQL statements , which I tested
separately with BENCH MARIADB. So…its back to SQl to see what I can do there to improve.

It is this type of sharing that puts Open-source head-and-shoulders above the rest.
The result is that more people will feel comfortable with a rather difficult ( if I may say so)
concept in ERPNext. This in turn will make people more willing to contribute
reports/graphs which can be incorparated into the core of ERPNext.

well done !!!

1 Like

Even we struggled quite long to bring the basic output. Finally got the basic output without making the script report to Standard.

Created a Script report and kept Is Standard to “NO”
Configured columns like below


In the query/Script section returned the simple result

result = [{“quotation”: “Here is a message”, “ebitda”: “abc”}, {“quotation”: “Here is the quote”, “ebitda”: “cba”}]

for now client code section is like below. Ideally we should be able to configure filters also like columns

frappe.query_reports[‘Script Report Test’] = {
“filters”:
}

Nutshell we don’t need execute function. The query script section is the execute function by itself.

Hope this helps someone

1 Like