I have been working recently with SQL Server 2005 Reporting Services to display dollar sums and volume sums, and I’m happy to report [I couldn’t resist] that I’ve found a neat trick for formatting non-currency negative numbers. 

Our client requested that we apply the following formatting rules to report figures:

·         Use parenthesis instead of a minus sign to display negative values

·         Display no decimals and format numbers with a comma when they exceed 999

·         Display the currency sign for financial figures

These are seemingly simple requirements to accomplish with standard .NET format strings.  First, setting the Format property of the SSRS textbox (or other control) to C0 [c-zero] will format numbers as currency with no decimal places.  This format will also automatically add parenthesis around negative numbers:

o   $999 or $1,000 for positive dollar figures

o   ($900) or ($1,000) for negative dollar figures

Unfortunately, none of the easy-bake format strings for numeric values appeared to automatically display parenthesis for negative numbers.  I tried N0 [n-zero], G0 [g-zero], D0 [d-zero] and various other combinations.  The answer was to use a custom format string instead.

In order to format numeric, non-currency values to use parenthesis when displaying negative numbers, set the Format property of the SSRS textbox to “#,0;(#,0)” (and omit the quotation marks).  Problem solved!

Finally, if you’re looking to add a bit extra, format negative numbers to display in red font instead of black by adding this expression to the Color property of the textbox: 

=IIf([Expression to Evaluate] >= 0, "Black", "Red")

Or, in my case, the specific formula was:

=IIf(SUM(Fields!AmountBilled.Value) >= 0, "Black", "Red")