String field to number field decimal issue

[stormtrooper]

Hello there. I am running CR9 and DB2. The issue I have is that I have a report that consists of a field called {RSRVD_PRMT.NUMBER} that comes in from DB2 as a string. I have to group on this field but I also have to order by ascending.

To do this I created a formula called @ToNumNumber that looks like this, ToNumber({RSRVD_PRMT.NUMBER}), and grouped on the formula.

Now, I get exactly what I want except when the report is scheduled and viewed over the web. This yields the following format in the left nav bar.

5.00
23.00
45.00
66.00
etc.

When viewed on my desktop the format of the numbers are not currency, which is what I want.

Any ideas or am I just having a brain-fart as to how to format the field?

Thanks.

 

[synapsevampire]

Are you trying to say that you don't want decimals? Decimals have nothing to do with being currency.

Try right clicking the formula in the report and selecting format field and select 0 for the decimals.

If that fails, try cheating by using:

val(totext(ToNumber({RSRVD_PRMT.NUMBER}),0,""))

Also there's a global setting for number formatting under the File->Report Options

-k

 

[stormtrooper]

vampire:

Yes, I don't want the decimals. I've tried the formatting of the field as well as the report options and that didn't do it. I'll have to give your "cheat" a try but for some reason that looks like a really familiar formula. I think I may have even tried that. I'll keep you posted.

Thanks.

 

[stormtrooper]

The "cheat" didn't work either. Any other ideas?

 

[stormtrooper]

I got it. I had to use an SQL Expression and group on that.

Int("RSRVD_PRMT"."NUMBER")

 

[stormtrooper]

Update:

The first issue is fixed. Now I've be notified that the left nav numbers are in 123,345,678 format. I've checked the live (web) report and sure enough that is the format.

Developing the report on my local desktop the report looks normal. That is, the numbers are in the correct format (without commas and decimals, etc.). The commas appear when the report gets published to the web using CE9. I've checked the settings on CE9 and they are set to what we want.

Any suggestions, please help. Thanks.