Formatting numeric fields

[drcraig232]

I've just downloaded the free trial yesterday and have worked my way through tables and queries. Now trying to generate reports. How do I go about formatting the numeric fields in my reports?

[Ken]

Hi there,

Thanks for giving Strata a whirl. Unfortunately here is not an "easy" way to get numeric formatting in, like you would in, say, Excel. This is on our feature list.

However, you can do formatting via the function "TO_CHAR". For commas only, you'd use the following format in the design view of the report:

=to_char(FIELD_NAME, "FM999,999,990.99")

...and then you'd right-justify the column accordingly using the formatting icons. Please see more details below (which explains formatting for a currency type):

You can use the flexible TO_CHAR function to get the formatting you want.

After you've created your report template, click on the design button on the upper right (the protractor symbol).

Edit the numeric field which you want formatted to a currency and type in the following formula:

="$"+to_char(FIELD_NAME, "FM999,999,990.99")

Make sure to replace FIELD_NAME in the above expression with the field name of the column you want to format. You can read more about TO_CHAR online, as this is a pretty standard function across databases.

Here's a short explanation of what the above does: The second parameter of the TO_CHAR function is a format parameter, which describes how the formatting should look. The FM means "don't pad the left with spaces." This is useful because you can use the report writer to right-justify the field. The zero you see in the format expression instructs the function to ensure that a zero is placed there, so that you get "0.85" instead of ".85"

Though clearly not ideal, I hope it helps in a pinch. If you have any other questions, please just let us know.

Best,
ken