Formatting output - money

[wilson3838]

I'm trying to display a column of money, which is currently a numeric data type (Postgres). The following syntax examples work pefectly in DBeaver, but produce errors in Jasper Studio. Does anyone have suggestions to display money ($79,967,345.23 for example)? I've tried dropping the postgresql-42.2.20.jre7.jar file (includes PG Money class) in to various folders containing other Jasper-related jar files, but continue to receive the error.

sum(d.revenue)::money --> Error: net.sf.jasperreports.engine.JRException: Unable to get value for result set field "sum" of class org.postgresql.util.PGmoney

cast(sum(d.revenue) as MONEY) --> Error: java.lang.ClassNotFoundException: org.postgresql.util.PGmoney cannot be found by net.sf.jasperreports_6.16.0.final --- net.sf.jasperreports.engine.design.JRDesignField@694ba313

If I use just "sum(d.revenue)", the correct information is displayed, but looks like a meaningless (hard-to-read) number. 

[jgust]

On the text field, you can navigate to the pattern and click the ellipsis button.  That will open up a wizard that has several formats including currency.

You could also use the built-in TEXT() function to format the number.

[wilson3838]

Thanks jgust. QQ - What is that special character that appears twice in the example you provided?

 

[jgust]

That is a generic currency symbol.  It is in the format twice once for positive and once for negative values.

https://en.wikipedia.org/wiki/Currency_symbol