Unwanted comma shows in string CE 10

[bdevitt]

Hi,

I'm using CE 10 (svc pack 5). I create reports in CR 10 (svc pack 5) that draws data from our MySql (v.4.0.18) database via ODBC.

I've created a report that shows charts of grant inquiries by year and month. Report header shows chart of all years. Group Header 1 shows chart of each year by month/source.

I've created 2 formula fields YEAR and MONTH to group on: YEAR (formula is: Cstr({Command.Year},0) where Command.Year is the YEAR(myqsl.date))

MONTH is the same formula structure as YEAR.

Problem: When viewing the report in Crystal Reports, the axis labels, legend labels show the value for @Year (string) properly as 2006 or 2005, etc. However, when the same report is published in CE 10 and an instance is run, the axis labels, legend labels, and anywhere else I've inserted the @Year (string) value shows with a comma like 2,006 or 2,005 etc.

I've tried turning off 'thousands separator' in File->Options->Fields->Number several different ways and nothing seems to stop this behavior. I just applied the latest Viewer hotfix (jan 2006) and believe all other hotfixes are uptodate.

Any help would be greatly appreciated!

 

[synapsevampire]

Odd that you'd convert it to a string, but try:

Cstr({Command.Year},0,"")

The second parameter of the CSTR function states the thousands seperator.

-k

 

[bdevitt]

Thanks very much synapsevampire for your quick response. Funny you should mention adding that last "" parameter in the Cstr function. As soon as I saw your suggestion, I remembered yesterday that had occurred to me, but I neglected to try that one of the 20 other variations I DID try.

As it turns out, Cstr({Command.Year},0,"") solves the problem perfectly. The "" kills the unwanted comma in my Year (string). I remember now I originally converted the value to a string because of this format issue. The report WAS working fine until I was forced to reinstall CE 10 due to corruption of some files after applying a hotfix. After that, the report went back to showing years as 2,006.00

Thanks again!

 

[synapsevampire]

If you left it as a numeric and explicitly formamtted the number using the format editor, CE should preserve the settings.

-k

 

[bdevitt]

Hi,

'Should' seems to be the important word in that sentence. If I place the field Command.Year (number) elsewhere in the report, and explicitly format for no comma or decimals, CE does preserve the settings.

The problem comes at the group level. In the report header, a chart shows inquiries by year (by grouping on Command.Year (number)). While the axis labels and legend labels show correctly in CR 10, once published they still show as 2,006.00 and 2,005.00 etc. I have File Options Fields Number set to no decimal, no thousands separator. I have Chart Options->General->Numbers set as 'number' with 0 decimal places.

There doesn't seem to be a place to explicitly format a field one is using to group by. I don't see a way in formula editor to explicitly format a number to prevent comma/decimals. Thus far, the Cstr(value,0,"") is the only way that I've been successful.

-bd

 

[synapsevampire]

The downside with that approach is that 10 will sort before 2, otherwise it's fine.

Not sure why your CE server does that, can't recall the same problem, I'll have to test it next week when I go to the CE 10 client.

-k

 

[synapsevampire]

Found a whitepaper that might resolve this:

http://technicalsupport.businessobj...sliceId=&dialogID=3686973&stateId=1 0 3688784

-k

 

[bdevitt]

Thx for the whitepaper link. Although it appears that this should solve the issue, it doesn't. I'm running Windows XP SP2. Following the white paper instructions one can see that windows Regional settings does affect the report in CR 10. However, after publishing, I see the same 2,006.00 behavior as before.

I'll continue to mess around with it as I have time, but, for now, I'm satisfied with the Cstr solution (I also use Group->Group Expert->Options->Sort in specified order to work around the string sorting issue).

Thx again for your assistance.

-bd

 

[synapsevampire]

So you changed the regional settings, or you simply saw that they affect it?

Perhaps after the report is published it won't change things. I remember going thropugh something similar before and the regional settings resolved.

-k

 

[bdevitt]

Yes I did change the regional settings to 0 decimals. I then reran the report in CR 10, saved it, then re-published in CE 10. Running an instance of the new report in CE 10 I see the same resultant behavior 2,006.00 etc.

I then changed regional settings back, reran the CR 10 report, republished, and get the same result 2,006.00 etc. Of course, my Cstr version of the report works beautifully.

Still thinking...

-bd

 

[synapsevampire]

Hmmm, odd, I guess the charts ignore it.

I guess the only other suggestion would be to make sure that you have the latest service pack on CE.

-k