Skip to main content
Solved

Formatting Numbers as Currency in Report Designer

  • 9 September 2024
  • 6 replies
  • 227 views

What is the best way to add a dollar sign in front of a dollar amount (e.g. Unit Price)? I’m looking through all of the community posts and I’m not seeing a direct answer. Here is what I have tried:

  1. Added '$'+ to the front of the field name. That made the Unit Price 5 decimals instead of 2. 
  2. Added the Unit Price as the Format. This made it 5 decimals instead of 2.

It seems like there should be a very easy way to do this, but I’m not seeing it. Thanks in advance for your help!

Best answer by dan63

@bstabiner95 - Yes, you can format the field in the properties exactly how you want.  You can use “$ ###,##0.00” or “C”.  The first is the easier approach but the second is the C# formatting code (Standard numeric format strings - .NET | Microsoft Learn

I created an example below to show how both formats look when printing the report.

 

View original
Did this topic help you find an answer to your question?

6 replies

meganfriesen37
Captain II
Forum|alt.badge.img+11

If you don’t care about a “thousands” separator, then I’d do something like this:

= ‘$’ + round([field name],2)

(you may also have to convert the section inside the round formula to be a string using the CStr formula)

If you really want to keep the thousands separator, then I’d look at having the “$” value be in it’s own text box.

 

This links shows all the default format options as well, but there isn’t one for currency: https://help.acumatica.com/(W(5))/Help?ScreenId=ShowWiki&pageid=cfce7e40-08bc-4edf-b664-cbf0404b8113


  • Author
  • Freshman II
  • 6 replies
  • September 9, 2024

I do need the thousands separator, so that won’t work.

The text box way won’t work either. If I have something that is right justified, then the dollar sign will have a large space between it and the number.

There has to be a better way!


BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 625 replies
  • September 9, 2024

@bstabiner95 The issue is happening because as soon as you change the output to something other than just the field value (i.e. a function, or expression), then the output will use DB values vs. UI values, so you get the extra decimal places because that’s how it’s seen in the DB.

In order to get around this you’d need to use Round() function to 2 in order to make sure the hundredth position was correct, and then you’d need to truncate the trailing 0s, which is a lot more tedious than just adding a single text box field with $ in it next to the dollar amount

 


  • Author
  • Freshman II
  • 6 replies
  • September 9, 2024

@BenjaminCrisman -- thank you for the response. The text box will not work either because of the text alignment. I need the dollar amount right justified, so there will be a large space between the dollar sign and the amount, which is not desired.

You are correct -- these methods are very tedious. Is there not a Format property like ‘$###,###.00 ‘ that I can enter to format the field as such? It seems very odd to me that there is no easy way to do this. I have to imagine that this is quite common among Acumatica users.


dan63
Jr Varsity I
  • Jr Varsity I
  • 5 replies
  • Answer
  • September 9, 2024

@bstabiner95 - Yes, you can format the field in the properties exactly how you want.  You can use “$ ###,##0.00” or “C”.  The first is the easier approach but the second is the C# formatting code (Standard numeric format strings - .NET | Microsoft Learn

I created an example below to show how both formats look when printing the report.

 


  • Author
  • Freshman II
  • 6 replies
  • September 9, 2024

@dan63 -- Thank you so much! That worked exactly as I wanted it to. I figured there was something like that, but I couldn’t get it to work properly. The “C” property makes it super easy!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings