This is a cool tip that I picked up from one of my nonprofit FRx clients.
They wanted to see a hyphen instead of a zero, sort of like the Accounting format in Excel. Here’s how. Go to the Company menu, International Formats, and pull up your currency, in this case USD for US Dollars. This is what it probably looks like:
Look at the amount format:
#,###,##0.00;(#,###,##0.00)
and amount with currency symbol:
$#,###,##0.00;($#,###,##0.00)
The above is a classic two-part format. To get hyphens for zeros, we’ll need a three-part format, shown as follows:
#,###,##0.00;(#,###,##0.00);-
and amount with currency symbol:
$#,###,##0.00;($#,###,##0.00);-
In other words, simply add a semicolon and a hyphen at the end of both formats.
But there’s one last trick in order to get this to work. Go to the catalog and in the Report Options tab, Formatting tab, deselect ‘Display blanks for zero amounts’.
You will need to repeat the catalog edit for each catalog you want formatted this way; otherwise you’ll still get zeros instead of hyphens.
Enjoy, and thanks to Kathy at Durham Western Heritage Museum in Omaha, Nebraska!
{ 7 comments… read them below or add one }
I just discovered your website today while trying to find a solution for an FRX Report issue. In looking for that solution I have discovered numerous other helpful hints that I never learned in class. I am a relatively new user of FRX and with only a few days of training under my belt and I am looking for all the help I can get, so your site appears to be a God send.
I have a report that requires amounts in the same column to be rounded in different methods, ( i.e. dollars and cents and whole thousands) I have yet to find a way to accomplish this as each row must be rounded one way or the other. Any helpful hints would be greatly appreciated.
Thanks,
Rick
Hi Rick…thanks for the kind words! Regarding rounding, you can add a format to a single row and it will override the way the column is rounded. It’s not easy to find in help, but you can find how to do this by searching the index for “formatting, amounts, row-by-row”. Good luck.
For some reason, this method displays a hyphen only in the first row of the spreadsheet. All the others display a zero.
If the FRx report looks right but Excel export looks wrong then it could be a bug in the export. I’d use my favorite Excel tool the Format Painter to fix after export.
I put a CS on every row of the report and it is now working. Although, I do not know why it wouldn’t before I did that.
There are 2 formats in the International Formats screen (see the 2 arrows in the screenshot above)…one for currency and one for just amounts. I’m guessing that maybe just the one for currency was changed.
Is there an easy way to suppress the “division by zero” error that appears in an excel spreadsheet when you export an FRx report to excel? One way of course, is to add the “IFERROR” manually to the spreadsheet formulas. But the users don’t want to do this. I have to turn “export formulas” on.