Need help with a model

[Nick Osdale-Popa]

I have a report that is layed out similar to this:
JOB: NNN.000
PHASE: 01
COST CODE: AAAA
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 8.00 2.00 0.00
_
* BBBB
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 8.00 2.00 0.00
_
JOB: NNN.000
PHASE: 01
COST CODE: BBBB
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 16.00 4.00 0.00
_
JOB: NNN.000
PHASE: 01
COST CODE: BBBB
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 8.00 2.00 0.00
_
JOB: NNN.000
PHASE: 01
COST CODE: CCCC
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 4.00 0.00
02/20/02 8.00 4.00 0.00
------- ------- -------
EMP TOTAL 8.00 8.00 0.00
_
A new page always produces the Job/Phase/Cost Code.
The "*" designates that the Cost Code has changed in the middle of the page. When a new Phase comes in to play the report will produce this:
PHASE: 02
_
* AAAA
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 8.00 0.00
02/20/02 8.00 8.00 0.00
------- ------- -------
EMP TOTAL 8.00 16.00 0.00I need to produce a table like so:
JOB PH CC WEEKENDING OVT HRS DBL HRS
NNN.000 01 AAAA 2/15/2002 2.00 0.00
NNN.000 01 BBBB 2/15/2002 2.00 0.00
NNN.000 01 BBBB 2/15/2002 4.00 0.00
NNN.000 01 BBBB 2/15/2002 2.00 0.00
NNN.000 01 CCCC 2/15/2002 8.00 0.00
NNN.000 02 AAAA 2/15/2002 16.00 0.00I'm having difficulty matching up the correct Cost Codes and Phases. Any help would be appreciated.

[Grant Perkins]

Have you considered a 'double extract' route? It's a bit difficult to be sure it will work (I always like to have a full sample report available, program(mer)s can so often find 'interestingly inconsistent' way of reporting data!

One idea might be to perform an initial extract on, say, the data which can be identified and correlated to the "*" ID changes only. Output these to a file that you can link to as a database in stage 2.

The stage 2 model would select the rest of records AND read the stage 1 file as a database and combine the records sorting, etc. as it goes.

Just an idea but I hope it helps in some way. I have used a similar principle but I may have been luckier with the data relationships available to me.

Grant

Originally posted by Nick Osdale-Popa:
I have a report that is layed out similar to this:
JOB: NNN.000
PHASE: 01
COST CODE: AAAA
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 8.00 2.00 0.00
_
* BBBB
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 8.00 2.00 0.00
_
JOB: NNN.000
PHASE: 01
COST CODE: BBBB
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 16.00 4.00 0.00
_
JOB: NNN.000
PHASE: 01
COST CODE: BBBB
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 2.00 0.00
------- ------- -------
EMP TOTAL 8.00 2.00 0.00
_
JOB: NNN.000
PHASE: 01
COST CODE: CCCC
_
PR PC 02/15/02 596.22 10.00 PAYROLL RUN
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 4.00 0.00
02/20/02 8.00 4.00 0.00
------- ------- -------
EMP TOTAL 8.00 8.00 0.00
_
A new page always produces the Job/Phase/Cost Code.
The "*" designates that the Cost Code has changed in the middle of the page. When a new Phase comes in to play the report will produce this:
PHASE: 02
_
* AAAA
_
EMP # CHK DATE REG HRS OVT HRS DBL HRS
02/20/02 8.00 8.00 0.00
02/20/02 8.00 8.00 0.00
------- ------- -------
EMP TOTAL 8.00 16.00 0.00I need to produce a table like so:
JOB PH CC WEEKENDING OVT HRS DBL HRS
NNN.000 01 AAAA 2/15/2002 2.00 0.00
NNN.000 01 BBBB 2/15/2002 2.00 0.00
NNN.000 01 BBBB 2/15/2002 4.00 0.00
NNN.000 01 BBBB 2/15/2002 2.00 0.00
NNN.000 01 CCCC 2/15/2002 8.00 0.00
NNN.000 02 AAAA 2/15/2002 16.00 0.00I'm having difficulty matching up the correct Cost Codes and Phases. Any help would be appreciated.

[Nick Osdale-Popa]

Thanks for the idea. If this was just for me, I'd probably try that way, but it's for another user who only knows how to apply a model (usually one's that I've created) to a report.

If this can't be accomplished with just one model, he can run the report by individual Cost Codes and then apply a more easier model, granted it takes longer this way.

[Joseph Palhofski]

Create you detail template for the EMP TOTAL line.

Select the COST CODE line, the next blank line and the PR PC line for the 1st append line. Select template/new and create the trap with the PR PC line. Define the cost code field (cost1) and the date field. On the PR PC line,define the location where the new cost code is (cost2), it should start with the letter T in COST and end with the letter O in CODE. Save your 1st append template.

Create a 2nd append template for your JOB # and PHASE # and save your 2nd append template.

Select Window/Table and then select DATA/CALCULATED FIELDS. Select NEW/FORMULA BASED and OK. Under the general tab enter the field name (cost) TYPE=CHARACTER FORMAT=GENERAL DISPLAY WIDTH=4 DATA LENGTH=4. Click on FORMULA. Use the following expression:
If(cost1>" ",cost1,cost2)
Select OK & OK

Cost should now have the value of cost1 if cost1 is not blank or the value of cost2 if cost1 is blank. You can hide cost1 and cost2.

[Grant Perkins]

Nick,

There are some specifics about your question, particularly the format of the report, that offer possible challenges. I have sent you a Private Message as I felt it inappropriate to open post something that asked more questions than it attempted to provide answers for. If it applies for your problem I will condense it and post later but would like your input first. It could be way off track.

Grant