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!

23 Likes

Is there any way to set the color of the chart? and formatting number that show up in the popup ?

Hi there,

I believe you can change the color via the chart object.
https://frappe.io/charts/docs/reference/configuration

I’m not sure what you have in mind by formatting number.

Is there a way to change it in your sample above?
What I mean by formatting, is when the number is 4 digit the it should display the number with thousand separator. I saw in standard ERPNext report they display 123456.99 instead of 123,456.99 for instance

Thank you for sharing, I like it!

I also would like to know, using your approach

  1. How to add filters for From Date and To Date?
  2. Use joined table
    Thanks a lot!
1 Like

The Frappe report format provides filters, which you can access as variables in the script component. For joined tables, it’s all about how you fetch your data. Here, I’ve just used arbitrary values, but you can use any frappe api call to build your data source (including the new query builder, which will facilitate table joins).

For your previous question about formating number outputs, I’m not sure. There are a few methods in the frappe charts library that seem promising, but I haven’t experimented much with it myself.

2 Likes

Thanks @peterg for custom reports tutorial.

Just wanted to know if there is any way to create a dashboard or a card on home screen to fetch a particular value from an existing report.

Like fetch the batch expiry date from batch item expiry status report in stock module.

something like
if batch.expiry_date and frappe.utils.date_diff(frappe.utils.today(), batch.expiry_date) <= 30

So that it will list all the batches that will expire in 30 days. I have no idea at all how to do it at all.

The downside of the current existing report is that it lists all the batches irrespective of the current stock availability. It is very difficult to identify which batches with in stock are really expiring.

Yes, you could definitely do something like that, though probably not by polling another report. I don’t know offhand how batch expiry dates are stored, but you’d just calculate the value the same way the other report did.

We manually enter the expiry dates when creating the batch during receipt of goods.
Instead of fetching the data from report the other option would be to fetch the values from the expiry field from the batch doc. Can you help me with the script? I am completely novice as i am not from the IT background?

If it’s in the batch doc, you can access that variable via a frappe.get_list call via python, and then use that data to build your chart object as per the example above.

If you’re new to coding, I would suggest trying this step by step and then starting another thread if you get stuck. Feel free to tag me on the post if you’d like me to take a look. Here’s the API reference to get you started:

https://frappeframework.com/docs/v13/user/en/api/database

@peterg , thanks much appreciated

I got another idea that if we can create a custom field in batch doc to get days remaining to expire which will get populated automatically upon entering expiry date in the expiry field manually which we do it every time we create a new batch. Do you think this is possible?

kind of calculate field by load event, also hooks to the get_list for doctype?

I have achieved it by doing the following.

Created a custom field in “Batch” doctype named “Expires In Days” with field type data.

Client side script as below

frappe.ui.form.on('Batch', {
    expiry_date: function(frm, cdt, cdn){
        var d = locals[cdt][cdn];
        // put any logic here
        var expires_in_days = frappe.datetime.get_day_diff(d.expiry_date, frappe.datetime.nowdate());
        // update the field
	frappe.model.set_value(cdt, cdn, 'expires_in_days', expires_in_days);
    }
});

This perfectly works. When ever i create a new batch and enters expiry date, the Expires In Days field automatically gets updated.