[Tutorial] Add Color to Cells in Query Report

Frappe Framework
1

We can add color to cells in report based on cell value.

Here we have made cells in row green if Rental Payment greater than 100 else red.

add following code to query_report_name.js file

frappe.query_reports["Payments Received"] = {
    "filters": [
        {
            "fieldname":"customer",
            "label": __("Customer"),
            "fieldtype": "Link",
            "options": "Customer",
            "width": "80"
        }
    ],
    "formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {
        value = default_formatter(row, cell, value, columnDef, dataContext);
       if (columnDef.id != "Customer" && columnDef.id != "Payment Date" && dataContext["Rental Payment"] < 100) {
            value = "<span style='color:red!important;font-weight:bold'>" + value + "</span>";
       }
       if (columnDef.id != "Customer" && columnDef.id != "Payment Date" && dataContext["Rental Payment"] > 100) {
            value = "<span style='color:green!important;font-weight:bold'>" + value + "</span>";
       }
       return value;
    }
}

color_report.png1180×379 28.7 KB

Thanks @Jitendra_Khatri1 for making such report using my blog post which was badly formated.

Regards,
Sambhaji Kolate,
New Indictrans Technologies Pvt Ltd.

26 Likes
2

for filling entire cell background color use following code

value = <p style='margin:0px;padding-left:5px;background-color:red!important;'>${value}</p>

image

5 Likes
3

where to create the js file for custom query report that i`ve created ??

4

@Mahmoud_Ghoneem you need to create script report to edit js files.

1 Like
5

Hi I just try to implement “formatter” in general Ledger report., and getting this error in console

Uncaught TypeError: Cannot set property 'html_format' of undefined
at report.min.js?ver=1545352574.0:1111

My frappe and erpnext is v10 - master and below is my code

"formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {
    value = default_formatter(row, cell, value, columnDef, dataContext);
   if (columnDef.id == "Debit" && dataContext["Debit"] > 0) {
        value = "<span style='color:red!important;font-weight:bold'>" + ${value} + "</span>";
   }
    if (columnDef.id == "Debit" && dataContext["Debit"] > 10000) {
         value = "<span style='color:green!important;font-weight:bold'>" + value + "</span>";
    }
   return value;

Any support in this ??

6

After doing some experimental, I have fixed it.

7

Perhaps a syntax error? Please share the code fix that would be informative thanks!

8

Corrected Code:

        "formatter":function (row, cell, value, columnDef, dataContext, default_formatter) {
            value = default_formatter(row, cell, value, columnDef, dataContext);

        if (columnDef.id == "Debit" && dataContext.debit > 0 && dataContext.debit < 100000) {
            value = "<span style='color:#006400!important;font-weight:bold';>" + value + "</span>";
        }
        if (columnDef.id == "Debit" && dataContext.debit >= 100000 && dataContext.debit < 300000) {
            value = "<span style='color:#ffff00!important;font-weight:bold'>" + value + "</span>";
        }
        if (columnDef.id == "Debit" && dataContext.debit >= 300000) {
            value = "<span style='color:#ff0033!important;font-weight:bold'>" + value + "</span>";
        }
        return value;
    }

Notable points :

  1. I have used dataContext[“debit”] which has produced no result (i.e., no changes in color)

  2. I have used ${value} this is the root cause for the syntax error.

  3. If we want to change the background colour for the cell then, the tag < span > should be replaced with < div > in the above statement

1 Like
9

Excellent many thanks for these details!

10

Hi,
I have put the same code, but i am getting this error.

2
21365×688 66.5 KB

1 Like
11

Continued here Error in colouring Script reports