Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Remove Comma from Parameter within a Formula

Posted on 2013-06-14
8
Medium Priority
?
894 Views
Last Modified: 2013-06-21
Hey guys,

I have the following formula:

if {?Year}-1 = {Command.FISCAL_YEAR} and {Command.ACCOUNT} = 4000 then {FBDETAIL.DB_UNITS_12}+{FBDETAIL.CR_UNITS_12} else
if {?Year}-1 = {Command.FISCAL_YEAR} then {Command.DB_AMOUNT_12}+{Command.CR_AMOUNT_12}

And it works correctly. The only problem is when the user is prompted, the Year parameter shows a comma. How can I alter the code so that I can remove the comma?
0
Comment
Question by:metalteck
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 39247958
Assuming the parameter is a number, you don't need to remove it.

YOu can try setting the default format for numbers to be -1234 but I don't think that affects the parameter screen.

What is the problem with the comma?

mlmcc
0
 

Author Comment

by:metalteck
ID: 39248028
The user is selecting a year and its coming up as 2,013 instead of just 2013.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39248555
It is a number so to change its display you may have to change the default number display under
FILE --> OPTIONS

You have no control over the parameter entry screen.
If you mean that is the way it displays in the report then just format it to remove the comma

mlmcc
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 

Author Comment

by:metalteck
ID: 39248720
I'v tried changing it under File--options--fields--number, but when I refresh the report and ask for new parameters, its still showing the comma.

I tried converting it to a string, but by doing that, if I try to do year-1, it don't think that will work.

Is there any other way I can use to get rid of the comma?
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 39248764
None that I know of.

You can use

Val({?Year}) - 1  if you set Year as a string.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 39249832
Assuming that you're running the report from within CR, then it is possible to change the format for numeric parameters, but there are definite caveats.

 Go to File > Options, select the Fields tab, click on Number and then select the Number tab.  The format selected there is used for numeric parameters.

 There are two basic "gotchas":
 1) That setting will affect every numeric parameter in any report that is run from CR.
 2) That sets the default format for any numeric _field_ that is added to a report.


 If you don't want to use that setting, then you could make the parameter a string and then, as mlmcc mentioned, convert the string to a number.  That's what I did for parameters like that.  It helps if you're setting the default values for the parameter and forcing the user to select one of those values.  Then you don't have to worry about the user entering something that is not a valid number.  If the user is typing in the value, you may want to add some basic "sanity checks", to make sure that you don't end up with a problem value.

 James
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 39266206
I agree with mlmcc - this is how I get around the "ugly" format for years. I set the parameter input type to be string, and then convert it to numeric in the record selection.

I would probably add an if to confirm format in record selection - i.e.: if someone times ABCD instead of 2013, the report will error without a condition to check for that.

{table.field} = (  IF isnumeric({?Year})=false THEN 1900 ELSE tonumber({?Year}) )
0
 
LVL 35

Expert Comment

by:James0628
ID: 39266307
FWIW, you could use Val (as mlmcc did) instead of ToNumber.  Then at least you wouldn't get an error if the string was not a number.  You just get 0.  Whether or not that's a problem depends on the report, data, etc.  If you're just comparing it to a year field (and every record has a valid year), then you'd just get no data, which might be acceptable.

 James
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Integration Management Part 2
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question