number formats (again!)

Hi

I'm having a problem with displaying my reports through my application at runtime. One of the formula fields on the report contains the id of the record I'm displaying and if the record's Verified field = True, it also shows a string. The formula is this:

If {?PubNoVer} = "Yes" then
if {View_TM_Get_BookID_BY_Surname.Verified} = True Then
{View_TM_Get_BookID_BY_Surname.BookID} & " Verified"
Else
{View_TM_Get_BookID_BY_Surname.BookID} & " "
Else
" "

In the report preview window, this all looks fine but running it through my application made any four digit id look wrong because it added a thousand separator and decimal places e.g. 1,512.00

In a previous post on another forum, a user very kindly showed me that using ToText on the ID field would get rid of the decimal places, so that's fine, but I still have the thousand separator and can't get rid of it!

Does anyone have any ideas?just format the field


right click on field, select format field, go to number that and select the format u want to display in|||just format the field


right click on field, select format field, go to number that and select the format u want to display in

Yeah, I did that but it only shows the correct format in the preview window, when running the report through the application, it shows the thousand separator and the decimal places even though i'd specified for that not to happen!

OK, I've found half an answer on another site. To get rid of both the decimal places and the thousands separators you use:

ToText({fieldname},0,"")

This works but because I'm also grouping on the id field, the ids still show with a thousands separator on the group tree at the side of the report! Anybody know how to format that?!|||You could create a formula like:

right('0000000000' & totext({fieldname}, 0, ""), 10)

and then group on that formula instead of the fieldname.
Trouble is you get a load of leading zeros in front of the number in the group tree.
You could limit this by finding the maximum length of the field with a whilereadingrecords formula and changing the formula to
right(replicatestring('0', {@.maxlen}) & totext({fieldname}, 0, ""), {@.maxlen})

You could use a space instead of a zero, but the numbers in the group tree won't line up, although they will still be in numeric order.

If anyone knows another way (apart from setting the PC's local settings) I'd like to know...|||You could create a formula like:

right('0000000000' & totext({fieldname}, 0, ""), 10)

and then group on that formula instead of the fieldname.
Trouble is you get a load of leading zeros in front of the number in the group tree.

Thanks JaganEllis. Instead of the formula you suggested, I used ToText({fieldname}, 0, "") and grouped on it and this worked perfectly!|||I can see how that helps with the format of the group tree data, but what about the group ordering? Unless all your number have the same number of digits, the order will be messed up (if important to you).
e.g. 300 will be after 2000 because the string '3' is > the string '2'|||I can see how that helps with the format of the group tree data, but what about the group ordering? Unless all your number have the same number of digits, the order will be messed up (if important to you).
e.g. 300 will be after 2000 because the string '3' is > the string '2'

Ah right... I see what you mean! I've used spaces instead of zeros. Thanks!

number formats

Hi

I'm using Crystal Reports for Visual Studio 2005. I'm having problems with displaying numbers in the correct format.

I've gone into Design > Default Settings menu and set the format of number fields to no decimal place (i.e. 1 instead of 1.0) and taken out the thousand separator. But this doesn't seem to have made a difference.

On one report, the numbers show ok (e.g. 54) on the report preview but when I run it through my application, the numbers are showing with decimal places (e.g. 54.00).

On another report, the ids of the records shown in the report appear in the group tree and have thousand separators on them when run through my application. As with the other report, they appear correctly in the report preview!

How can I make the format show correctly when running the report through my application?

ThanksFor anyone that's interested, I got the answer elsewhere. the formula to use is ToText({fieldname},0,"")

Nullifying Measure not related to the displayed dimension, yet displaying Grand Totals

Hello,

I have 2 measure groups; say X and Y. X is related to the dimensions products and customers, while Y is related to Products only. I needed to create a view combining measures from X and Y. Now if I drag a measure from Y in front of the Customers dimension members the total for that measure is displayed in front of every Customer there. What I really need is to set the measures from group Y to Null when displayed in front of individual customers, and display their total value only on the Customers.All level and for 'Grand Totals'. I tried created a calculated member over that measure to act as a filter by using IIF function to check if customers.currentmember.level.ordinal > 0 to set the calculated measure to Null, else display the underlying value. Things work fine now in the BIDS Browser, but when I try to create the same view in ProClarity the Grand Total is Null. Any Clues?

How about setting IgnoreUnrelatedDimensions to false for Measure Group Y?

http://msdn2.microsoft.com/en-us/library/ms365411.aspx

>>

SQL Server 2005 Books Online

Configuring Measure Group Properties

Measures groups have properties that enable you to define how measure groups function.

...

IgnoreUnrelatedDimensions

Determines whether unrelated dimensions are forced to their top level when members of dimensions that are unrelated to the measure group are included in a query. Default setting is True.

>>