Getting sum at second level grouping 1

[infinitizon]

Hi guys,

I'm at a fix currently,

I have a database that looks like this

No_	      Document No_	Job desc	    Job Category	 COGS	   	Amount	  GP
JCE26506	PSIV39095       MATERIALS USED	     Field Service	741360.66    448354.46    706993.80
JCE26506	PSIV39095	LABOUR	 	     Field Service	741360.66    51875.29     -689485.37
JCE26506	PSIV39095	TRANSPORTATION	     Field Service	741360.66    5000.00      -736360.66
JCE26359	PSIV39000	MATERIALS USED	     Contract	        35741.21     360284.00    324542.79
JCE26359	PSIV39000	LABOUR	             Contract	        35741.21     26000.00     -9741.21
JCE26359	PSIV39000	TRANSPORTATION	     Contract	        35741.21     5500.00      -30241.21

I want the report to be grouped by No_ and then by Job Category while picking only the distinct COGS.

To achieve this, I grouped first by No_, then by Job Category. I now did an average for COGS. So that at the group by No_. I now have something like this

No_	      Document No_	 COGS	   	Amount	    GP
[b]Field Service[/b]
JCE26506	PSIV39095     	741360.66     505229.75    -236130.91
JCE26507        PSIV39097       64136.66     201229.75     137093.09
[b]Contract[/b]
JCE26359	PSIV39000	35741.21      391784.00    356042.79
JCE26360	PSIV39003	34551.21      401784.00    367232.79

Note: GP = Amount - COGS
COGS is the distinct value(I used average to get that)
The challenge is when I want to get the sum at the Job Category level so that I have some thing like this

No_	      Document No_	 COGS	   	Amount	    GP
[b]Field Service                805497.32     706459.5     -99037.82[/b]
JCE26506	PSIV39095     	741360.66     505229.75    -236130.91
JCE26507        PSIV39097       64136.66      201229.75     137093.09
[b]Contract                     70292.42      793568.00    723275.58[/b]
JCE26359	PSIV39000	35741.21      391784.00    356042.79
JCE26360	PSIV39003	34551.21      401784.00    367232.79

Any ideas?

Thanks in advance...


____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[infinitizon]

P.S.: I'm using Crystal Report 8

 

[IanWaterman]

You will have to replace your Avg for COGS with a running total. When setting up the RT select evaluate on change of Job Category and reset on change of No_

You will have to move your summaries underneath data as RTs are while printing formula.

In the No_ group footer add a formula

@GP
{#GOGS RT} - sum({AmountField}, {no_field})

Ian

 

[infinitizon]

This works fine when calculating for group by No_. The challenge is that I was not able to get the summary for Job Category.

I'd appreciate further support.

Thanks

 

[IanWaterman]

Add a group by Job category and do the same thing, you will require a 2nd Running Total this time resetting and evaluating on change of Job Cat

@GP_JC
{#GOGS RT_JC} - sum({AmountField}, {JobCategory})

Ian

 

[infinitizon]

Thanks Ian,

I see where the problem is now. I was trying to place the running total field at the top of the Job Category grouping and was therefore getting a wrong value. I just placed it at the bottom and now I'm getting the accurate values

Your input was valuable though.

Thanks.