Number format negative with parenthesis in reports

ERPNext Accounting
1

Any way to enclose negative numbers with parentheses instead of the negative sign in reports? Thanks.

2

Hi @mincerray,

If you want to Output like (-)100.000 then Please check the below example.

SELECT 
  `tabSales Invoice`.`name` as `Invoice ID:Link/Sales Invoice:200`,
  CONCAT(
    CASE 
      WHEN `tabSales Invoice`.`grand_total` < 0 THEN '(-)'
      ELSE ''
    END,
    ABS(`tabSales Invoice`.`grand_total`)
  ) as `Grand Total:Curruncy:200`
FROM `tabSales Invoice`

Output:

image
image1919×454 27.6 KB

If you want to Output like (-100.000) then Please check the below example.

SELECT 
  `tabSales Invoice`.`name` as `Invoice ID:Link/Sales Invoice:200`,
  CASE 
    WHEN `tabSales Invoice`.`grand_total` < 0 THEN CONCAT('(', `tabSales Invoice`.`grand_total`, ')')
    ELSE `tabSales Invoice`.`grand_total`
  END as `Grand Total:Curruncy:200`
FROM `tabSales Invoice`

Output:

image
image1919×445 27.8 KB

I hope this helps.

Thank You!

3

Thanks for the suggestion… I want it to output as in the “Accounting” format in Excel…

For example if -100, then (100.00), if -1000 then (1,000.00). Your suggestion might work for custom reports but if all reports need be like that, is there a common frappe method to override?

4

Hmm :thinking: @mincerray,

I haven’t any idea about Excel formatting.
I don’t know but yes, maybe you can override the method for all reports.

Thank You!