Charting from string of data

[NewToCrystalX]

I am a beginner and I cannot figure out how to create a line chart in CrystalX that pulls data from a SQL database STRING field. Chart expert wants to count the number of values in the field rather than charting each value in the field.

Here is an example of my string data in SQL - 10.86,10.72,11.25,13.10.

In this example I get a returned singlevalue of 4 on my chart since there are 4 values in the string. I need the chart to plot each value.

Do I have to create a fornula field to read the string?

I know this is a beginners question. I appreciate your help.

 

[lbass]

I think you should convert the field to a number:

tonumber({table.string})

Then you might have to change the setting to "for each record" if you don't want to use a summary or check "do not summarize".

-LB

 

[NewToCrystalX]

I get the error "The string is non-numeric"

The DATA TYPE of the field in SQL is VARCHAR(MAX).

Dan

 

[lbass]

Sounds like you might have a null in the field. Go to file->report options->check "convert nulls to default values" and see if that resolves the issue. Otherwise, leave the conver nulls to default unchecked and instead create a formula like this:

if isnull({table.string}) or
not isnumeric({table.string}) then
0 else
tonumber({table.string])

Then use this formula for your chart.

-LB

 

[NewToCrystalX]

LB -

I tried both solutions.

Changing the report options netted the same error as before.

The formula gives the result of ZERO in every case.

I think my problem is with the "field type". The field(s) show up as the field type "memo" in field explorer. I believe with the formula you gave me this would be the reason that it is returning a value of ZERO every time???

Again, in SQL the field type is set to VARCHAR(MAX). Below is a sample of the actual data from 1 record.

If you need me to send you anything that might help you, just let me know.

I really appreciate your help!!

Dan



8.11938554644007,8.38494591116315,8.56939244804556,9.29834806250566,
8.23465110468388,7.75093154490433,7.80838475680928,8.07882868060015,
8.32091937239763,7.15646510626607,7.91403128313438,6.69392956427596,
6.31134031133667,6.3234491299448,6.45240892773363,6.12523624747281,
5.27827196062102

 

[lbass]

I'm not sure what to suggest here. There are several problems--one is that CR won't display numbers with more specificity than about 10 decimals, and you need these to be numbers to chart. I guess you could potentially multiple each value by 10,000 in order to get relative values.

The second issue is that you have to split these values out of the memo field to create separate "fields" for each and then convert them to numbers. The only way I could chart them was to create a separate bar for each value, but I was unable to create a line chart this way.

Ideally, these values would be in one field. Maybe there is a way you could use substrings to split these out and then combine them into one field in a command. Something like:

select substr("table"."memo",1,14) "value"
from "table"
union all
select substr("table"."memo",15,14)
from "table"
union all
select substr("table"."memo",29,14)
from "table" //etc.

Then you could create a formula like this:

if isnull({command.value}) then
0 else
tonumber({command.value})*10000

Then you should be able to chart. You could then note that the chart is based on 10,000's. Note also that you might have to use {fn substring()} instead of substr()--I can't remember which works with Oracle.

-LB