Sum (on a Group?) in a 1-2-Many relationship

[brendanj68]

Hi,

Am running CR9/ SQL 2000.

My report is supposed to show all Contracted 'days'(and Actual invoiced days) for a number of consultants. The nature of this data means a 1-2-many relatinship is unavoided. I have shown and summed up the contracted days with no problem, but am unable to determine a way to just sum up the invoiced days (for each consultant). The structure is as follows:
GH1 - Employee Name (Shown)
GH2 - Employee ID (Hidden)
GH3 - Project ID (Hidden)
D - @RecordCount Add (Hidden)
GF3 - Details/ Data (Shown)
GF2 - Summary (Contracted Days)
Summary (Invoiced Days)

I have tried a multiple of solutuions based on other threads - RT's (with/without resets), RT's based on @Invoiced Days), straight-forward summing (having added a Grouping on Invoiced Days) - all it does is 'sum' the first figure (only) for each consultant.

In addition I have tried the creation of a single view (returned the expected multiple data - which did not help). Have now got two seperate views (linked by Employee_Id) which is okay and returns the correct values (and number of entries) for each consultant. My request is what possible other solutions are there? Many thanks in advance (as ever)

B

 

[lbass]

You should provide sample data from the details section for a couple of consultants--copy the details from the group 3 footer if necessary so you can see the duplication. Then it should be fairly straightforward to determine the appropriate evaluation criteria and reset for your running totals.

-LB

 

[brendanj68]

LB

Here's a sample of data:

Employee_Id Employee Start_Date End_Date
0x00000000000001F6 Alasdair Robertson 01/04/2003 30/04/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 30/04/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 30/04/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 30/04/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 30/04/2003
0x00000000000001F6 Alasdair Robertson 01/04/2003 10/05/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 10/05/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 10/05/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 10/05/2003 0x00000000000001F6 Alasdair Robertson 01/04/2003 10/05/2003
0x00000000000001F6 Alasdair Robertson 01/04/2003 30/05/2003

Department Role Project_Code
Solutions Project manager SCN IPRM SFD CAP04
Solutions Project manager SCN IPRM SFD CAP04
Solutions Project manager SCN IPRM SFD CAP04
Solutions Project manager SCN IPRM SFD CAP04
Solutions Project manager SCN IPRM SFD CAP04
Solutions Project manager SCN COPD ARA CAP04
Solutions Project manager SCN COPD ARA CAP04
Solutions Project manager SCN COPD ARA CAP04
Solutions Project manager SCN COPD ARA CAP04
Solutions Project manager SCN COPD ARA CAP04
Solutions Consultant SCN COPD HAC CAP04

Business Charge_Days Project_Id
Consultancy 9.0 0x00000000000002FC
Consultancy 9.0 0x00000000000002FC
Consultancy 9.0 0x00000000000002FC
Consultancy 9.0 0x00000000000002FC
Consultancy 9.0 0x00000000000002FC
Consultancy 3.5 0x000000000000030C
Consultancy 3.5 0x000000000000030C
Consultancy 3.5 0x000000000000030C
Consultancy 3.5 0x000000000000030C
Consultancy 3.5 0x000000000000030C
Consultancy 5.5 0x0000000000000321

Client-Name Actual_Days Date_from Manager
Client A 3.0 01/09/2003 Ruby Dixon
Client A 3.5 01/04/2003 Ruby Dixon
Client A 5.5 01/04/2003 Ruby Dixon
Client A 8.0 18/08/2003 Ruby Dixon
Client A 9.0 01/04/2003 Ruby Dixon
Client B 3.0 01/09/2003 Ruby Dixon
Client B 3.5 01/04/2003 Ruby Dixon
Client B 5.5 01/04/2003 Ruby Dixon
Client B 8.0 18/08/2003 Ruby Dixon
Client B 9.0 01/04/2003 Ruby Dixon
Client C 3.0 01/09/2003 Ruby Dixon......

To try and summarise what is expected:
Alasdair planned to do 9 days consultancy for Client A (and invoiced 9), Client B 3.5 days (invoiced 3.5), Client C 5.5 consultancy (5.5 invoiced). The upshot is that of the 12 projects Alasdair has worked on only 5 have been invoiced so far.

Data held in Details section for this employees invoiced days are:
3.0, 3.5, 5.5, 8.0, 9.0

If this is of assistance then great if not pls let me know

B

 

[lbass]

Not clear whether you want a sum of 3.0, 3.5, 5.5, 8.0, 9.0
or a count (5), but based on a quick look, you should be able to do a running total sum of {table.actualdays}, evaluate for each record, reset on change of {table.clientname}, since it appears that the actual days column is repeating based on this field.

-LB

 

[brendanj68]

LB,

You were right in your assumption I was looking for the Sum (as opposed to Count). Your suggestion was probably the one variable I had not considered i.e. Reset on ClientName. The result is exactly what I was looking for - so once again many thanks.

B