Tuesday, April 27, 2021

Dynamics 365 Docentric report: formatting negative numbers with parentheses

 I was hoping there was an easier way to do this, but this is how I ended up doing it.
For each numeric field that you want to show parentheses around a negative number, do the following. 
1. Change the field to return the absolute value of your field:
    For example:  abs(@GrossTotal)

2. Make sure you go outside of the "field" definition and add a beginning parenthesis before it and an ending parenthesis after it.

3. Highlight the first parenthesis and go up to the top and choose the Docentric "IF" to put around it. Only show the parenthesis if the value of @GrossTotal is less than zero. @GrossTotal < 0.

4. Highlight the last parenthesis and go up to the top and choose the Docentric "IF" to put around it. Only show the parenthesis if the value of @GrossTotal is less than zero. @GrossTotal < 0.

When you "show tags" on the template, it should look like this:


If anyone finds an easier way to do this, let me know.
I tried using an iif statement in the field value but it didn't give me the decimal places I needed:
iif(@GrossTotal>0,@GrossTotal,concat(concat('(',@GrossTotal*-1.00),')') )
It returned (25) instead of (25.00)


No comments:

Post a Comment