Show Negative Numbers in Parentheses in ConnectWise Custom Invoice or Report Writer
A lot of clients prefer to have their negative numbers in parentheses instead of with a minus sign. I’ve used various methods to get around the lack of support for this display format in Report Writer by today I realized there’s a simpler way that can be applied universally.
Rather than adding complex SQL logic to the field to marry text and numeric info, you can simply add a couple of text fields to your report with the CASE statement below to invoke them when applicable.
The Setup
Add a couple of text-based fields in your dataset and name them “(” and “)”. It doesn’t matter which fields but just pick a text-based one instead of a number. For my Report Writer custom invoice template, I just used the Reference field twice like this:
In the Advanced Field Properties on those, add this simple logic to the expression box. Edit it it to reference the condition in which you want to display the parentheses. In this case, I want to add them when the total of Paid Amount + Downpayment on the invoice is more than $0 (that way, I could show that amount being deducted from my subtotal).
Now, go to your report HTML and add in those parenthesis as you would any data field. If the condition is met (e.g. there is a negative amount), they will display. I added mine to my [Paid Amount] field, which is actually set to the sum of [Paid_Amount]+[Downpayment].
The Result
My final output shows negative numbers in parentheses for the paid amount. I also created a set of parentheses for the Credits field, which does not show parentheses as the amount is not greater than $0.
Want to do more?
Now, before you go and update all your reports to show negative numbers in parentheses, do you have any other Report Writer formatting issues you’ve been struggling with? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.