ERPNext.com Frappe Cloud Support Partners Foundation Frappe School

[DevProTip] Custom Reports, purely client side

Hi all,

I’ve been playing around with the new client-side scriptable reports in v13, and they are immensely powerful. Documentation and examples are a bit limited, however, so I thought I’d share a not-quite-minimum working example to help others discover and use this feature. The report I’ll describe here is as basic as they get, but it should be a solid start for anyone looking to design something more powerful.

First, here’s what our simple example will look like when completed:

To get there, we have to follow a few steps:

First, create a new Report:

  • Set Ref Doc Type to “User”
  • Set Is Standard to “No”
  • Set Report Type to “Script Report”

Then, if you want to demo filters, define a filter in the Filters child table:

  • Set Fieldname to “enabled”
  • Set Label to “Enabled users only”
  • Set Fieldtype to “Check”

Finally, add the following code to the Query / Script box:

## First, fetch your base data results using normal api calls
## We can also access `filters`, defined by either the table above or the client script below
results = frappe.db.get_all('User', ['*'], filters=filters)

# Then, for fun, let's define a new property programmatically
for result in results:
    result.backwards_name = result.first_name [::-1]

## Next, we can add a custom message. This will appear near the top
message = "This report has been generated automatically."

## After that, we can generate a report summary to display above the chart and the data
## (For this, we'll split our list up a bit using comprehensions. You can generate this summary data any way you want.)
male_users = [user for user in results if user.gender == "Male"]
female_users = [user for user in results if user.gender == "Female"]

report_summary = [
	{
		"value": frappe.format_date(frappe.utils.nowdate()),
		"label": "Report Date",
		"datatype": "Data",
	},
	{
		"value": len(results),
		"label": "Total users",
		"datatype": "Data",
	},
	{
		"value": (100 * len(female_users) / len(results)),
		"label": "Percent female",
		"indicator": "Red" if (100 * len(female_users) / len(results)) > 50 else "Blue",
		"datatype": "Percent",
	}
]

## Now, we can generate a chart using standard Frappe Charts syntax
## To keep things short, I'm just manually entering data here, but of course usually this would be generated programmatically
chart = {
	'data': {
		'labels': ["One", "Two", "Three"],
		'datasets': [
            {
                'name': "Female", 'type': "bar",
                'values': [3, 5, 7]
            },
            {
                'name': "Male", 'type': "bar",
                'values': [4, 2, 1]
            }
        ]
	},
	'type': "bar"
}

## Finally, define your columns. Many of the usual field definition properties are available here for use.
## If you wanted to, you could also specify these columns in the child table above.
columns = [
    {
        'fieldname': 'name',
        'label': _('Document Link'),
        'fieldtype': 'Link',
        'options': 'User',
        'width': 300
    },
    {
        'fieldname': 'first_name',
        'label': _('First Name'),
        'fieldtype': 'Data',
        'align': 'left',
        'width': 200
    },
    {
        'fieldname': 'last_name',
        'label': _('Last Name'),
        'fieldtype': 'Data',
        'width': 200,
        'align': 'left'
    },
    {
        # here's our `backwards_name` field, which we defined earlier
        'fieldname': 'backwards_name',
        'label': _('Backwards Name'),
        'fieldtype': 'Data',
        'align': 'right',
        'width': 200
    },
]

## finally, we assemble it all together
data = columns, results, message, chart, report_summary

Now, when you run the report, you should get a customized presentation of your users, along with some summary statistics and a chart. This is a trivial example, but the possibilities are endless. The fact that it can all be done on the client-side, too, is very cool. In a wonderful way, Frappe continues to become more and more powerful in the hands of power users and service providers!

15 Likes