obrienj
asked on
Setting input field formats
I have an crystal report which reads from a view. There is a field in the view that has up to 4 decimal places.
When I add that field to the report it appears with only 2 decimal places. This can be altered specifically (field formatting), for that field, to display 4 decimal places.
But, If I add the field again, it is back to two.
Is there a way to set the field format for the input for the entire report. No matter where it appears. Whether it be on the report or within a formula etc.
The only place I have found to do this is in the File->Options and set the overall number formatting to 4 decimal places. This is not ideal for two reasons. One, I don't really need the format for all fields. Two, this seems to only have effect on my own machine. It does not work for my client machines.
Please please help me with this.... very very very urgent.
When I add that field to the report it appears with only 2 decimal places. This can be altered specifically (field formatting), for that field, to display 4 decimal places.
But, If I add the field again, it is back to two.
Is there a way to set the field format for the input for the entire report. No matter where it appears. Whether it be on the report or within a formula etc.
The only place I have found to do this is in the File->Options and set the overall number formatting to 4 decimal places. This is not ideal for two reasons. One, I don't really need the format for all fields. Two, this seems to only have effect on my own machine. It does not work for my client machines.
Please please help me with this.... very very very urgent.
peter57r
You can't apply general number formatting options just to specific fields. Either you accept the format for all numbers or else you format specific fields the way you want.
You can do a multi-select of several number fields (using shift-click) and format them all in one step.
ASKER
Okay, I'd have no problem setting the format individually, but can that be done within formulas and functions???
ASKER
Also, as I said, the overall number formatting doesn't seem to work. Only effects my own machine. This report goes out to 60+ users.
You cannot set number formats in a formula field that calculates the result you want to format.
The default formatting will be applied after the formula result is calculated.
So I don't see that a formula based result would help unless you are happy to convert the result to text within the formula field, which means you can't then use it in any calculations or summaries.
The default formatting will be applied after the formula result is calculated.
So I don't see that a formula based result would help unless you are happy to convert the result to text within the formula field, which means you can't then use it in any calculations or summaries.
ASKER
I'm not putting it into a formula for a solution. It is within formulas.
I am using the field to do calculations. Then output is coming out wrong because the decimal places are been cut off.
Do you know anything about the default formatting only effecting my current machine, and not client machines?
I am using the field to do calculations. Then output is coming out wrong because the decimal places are been cut off.
Do you know anything about the default formatting only effecting my current machine, and not client machines?
ASKER
This is what really gets me.
I have a custom function.
Dim Reading as Number
Reading = 25.32423
getreading = cstr(Reading,"##0.0000")
This displays 25.3200 on the page.
I have a custom function.
Dim Reading as Number
Reading = 25.32423
getreading = cstr(Reading,"##0.0000")
This displays 25.3200 on the page.
I don't know what you mean by..
"I'm not putting it into a formula for a solution. It is within formulas."
The basic rule has to be that if you want the report to appear with specific field formatting then you should apply that formatting to your fields.
However, my understanding is that formatting is defined at the time the report is designed (and more specifically - at the time a field is added to the report) and will not change from user to user unless you specifically select the 'user default' option in the format list.
But you still have the issue that you cannot specify the File>options settings for some number fields and not others. Anything that should not conform to the File>Options setting must be defined using FormatField.
"I'm not putting it into a formula for a solution. It is within formulas."
The basic rule has to be that if you want the report to appear with specific field formatting then you should apply that formatting to your fields.
However, my understanding is that formatting is defined at the time the report is designed (and more specifically - at the time a field is added to the report) and will not change from user to user unless you specifically select the 'user default' option in the format list.
But you still have the issue that you cannot specify the File>options settings for some number fields and not others. Anything that should not conform to the File>Options setting must be defined using FormatField.
...getreading = cstr(Reading,"##0.0000")
This displays 25.3200 on the page. .....
Yes - because you are converting the result to a string as I indicated earlier.
But you can't sum this field or do any other calculations with it.
This displays 25.3200 on the page. .....
Yes - because you are converting the result to a string as I indicated earlier.
But you can't sum this field or do any other calculations with it.
ASKER
What I'm saying is that the field is not "added" to the report in some instances. I am using it within formulas/functions for calculations. Is there nothing I can do to set the formatting in those intances?
ASKER
"Yes - because you are converting the result to a string as I indicated earlier.
But you can't sum this field or do any other calculations with it."
No, you misunderstand the issue. The converting to a string does nothing for it either.
The number should be 25.3242, but it comes out 25.3200.
As far as I can tell, the last two decimal places are not getting set within the reading variable field at all.
But you can't sum this field or do any other calculations with it."
No, you misunderstand the issue. The converting to a string does nothing for it either.
The number should be 25.3242, but it comes out 25.3200.
As far as I can tell, the last two decimal places are not getting set within the reading variable field at all.
ASKER
I really can't get over this. It makes no sense.
Crystal Reports is going out of it's way to alter the value of an input field. Why would it do this.
Not only that, there is no way around it. It's just tough. It makes no sense I tell you.
Crystal Reports is going out of it's way to alter the value of an input field. Why would it do this.
Not only that, there is no way around it. It's just tough. It makes no sense I tell you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
okay, that method worked... at least it brought back the right number. I might be able to use that.
ASKER
Okay... got it working within the formula.
Had to use the method above and play around with it in a few other places. Thanks for your patience, although I would still see this as a serious limitation on Crystal Reports.
Had to use the method above and play around with it in a few other places. Thanks for your patience, although I would still see this as a serious limitation on Crystal Reports.
If I'm following what you're saying, you're overestimating the problem.
CR uses the actual value in a field whenever it's referenced. If you put the field on the report, CR will _show_ it with however many decimal places you specify, but the value is not affected by that in any way. If you tell CR to summarize that field, you get a summary of the actual values in the field, not the rounded values. If you use the field in a formula, the actual value is used. CR does not do any rounding in the formula, unless you tell it to (using Round, Truncate, etc.).
OK, technically, there are some precision limits, but aside from that, CR uses the actual values unless you tell it not to (eg. using Round). With values like 25.32423, precision should not be an issue.
As you noted, for some reason using CStr with a format string (eg. "#.0000") does not show you all of the decimal places. I'm not sure why. It could be a bug, or it could be a by-product of the way CStr works. But, as Peter pointed out, you can use alternate forms of CStr, where you use a separate argument to specify the number of decimal places, and that does work. But again, that's only affecting how the field is shown. It has no affect on the value of the field.
As for the settings under File > Options not affecting other users, that's correct. What you're setting there are defaults for CR on your machine. They have no immediate affect on any existing reports. They just affect what happens after that. For example, if you set the default number format there to 4 decimal places, then from then on, when you put a numeric field on a report, it will default to 4 decimal places. But the fields that are already on the reports won't change because you changed that setting.
I hope this clears up some things.
James
CR uses the actual value in a field whenever it's referenced. If you put the field on the report, CR will _show_ it with however many decimal places you specify, but the value is not affected by that in any way. If you tell CR to summarize that field, you get a summary of the actual values in the field, not the rounded values. If you use the field in a formula, the actual value is used. CR does not do any rounding in the formula, unless you tell it to (using Round, Truncate, etc.).
OK, technically, there are some precision limits, but aside from that, CR uses the actual values unless you tell it not to (eg. using Round). With values like 25.32423, precision should not be an issue.
As you noted, for some reason using CStr with a format string (eg. "#.0000") does not show you all of the decimal places. I'm not sure why. It could be a bug, or it could be a by-product of the way CStr works. But, as Peter pointed out, you can use alternate forms of CStr, where you use a separate argument to specify the number of decimal places, and that does work. But again, that's only affecting how the field is shown. It has no affect on the value of the field.
As for the settings under File > Options not affecting other users, that's correct. What you're setting there are defaults for CR on your machine. They have no immediate affect on any existing reports. They just affect what happens after that. For example, if you set the default number format there to 4 decimal places, then from then on, when you put a numeric field on a report, it will default to 4 decimal places. But the fields that are already on the reports won't change because you changed that setting.
I hope this clears up some things.
James