crosstab report to calculate average job time by complexity

[Rockytop62]

I'm using CR 11.0
I have record data fields as follows:

JobRefNo
JobComplexity
Department
JobHours

The JobComplexity field is a dropdown with 5 options
My crosstab report has the 5 JobComplexity options as Columns. The Departments are the rows.
I need to calculate JobHours/Distinctcount(JobComplexity) per department.. So if Department A did 5 jobs at "simple" level of JobComplexity and consumed a total of 25 hours for those 5 jobs, the cell entry would be 5 in the "Simple" column for Department A.

Any one have an idea of how to create a cell formula capable of this? Thanks!

 

[Madawc]

Sorry, what's the problem? If your crosstab already shows the number of jobs, use the Crosstab Expert to also show the sum of hours.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Rockytop62]

I have a crosstab report that shows the SUM of the hours... and I have a crosstab reports that shows the job count at each level of complexity.. I'm trying to figure out how to set up a cell formula that divides hours by job count... Columns are Job Complexity levels.. Rows are departments.. cells = Total hours / job count.

 

[lbass]

What results do you get if you use "average" as the summary on the Hours field?

-LB

 

[Madawc]

I'm trying to figure out how to set up a cell formula that divides hours by job count

I don't think a crosstab can do that. It sums up values found at detail-line level. You'd need to define those totals yourself, separately form the crosstab.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Rockytop62]

Finally figured it out... Requires the use of an Embedded Summary Calculation in the CrossTab report. For any others looking for a solution to this problem, you must have CR 2008 or later.. From that point, my solution turned out to be rather straight forward.. In my case: Create the Crosstab report with both numbers - SUM(Hours) and COUNT(Jobs).. Then create an embedded summary with GRIDVALUEAT(CURRENTROWINDEX,CURRENTCOLUMNINDEX,0)/GRIDVALUEAT(CURRENTROWINDEX,CURRENTCOLUMNINDEX,1) as the third element in the cell.