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 Typeto “User” - Set
Is Standardto “No” - Set
Report Typeto “Script Report”
Then, if you want to demo filters, define a filter in the Filters child table:
- Set
Fieldnameto “enabled” - Set
Labelto “Enabled users only” - Set
Fieldtypeto “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!