Error Using SQL Server nText Field in Formula

[tubbers]

I'm using Crystal Reports XI and connecting to a SQL Server database via OLE DB (ADO).

I have a table which contains lab results. The field, ValueText, has the datatype of ntext with a precision of 16.
Sample data:

  <0.3
  73
  9.2
  NO INTERPRETATION
  Pending
  138
  4.4
  87
  40
  15
  126
  16
  0.9
  8.5
  >200
  21KH75915
  AMENDED ON 07/15 AT 0614: PREVIOUSLY REPORTED<20 CK </= 20

As you can tell there is no rhyme or reason to what an individual can input into this field. For the specific test results I'm looking for, the values could be simple numbers (entered as text) or >200.

These are the formulas that I started with. It worked a few times and now I get an error. There is no error message, when I refresh the report it just takes me to the Formula Workshop and highlights one formula.

@Convert Test Results

Left({CDRObservation.ValueText},4)

I then convert the values to numbers for calculation purposes.
@Convert to Number

If {@Convert Test Results} = ">200" Then 200
Else
ToNumber({@Convert Test Results))

Then I determine whether the test results are in the range that I need
@In Therapy Range

If ({@Convert to Number} > 69 AND {@Convert to Number} < 111) Then "Y"
Else
    "N"

When the report errors out, in the Formula Workshop it opens {@In Therapy Range}, highlights the first {@Convert to Number}.

I don't know where I've gone wrong or if I am trying to accomplish something that Crystal Reports will not allow me to do.

I only have view access to the database so I cannot create Views or Stored Procedures. I attempted to use a SQL Expressions Field but I received the same error response.

%Convert Test Results

{fn SUBSTRING("CDRObservation"."ValueText",1 ,4 )}

I would appreciate any help or suggestions.

 

[synapsevampire]

Post the formula used in it's entirety.

Try changing your SQL Expression to:

SUBSTRING(CDRObservation.ValueText,1 ,4 )

Not sure what good this SQL Expression is going to do you, you haven't described intent yet.

The clear problem is that your front end coders are bad, there should be a specific field for the value as a numeric if that is what you're expecting.

So to try to extract values from it will be tricky, especially trying to interpret their various ways of describing it, such as >

You can certainly extract numbers, but we'll go with a simple model to try to get some reasonable values:

if isnumber({CDRObservation.ValueText}) then
val({CDRObservation.ValueText})
else
if left({CDRObservation.ValueText,1) in ["<",">"]
and
isnumber(substr({CDRObservation.ValueText,2)) then
substr({CDRObservation.ValueText,2)
else
0

That should get you closer.

The rules will go on forever I fear...

-k

 

[tubbers]

Here's my new formula based on what you suggested. I had to make some changes so that the functions would "turn blue":

@Test Results to Number

[COLOR=blue]If IsNumeric[/color]({CDRObservation.ValueText}) [COLOR=blue]Then Val[/color]({CDRObservation.ValueText})
[COLOR=blue]Else
    If Left[/color]({CDRObservation.ValueText},1) [COLOR=blue]in [/color]["<",">"]
    [COLOR=blue]And
    IsNumeric[/color](substr({CDRObservation.ValueText},2)) [COLOR=blue]Then[/color]
substring({CDRObservation.ValueText,2)
[COLOR=blue]Else[/color]
0

After I changed isnumber to isnumeric, that worked. I tried changing the substr to substring but neither of the word variations works. Is there another function that I could use that would be correct or am I missing something that would allow either the substr or substring functions to work? When I check the formula, it highlights the first substr and says "The ) is missing".

 

[tubbers]

I think I've got it working:
First, I only select the test results which have a length of 4 or less (because for at least this specific report I know for a fact it will never be longer than 4).

@Max Length of Results

[COLOR=blue]Len[/color]({CDRObservation.ValueText})

Report Selection Formula

{@Max Length of Results} <= 4.00

Then using the formula you suggested, I made a few changes:

[COLOR=blue]If IsNumeric[/color]({CDRObservation.ValueText}) [COLOR=blue]Then Val[/color]({CDRObservation.ValueText})
[COLOR=blue]Else
    If Left[/color]({CDRObservation.ValueText},1) [COLOR=blue]in [/color]["<",">"]
    [COLOR=blue]And
    IsNumeric[/color]([COLOR=blue]Right[/color]({CDRObservation.ValueText},3)) [COLOR=blue]Then
ToNumber[/color]([COLOR=blue]Right[/color]({CDRObservation.ValueText},3))
[COLOR=blue]Else[/color]
0

Looks like it works so far.

 

[synapsevampire]

Yeah, you need to return the same data typewithin different parts of a formula, you can't output text and then a string from the same formula.

Looks about right, sorry about the isnumber, I go from memory and it's olde and craggy

You might change your last attempt to:

If IsNumeric({CDRObservation.ValueText}) Then Val({CDRObservation.ValueText})
Else
If Left({CDRObservation.ValueText},1) in ["<",">"]
And
IsNumeric(mid({CDRObservation.ValueText},2)) Then
ToNumber(mid({CDRObservation.ValueText},2))
Else
0

-k

 

[tubbers]

Is using MID rather than RIGHT a more efficient or "correct" or is it simply a matter of preference.

Thank you for your help in this matter.