Ordering graph x axis with formula 1

[gav12345]

Hi,

Using CR10 against Oracle 11g.

We have a graph which currently uses a field directly from the database (i.e. not a formula) for its x-axis data. An example of the graph presentation would be:

4
3
gm 2
1
0
0 7 14 21 28 35
Timepoint


We are now trying to assign different labels to individual 'Timepoint' values on the x-axis (by using a formula to combine 2 database field). What we would want to see is:


4
3
gm 2
1
0
0 7 14 21 28 35
Pretrial Day Day Day Day Day


Unfortunately what we're seeing is below. basically, as soon as we use a formula, the data is ordered by the first number in the value that Crystal finds. By this I mean that '7' wil be displayed after '35', i.e.


4
3
gm 2
1
0
0 14 21 28 35 7
Pretrial Day Day Day Day Day


Can anyone tell me how we can order the values on the x-axis correctly using a formula?

Thanks in advance, Gavin

 

[lbass]

You could try using:

val({table.field})

Or, totext(val({table.field}),"00")

-LB

 

[IanWaterman]

Looks like Its because its a stringnumber, try converting to a number

@convert
tonumber(Stringnumberfield)

Then use this as x axis instead of your field.

Ian

 

[gav12345]

Thanks both - but I don't think I've been very clear. The field I have on the x-axis is a concatenation of a numeric field, some spaces, and a non-numeric field.

For example...

"    " {GET_CONTRIBUTION_PAGE.CM_TIME_POINT_NUMBER} & "    " & {GET_CONTRIBUTION_PAGE.CM_TIME_POINT_UNIT}

would give...

   0
Pretrial

The formulae you've suggested seem to assume that there is no text required?

 

[IanWaterman]

As LB suggested pad with leading zeros

" "& totext(val({GET_CONTRIBUTION_PAGE.CM_TIME_POINT_NUMBER}),"00" )& " " & {GET_CONTRIBUTION_PAGE.CM_TIME_POINT_UNIT}

Ian

 

[gav12345]

Sorry, should have said that I did try that but it initially generated an error because val was looking for a string, not a number. But I've noticed we have a similar string field that I've tried using for which val works perfectly. - Thanks for the help.
Gavin

 

[lbass]

You could have just removed the val() then and used totext(number,"00").

-LB