Solved

Formula in Crystal - numeric fields along with string fields

Posted on 2013-01-29
14
376 Views
Last Modified: 2013-02-03
I need to take a string field and change it to a numeric field so I can round the decimal points in the Crystal report.

I have a formula already made for this field that includes some logic for this field. It appears that it gets cranky when I want to make some "if's" when it is a string and then change it to a numeric field within that same formula. See below. Is this not possible? What do I need to do to change the field to a numeric value in order to round the decimal point when this can also be a string field.

It is requiring that the "else" portion also be numeric even if I state, totextt in the formula.

if isnull ({Out_FNData.Out_FNData_IFTResult}) then {Out_FNData.Out_FNData_VendorResult}
else if {Out_FNData.Out_FNData_IFTResult} = "True" then "Pass"
else if {FN_ProductSpecs_Tests.FN_PS_TestID} in [174, 175] then tonumber ({Out_FNData.Out_FNData_IFTResult})
else {Out_FNData.Out_FNData_IFTResult}
0
Comment
Question by:Joe Brown
  • 9
  • 4
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 38833790
A formula can only return 1 data type.

You are trying to return a number or a string (PASS)

You can use CStr or ToText to change the number to a string

CStr({NumericField},NumberofDecimals,"Thousands Separator")

Round to 3 decimals with no thousands separator

           CStr({YourField},3,"")

or

CStr({NumericField},"FormatString")

      CStr({YourField},"#####0.000")

mlmcc
0
 

Author Comment

by:Joe Brown
ID: 38845818
I need to explain my problem a little bit better and I am sure you can help me with a formula that will work.

I have a field named IFTResult and it is a string field in SQL.
Sometimes this field has words but most of the time it is a numeric text.
Then I have another field named TestID and it is an integer field.

I have a crystal report where I have to report the field with the words when the Test ID is equal to 179, 116 or 195. I have to report the field as a number with one deciamal place when the Test ID is equal to 105, 106, 189, 190, 186, 200, 177, 161 and I have to report the field as an integer the rest of the time.

I have tried many differen combonations, even one where I make a formula field for each situation. It likes all my formulas but as soon as I put a formula on there that has a "not" value on to the report I get an error.
If I try this at any point:
if {FN_ProductSpecs_Tests.FN_PS_TestID} <> [179, 116, 195] then tonumber ({Out_FNData.Out_FNData_IFTResult})
It states that the string is non numeric.

It would be nice to have it report the 179, 116, 195 as a string - the 105, 106, 189, 190, 186, 200, 177, 161 as a number with one decimal and the rest as integers but I can't figure out how to do this with the test id being numeric and the iftresult being a string and I am just pissing it off :)
0
 

Author Comment

by:Joe Brown
ID: 38845834
And fields that I need to result as an integer have to round the value.
That is why my goal was to turn all tests to a numeric field when they were not in the short list of tests. And turn them in to a number when they were in the longer list, then allow that field to be shown as is (0.3) then I was trying to make a third formula field that would take all other tests not noted above and turn them in to a numeric field also - then take that field and format it to round. I was going to layer them over each other and then supress when each was not valid.

I'm sure that is not best practice, but I am desperate to find a solution to this
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38845929
>>If I try this at any point:
if {FN_ProductSpecs_Tests.FN_PS_TestID} <> [179, 116, 195] then tonumber ({Out_FNData.Out_FNData_IFTResult})

That means on of the other values has non-numeric text in it.

if {FN_ProductSpecs_Tests.FN_PS_TestID} IN [179, 116, 195] THEN
     {Out_FNData.Out_FNData_IFTResult}
Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161]
(
      If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
           CStr({Out_FNData.Out_FNData_IFTResult},1)
      Else
           {Out_FNData.Out_FNData_IFTResult}
)
Else
(
      If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
           CStr({Out_FNData.Out_FNData_IFTResult},0)
      Else
           {Out_FNData.Out_FNData_IFTResult}
)

mlmcc
0
 

Author Comment

by:Joe Brown
ID: 38845961
I tried this and I get an error that the keyword then is missing
right at the parenthesis before
 If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
0
 

Author Comment

by:Joe Brown
ID: 38845971
Also, will this part of the formula round the number or just omit the numbers to the right of the decimal point?
 
      CStr({Out_FNData.Out_FNData_IFTResult},0)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38845995
If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then

It will round and not just truncate

You could also use

CStr(Round({Out_FNData.Out_FNData_IFTResult},0) ,0)

mlmcc
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Joe Brown
ID: 38846140
I tried this and I get an error that the keyword then is missing
right at the parenthesis before
 If IsNumeric({Out_FNData.Out_FNData_IFTResult} then
0
 
LVL 34

Expert Comment

by:James0628
ID: 38846551
mlmcc left the "Then" off of the third line.  It should be:

Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161] then


 And, as already mentioned, the two lines with IsNumeric are missing a ")".  They should both be:

      If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then

 James
0
 

Author Comment

by:Joe Brown
ID: 38848893
Thanks James - I will try that now.
0
 

Author Comment

by:Joe Brown
ID: 38848919
I had forgotten that I that I had already tried this after adding the "then" that was missing and I had figured out that the parenthesis weremissing.

I added these and get an error on the 5th line that "There are too many arguments have been given to this function."

It is highlighting this error on the number 1 in the following line:
CStr({Out_FNData.Out_FNData_IFTResult},1)
0
 

Author Comment

by:Joe Brown
ID: 38848922
Here is the complete formula I currently have.

if {FN_ProductSpecs_Tests.FN_PS_TestID} IN [179, 116, 195] THEN
     {Out_FNData.Out_FNData_IFTResult}
Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161]
 then (
      If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
           CStr({Out_FNData.Out_FNData_IFTResult},1)
      Else
           {Out_FNData.Out_FNData_IFTResult}
  )
Else
  (
      If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
           CStr({Out_FNData.Out_FNData_IFTResult},0)
      Else
           {Out_FNData.Out_FNData_IFTResult}
  )
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 38849220
Is  {Out_FNData.Out_FNData_IFTResult} a string already?
If so you have to convert it to a number then you can format it with CStr



if {FN_ProductSpecs_Tests.FN_PS_TestID} IN [179, 116, 195] THEN
     {Out_FNData.Out_FNData_IFTResult}
Else If  {FN_ProductSpecs_Tests.FN_PS_TestID} IN [105, 106, 189, 190, 186, 200, 177, 161]
 then (
      If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
           CStr(Val({Out_FNData.Out_FNData_IFTResult}),1)
      Else
           {Out_FNData.Out_FNData_IFTResult}
  )
Else
  (
      If IsNumeric({Out_FNData.Out_FNData_IFTResult}) then
           CStr(Val({Out_FNData.Out_FNData_IFTResult}),0)
      Else
           {Out_FNData.Out_FNData_IFTResult}
  )

mlmcc
0
 

Author Comment

by:Joe Brown
ID: 38849739
Perfection!!!!!!!!!!!!!!!!
Thanks so much mlmcc
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now