help with coverting string field to number

[qster]

Hi,

would anyone here know of a way or a formula to convert a string field to number?
I've managed to convert it to a number field, but I ran into a problem. When I check the browse data field, it lists it as a 'number' field but the box is blank (if done correctly it should have a range of numbers)
I need this in order to calculate a running total.

unless anyone can think of another way around this.

thanks in advance...

 

[cbarrol]

Try the ToNumber function. You will find it in the Functions Pane of the Formula Editor. There are some other handy string functions there as well.

 

[qster]

I've already tried the "toNumber" function
it changes the field to a number but does not display any of the numbers in the data field when you check the "browse data field", so I'm not able to calculate the running total

 

[cbarrol]

Im not 100 percent sure of this but if you use a formula field to do the conversion, then do a running total using the formula field, it may work, regardless if it shows up in the browse data box or not.

 

[qster]

that cannot be done.
in order for running total to work in the manner you are thinking, you would need to the field as a "number" and be able to view the range of numbers, otherwise you get "the summary / running total field cannot be create"

you get this error because, I don't think CR 8.0 understands what to calculate as the field is not a true number field.

i'm still stumped.

 

[kenhamady]

Tell us what version of CR you are using and post the formula that you are using to convert the text to number. I prefer VAL() to ToNumber myself, since ToNumber will error if it hits an alpha character in the string.

What you see in browse has nothing to do with your totals. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[qster]

i'm using CR version 8.0

to measure the time in between end_date to next start_date, I'm using: (formula I name {@timebetween}

WhilePrintingRecords;
//get the login date of the next record
DateTimeVar nextLogin := Next({Consolidated.START_DATE});
//get the Date diff between the logout date of this record and nextLogin
NumberVar myDateDiff := DateDiff("n",{Consolidated.END_DATE}, nextLogin)/60;

(which gives me a string value)
I then create a new field and place the new formula named {@hook time} in the details section to give me a total time for each segment within a ticket and put in:
tonumber ({@Timebetween})

now what i'm looking at is finding a way to do a running total for the {@hook time} to give a total time count.
which i've created another field {@total hook time} but i'm stomp as to a formula as "sum" will not work for the {@hook time}

hope this is enough info for you

 

[kenhamady]

The problem is the NEXT() function. That comes into play after the totals are calculated. That is why you can't use the SUM function. You can sum these values if you use the 3-formula technique described in faq149-182. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[qster]

with a bit of tweaking around, I got the run total.
it wasn't exactly what I was looking for in the run total as it is giving me a run total as the data goes along.
I guess I'll have to do with this.

Now the problem is trying to get the run total to stop.
I've surpress the last data in each grouping of tickets, but the problem now is the run total is carrying over to the next ticket in the report.

Does anyone know how to stop the run total for each new ticket/group?

 

[kenhamady]

The 3-formula technique describes a 'reset' formula to assign a zero to the variable. Put this in the group header of the group that should start over. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[qster]

ok..
I figured out what the problem was.
I forgot to place the WhilePrintingRecords in the formula in the GH section.

question is there any way to get the final run total value in the GF.

I use
{FINAL.TICKET_NUMBER} <> next({FINAL.TICKET_NUMBER}) to surpress the final time time as it is subtracting the Start time and End time for the next ticket.
so for now I've surpressed the GF as it takes the very last run total with is not correct.

 

[kenhamady]

Post your three formulas, and the section they are located in on the report. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[qster]

here they are.

GH:
whileprintingrecords;
numbervar HookTime := 0

Details:
Whileprintingrecords;
Numbervar HookTime := HookTime + ({@Time Between});

GF:
Whileprintingrecords;
Numbervar Hooktime

using this it give a run total for each state in the ticket.

I managed to get total run time from first start date to end date using:
whileprintingrecords;
(maximum ({FINAL.END_DATE}, {FINAL.TICKET_NUMBER}) - minimum ({FINAL.START_DATE}, {FINAL.TICKET_NUMBER})) *24

that gives me the total time i'm looking for, however the final totals do not match the final runtotal from the 3 step formala

 

[kenhamady]

Is your time between formula including the time between the last record of one group, and the first record of the next? Sounds like the max and min are giving you accurate results, so do you need to do it both ways? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[qster]

yes that is what the @time between formula is doing.
it is measuring the end time to the next start time.
The Max and Min are giving the correct results, but the reason I need to do individual times is because we have different status in each ticket and I would like to run a query to give me total time between for a certain status.

also the max-min total time does not match the running total of {@time between} which I call hook time because we have auto_escalator times inbetween which I don't need to calculate.

So essentially, what I'm looking to find out if there is a way to give me a final total in the Group Footer for each ticket properly without having to export to MS Excel and creating a new CR report each time

 

[kenhamady]

Tell it to NOT look at the next record, if the next is a different group. Then you have to decide how to calc a time for the last record. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com