Sum a numerical field by another field.

[rlporter]

I have a table with the following fields:

Code - 3 digit code describing a certain job
HowMany - number of widgets completed for above code
HowLong - time it took to complete, in minutes

I wish the result to look like this:

Code...HowMany....HowLong
100....10.........20
120....3..........60 , etc.

If the employee has more than 1 code in the time period, the result is:

Code...HowMany...HowLong
100....2.........4
100....4.........8
100....3.........6
100....1.........2
120....1.........20
120....1.........20
120....1.........20

I have to sum the numbers by each code and show the average time to complete each code for the employee and then show the average time for all employees for each code.

Thanks for your help.

rlporter

 

[dgillz]

Right click the HowLong field and select insert, summary. Make it an average summary and group by the Code field. No formulas required.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rlporter]

Thanks for your help, but it isn't quite what I want to do. I only want one line for each code. If there are 10 lines with code 100, I want the sum of HowMany and HowLong for all 10 lines to show on 1 line for each code.

 

[dgillz]

Yes, what I said to do will accomplish this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rlporter]

I am obviously doing something wrong. I have only had Crystal Reports 9 for about a week. Sorry for the trouble.

When I right click on the HowMany and click insert summary, a dialog box opens. There are 3 fields not greyed out.

* Choose the field to summarize
I select HowMany

* Calculate this summary
Using the drop down list, I choose Average

* Summary location
Has 2 choices, GrandTotal (Report Footer) and Group#1:RecordsStats.Code-A

I chose Group#1:RecordsStats.Code-A.

When I run the report there is 8 rows totaling 81. The average in the field I created is 10.13

See I told you I was doing something wrong.

 

[dgillz]

I don't havbe crystal reports v9, but in v8.5, when you choose summary, it will default to an existing group, such as in your case Group#1:RecordsStats.Code-A. However at that point you can ignore this and choose any database field. Try this and choose the "Code" field that you mentioned.


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rlporter]

There doesn't seem to be any way to choose any field but the default. You can move the it around in the design mode, but only to the footers.

Maybe someone else that has version 9 will know. Thanks anyway.

 

[dgillz]

When you right click on the HowMany field, what are your options?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rlporter]

The following are the options:

Field:RecordsStats1.HowMany
***************************
Format Field
Highlighting Expert
***************************
Browse Field Data
Select Expert
***************************
Insert
Summary
Running Total
Move
Backward
To Back
Forward
To Front
Size and Position
***************************
Cut
Copy
Delete
***************************
Cancel Menu

 

[dgillz]

You are choosing Insert->Summary correct?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rlporter]

Yes, Insert / Summary with this result:

When I right click on the HowMany and click insert summary, a dialog box opens. There are 3 fields not greyed out.

* Choose the field to summarize
I select HowMany

* Calculate this summary
Using the drop down list, I choose Average

* Summary location
Has 2 choices, GrandTotal (Report Footer) and Group#1:RecordsStats.Code-A

I chose Group#1:RecordsStats.Code-A.

When I run the report there is 8 rows totaling 81. The average in the field I created is 10.13

 

[lbass]

To suppress the detail section, go to format->section->details->suppress. This will give you one line per code.

-LB

 

[rlporter]

This didn't work by itself. I finally hit on it, mostly because the help I received here. I had to do a summary of the group code and then suppress the details. I moved the summaries to the group header and everything shows up on one line.

Thanks for all your help..