Formatting output - money

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. 

wilson3838's picture
Joined: Mar 16 2021 - 8:03am
Last seen: 11 months 3 weeks ago

3 Answers:

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.

jgust's picture
1311
Joined: Jun 10 2010 - 6:39am
Last seen: 15 hours 58 min ago

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

 

Attachments: 
AttachmentSize
Image icon special_char.png36.65 KB
wilson3838's picture
Joined: Mar 16 2021 - 8:03am
Last seen: 11 months 3 weeks ago

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

 

jgust's picture
1311
Joined: Jun 10 2010 - 6:39am
Last seen: 15 hours 58 min ago
Feedback
randomness