Color of Values in Non Standard Script Report

ERPNext User Forum
1

Hello,

May I know how to change color of values in non-standard Script Report (Is Standard = No).

Ref DocType: Attendance
Is Standard: No
Report Type: Script Report

Query / Script (Script)

columns = [
    {'fieldname':'name','label':'ID','fieldtype':'Link', 'options': 'Attendance', "width": 200},
    {'fieldname':'employee_name','label':'Employee Name','fieldtype':'Data', "width": 300 },
    {'fieldname':'attendance_date','label':'Attendance Date','fieldtype':'Date', "width": 200},
    {'fieldname':'status','label':'Status','fieldtype':'Data', "width": 200}
]
data = columns, frappe.db.get_all('Attendance', ['name','employee_name','attendance_date','status'])

Client Code (Javascript)

frappe.query_reports["Test script Report"] = {
	"filters": [
		 {
		 	fieldname: "date_filter",
		 	label: "Date Filter",
		 	fieldtype: "Date",
		 	Note the following default attribute, which contains an API call
		 	default: frappe.datetime.get_today()
		}
	],
	formatter:function (value, row, column, data, default_formatter) {
		value = default_formatter(value, row, column, data);
		if(column.fieldname == 'status'){
			if(value == 'Present'){
				value = `<b style="color:green">${value}</b>`
			}else if(value == 'Absent'){
				value = `<b style="color:red">${value}</b>`
			}
		}
	return value;
	}
};

In Report Absent should be in Red.

image
image1288×558 46 KB

Thanks.

1 Like
2

Hi @umarless,

Please apply it.

frappe.query_reports["Test script Report"] = {
	"filters": [
		{
			fieldname: "date_filter",
			label: "Date Filter",
			fieldtype: "Date",
			default: frappe.datetime.get_today()
		}
	],
	formatter: function(value, row, column, data, default_formatter) {
		value = default_formatter(value, row, column, data);
		if (column.fieldname == 'status') {
			if (value == 'Present') {
				value = `<b style="color:green">${value}</b>`;
			} else if (value == 'Absent') {
				value = `<b style="color:red">${value}</b>`;
			}
		}
		return value;
	}
};

Output:

image
image2160×1004 56.6 KB

Issue in Comment Syntax, Semicolons and Code Formatting.

Thank You!

3 Likes
3

Thanks a lot @NCP
It’s working.

image
image1323×371 23.3 KB

But Color Red is not showing in Print Preview.

image
image1427×371 11.5 KB

Update :
When I used
value = `<span style='color:red!important'>${value}</span>`;
Insted of
value = `<b style="color:red">${value}</b>`;

OR just using !important property is the solution for Print Preview.

Thanks again.

4

:+1: @umarless

5

@NCP
I tried to apply the same logic to a column that was added by the query itself and it does not represent an actual field. How do I address that in the client script for my query report?

Query:
SELECT
so.cost_center AS Cost Center,
CONCAT(YEAR(so.issue_date), ‘-’, LPAD(MONTH(so.issue_date), 2, ‘0’)) AS Posting Date,
SUM(so.net_total * (1 - (so.per_billed / 100))) AS Amount To Be Billed,
IFNULL(si.net_amount, 0) AS Actual Billed Amount,
(SUM(so.net_total * (1 - (so.per_billed / 100))) + IFNULL(si.net_amount, 0)) AS Total Expected Revenue
FROM
tabSales Order AS so
LEFT JOIN (
SELECT
si_posting.cost_center,
CONCAT(YEAR(si_posting.posting_date), ‘-’, LPAD(MONTH(si_posting.posting_date), 2, ‘0’)) AS Invoice Posting Date,
SUM(si_posting.net_total) AS net_amount
FROM
tabSales Invoice AS si_posting
WHERE
YEAR(si_posting.posting_date) = 2024
AND si_posting.docstatus = 1
AND si_posting.cost_center = ‘TOP1 - DG’
GROUP BY
si_posting.cost_center, Invoice Posting Date
) AS si ON so.cost_center = si.cost_center AND CONCAT(YEAR(so.issue_date), ‘-’, LPAD(MONTH(so.issue_date), 2, ‘0’)) = si.Invoice Posting Date
WHERE
so.docstatus = 1
AND YEAR(so.issue_date) = 2024
AND so.per_billed < 100
AND so.cost_center = ‘TOP1 - DG’
GROUP BY
so.cost_center, CONCAT(YEAR(so.issue_date), ‘-’, LPAD(MONTH(so.issue_date), 2, ‘0’))
ORDER BY
so.cost_center ASC, CONCAT(YEAR(so.issue_date), ‘-’, LPAD(MONTH(so.issue_date), 2, ‘0’)) ASC;

Script:
frappe.query_reports[“Expected Revenue based on Issue Date”] = {
formatter: function(value, row, column, data, default_formatter) {
value = default_formatter(value, row, column, data);
// Adjust the column name as it appears in the report, ensuring exact match
// It might include spaces or capitalization as per the SQL AS clause
if (column.fieldname === “Total Expected Revenue” || column.label === “Total Expected Revenue”) {
var numericValue = parseFloat(value.replace(/,/g, ‘’));
if (numericValue < 250000) {
value = <span style="color:red">${value}</span>;
} else if (numericValue > 250000) {
value = <span style="color:green">${value}</span>;
}
}
return value;
}
};

Thanks in advance!