Formatting a field using ToText in a Crystal Reports formula field

Question

I'm trying to create a Crystal Reports formula field (to calculate the percentage change in a price) that will return "N/A" if a particular report field is null, but return a number to two decimal places using accounting format (negative numbers surrounded by parentheses) if it is not.

The closest I have been able to manage is this:

If IsNull({ValuationReport.YestPrice}) Then
    'N/A'
Else
    ToText({@Price}/{ValuationReport.YestPrice}*100-100, '###.00', 2)

However this represents negative numbers using a negative sign, not parentheses.

I tried format strings like '###.00;(###.00)' and '(###.00)' but these were rejected as invalid. How can I achieve my goal?

Answer 1

I think you are looking for ToText(CCur(@Price}/{ValuationReport.YestPrice}*100-100))

You can use CCur to convert numbers or string to Curency formats. CCur(number) or CCur(string)

---

I think this may be what you are looking for..

Replace (ToText(CCur({field})),"$" , "") that will give the parentheses for negative numbers

It is a little hacky, but I'm not sure CR is very kind in the ways of formatting

Answer 2

if(isnull({uspRptMonthlyGasRevenueByGas;1.YearTotal})) = true then "nd" else totext({uspRptMonthlyGasRevenueByGas;1.YearTotal},'###.00')

Above logic will be work, what you are looking for..

Answer 3

It is working but in the case of decimal like 9999.10 it is showing 9999.00 .