Summarizing between groups.

[tricer]

I have a report which looks like this

Department1
Teacherid1 Class1 Sec1 credithours1
Teacherid1 Class2 Sec2 credithours2

Teacherid2 Class3 Sec1 credithours3
........

Department2
Teacherid1 Class9 Sec1 credithours9
.....

I want(have been mandated) to to be able to show another column that shows how many hours total the teacher is teaching in *every* department in each details section.
The new line would look something like so..
Teacherid1 Class1 Sec1 credithours1 "out of 28 total"

For the life of me I can't seem to get crystal to sum across the groups for each teacher (since teachers show up in different depts) Eventually that number will go into a formula to do salary calculations on.

The only way I have figured it out so far is to create a subreport, group by teacher id, sum and then share that back to the main report.... but that takes so long it makes the report basically unusable.

Any suggestions?

 

[synapsevampire]

Try including technical information when requesting it:

Crystal version
Database and connectivity used
Example data
Expected output

In this case, your assessment and approach my be the best one for your environment, but since you didn't share your environment, it's difficult to advise you.

Recent versions of Crystal, and even older ones have means by which you might use a nested query to return this information which would then be processed on the database, rather than within Crystal, or as multiple selects from Crystal.

CR 9 and above, use the Add Command.
CR 8, 8.5 and below, use either an ADO connection and paste in the SQL, or use a SQL Expression which would be the subquery.

The real solution would be to create a View or Query on the database side to handle this.

-k

 

[tricer]

Sorry.... And thanks so far. I have thought about doing on the server side, but the query looks so bad I hadn't thought to hard about it.

As for my info...

Crystal 9 (I have 10 available)
MySQL and ODBC


I sped the report up greatly by altering the select statement in the subreport to just limit itself to the created parameter field. {pm-per_course.personnellID} This made the report quite spiffy and quick. I am going to continue playing with it though, and I am fairly certain there at least *should* be a way to summarize data in more than one way (i.e. across groups) as opposed to
only being able to do a sum within groups.