Running Total

[donodm]

I am using Crystal Reports 8.5

I have a report that uses a cross tab to count the number and types of appointments that our trainers have with our clients within a specified time (usually monthly). So it looks like this:

Initial Training: 3
Follow-Up Training: 3
Travel: 5

For each appointment we also track the duration. What I want to do is get a mothly total of the durration of each type of event--not a count. So it should look like this:

Initial Training: 3 16 (hours)
Follow-Up Training: 3 20 (hours)
Travel: 5 12 (hours)

The table where the information is stored is the CONTHIST table--we are using GoldMine. Is there a way that I can do a running total of CONTHIST.DURATION in the crosstab?

Thanks,
Dave

 

[kenhamady]

I don't think you want a running total, which you can see getting bigger as it accumulates. I think you want a sum of durations for each category. One challenge, in Goldmine, the duration field is a string so you will need the VAL() function to convert it into a number. You will also have to make sure that they are stored consistently in minutes (I am not sure if GM can store hours as well).

Once you have a numeric in minutes for each record, you can convert to hours by dividing by 60, and then sum the converted value in your cross-tab.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[donodm]

Thanks Ken,

I am now running into two problems: 1. The data is not consistent and 2. I'm having problems converting the string to number. Can you help? I'm pretty new at this.

Dave

 

[kenhamady]

What happens when you use the VAL function?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[donodm]

Hi Ken,

Wasn't sure how to use the VAL function--never done it before. I just went in and saw that all the data is inconsistent. It is in **:**:** format but some people were using the two right colums for seconds and others were using them for minutes, and others were typing things in.

Dave

 

[kenhamady]

There is another field that tells you what the duration is measured in. You can use that to determine which positions in the string to take for your val(). You will certainly need some If-Then logic.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[donodm]

Hi Ken,

This is what I have so far to convert it to a number:

Val ({CONTHIST.DURATION})
if isnull ({CONTHIST.DURATION}) then &quot;<null>&quot; else if {CONTHIST.DURATION} = &quot;&quot; then &quot;<blank>&quot; else {CONTHIST.DURATION}
if {CONTHIST.DURATION} like &quot;*:*:*&quot; then {CONTHIST.DURATION}

Dave

 

[ms1746]

Ken,

You told donodm that you didn't think he wanted a running total....well I do and I am about to pull my hair out because something so simple is causing me so much frustration!!! Please help!

Here is my situation....

Jan'03 Feb '03 Mar' 03

productname 1 2 3


The above is what I want to see where each month is added to the next month as you scroll across the columns...

However what I am getting is below:

Jan'03 Feb '03 Mar' 03

productname 1 1 1

I feel like I have tried everything....I am sure it is a simple cross-tab thing and I am just not thinking of it!

Any help will be appreciated!

ms1746

 

[kenhamady]

ms17466,

I posted a response in your separate post.


Donodm,

I will have to look in goldmine to determine where the unit of measure for duration is stored. How are you telling minutes from seconds? What other things are being typed in?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[donodm]

Hi Ken,

Sorry for the delayed response--I was out of town for a week. I actually think that I may want the same answer as for ms1746. I need a running total for hours per activity, so we can get the total hours per month spent on an activity. Could you send me the response that you sent to ms1742?

Thanks,
Dave

 

[kenhamady]

see thread767-650334

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[donodm]

Thanks Ken,

I appreciate it.

Dave