I’m trying to create a new line a subfoot to recalculate productivity by using recap, I’m not getting an error but it does not print either
Any suggestions? This is my first attempt using recap we are running on 7.7.01



-* File test_reacap1.fex
ENGINE SQLORA SET DEFAULT_CONNECTION prodmfg
SQL SQLORA PREPARE SQLOUT FOR
select department, sum(esh) as esh, sum(dl) as dl, sum(idl) as idl,
sum(dl) + sum(idl) total_hrs,
case when sum(esh) <> 0 and sum(dl) <> 0 then
sum(esh) / sum(dl) * 100 else 0
end as efficiency,
case when ((sum(esh) <> 0) and (sum(dl) + sum(idl) <> 0)) then
sum(esh) / (sum(dl) + sum(idl)) * 100 else 0
end as productivity
from
( (select a. department,
case when a.labourtype <= '19' then
a.elapsedhours else 0
end as dl,
case when a.labourtype > '19' then
a.elapsedhours else 0
end as idl,
0 as esh
from timecardentries a, departments_2 d
where a.shiftdate = trunc(sysdate)
and rtrim(a.department) = rtrim(d.dept_code)
and d.plant_number in ('2','3')
and a.elapsedhours > 0
and a.employeeno > '0000'
and a.employeeno <= '99999'
and a.department in ('012','015','016','017','019','024','036','043','050','052','055','057','063','065','070','071','073','076','077','080','081','260','812','815','816','817','826','841','845','851','857','860','861','862','863','895','896','897')
union all
select a.department,
case when a.labourtype <= '19' then
a.elapsedhours else 0
end as dl,
case when a.labourtype > '19' then
a.elapsedhours else 0
end as idl,
0 as esh
from timecardentries_mirror a, departments_2 d
where a.shiftdate = trunc(sysdate)
and rtrim(a.department) = rtrim(d.dept_code)
and d.plant_number in ('2','3')
and a.elapsedhours > 0
and a.employeeno > '0000'
and a.employeeno <= '99999'
and a.department in ('012','015','016','017','019','024','036','043','050','052','055','057','063','065','070','071','073','076','077','080','081','260','812','815','816','817','826','841','845','851','857','860','861','862','863','895','896','897'))
union all
select tp1.dept_code as department, 0 as dl, 0 as idl, SUM(tp1.earned_dept_hrs / 10000) as earned_dept_hrs
from visib.whse_thruput_rec tp1
where tp1.datecompleted = trunc(sysdate)
and tp1.plant_number in ('2','3')
and tp1.dept_code in ('012','015','016','017','019','024','036','043','050','052','055','057','063','065','070','071','073','076','077','080','081','260','812','815','816','817','826','841','845','851','857','860','861','862','863','895','896','897')
group by tp1.plant_number, tp1.dept_code
)
group by department;
END
TABLE FILE SQLOUT
PRINT
DEPARTMENT
ESH
DL
IDL
TOTAL_HRS
EFFICIENCY/D20.2%
PRODUCTIVITY/D20.2%
BY DEPARTMENT NOPRINT
HEADING
"PCC Structurals, Inc."
"Large Parts Campus"
"Daily Manufacturing Summary Data"
"&DATEMDYY <+0> &TOD"
ON TABLE RECAP
PRODUCTIVITY/D20.2% = ESH / TOTAL_HRS * 100;
ON TABLE SUBFOOT "TOTALS"
""
ON TABLE SUMMARIZE ESH DL IDL TOTAL_HRS EFFICIENCY PRODUCTIVITY AS ' LPC TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
END

This message has been edited. Last edited by: Kerry,

The RECAP-ed fields will either:
- print automaticaly with some ugly lead, or
- print in the accompanying subfoot, but only if you mention the field(s).
Since you have a subfoot in place, the fields will not print automatically, but since you did not mention the field in the subfoot either, the field will not show. You should code something like this:

...
ON TABLE RECAP
PRODUCTIVITY/D20.2% = ESH / TOTAL_HRS * 100;
ON TABLE SUBFOOT "TOTALS"
"PRODUCTIVITY <PRODUCTIVITY"
...

Hope this helps ...

when I added the requested fields "PRODUCTIVITY I got print but now my problem is the subfooting data is the same as my last record..what I'm I missing? Thanks for all and any suggestions

data to follow
896 4.13 5.90 4.76 10.66 69.97% 38.72%
897 2.89 1.60 1.55 3.15 180.31% 91.59% ******* here
LPC TOTAL
312.24 459.97 240.90 700.87 1,995.41% 884.32%
TOTALS
PRODUCTIVITY 91.59 *** and here
EFFICIENCY 180.31 ***


code to follow


TABLE FILE SQLOUT
PRINT
DEPARTMENT
ESH
DL
IDL
TOTAL_HRS
TOT.DL NOPRINT
TOT.IDL NOPRINT
TOT.ESH NOPRINT
EFFICIENCY/D20.2%
PRODUCTIVITY/D20.2%
BY DEPARTMENT NOPRINT
HEADING
"PCC Structurals, Inc."
"Large Parts Campus"
"Daily Manufacturing Summary Data"
"&DATEMDYY <+0> &TOD"
ON TABLE RECAP
PRODUCTIVITY/D20.2% = TOT.ESH / TOT.DL + TOT.IDL * 100;
EFFICIENCY/D20% = TOT.ESH / TOT.DL * 100;
ON TABLE SUBFOOT "TOTALS"
"PRODUCTIVITY "EFFICIENCY ON TABLE SUMMARIZE ESH DL IDL TOTAL_HRS EFFICIENCY PRODUCTIVITY AS ' LPC TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
END

WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF

I just now see that you also have a real field called PRODUCTIVITY.
When working with recaps I'd take care that my recap fields have a unique name.
So:

TABLE FILE SQLOUT
PRINT DEPARTMENT ESH DL IDL TOTAL_HRS
TOT.DL NOPRINT TOT.IDL NOPRINT TOT.ESH NOPRINT
EFFICIENCY/D20.2%
PRODUCTIVITY/D20.2%
BY DEPARTMENT NOPRINT
HEADING
"PCC Structurals, Inc."
"Large Parts Campus"
"Daily Manufacturing Summary Data"
"&DATEMDYY <+0> &TOD"
ON TABLE RECAP
R_PROD/D20.2% = TOT.ESH / TOT.DL + TOT.IDL * 100;
R_EFFI/D20%   = TOT.ESH / TOT.DL * 100;
ON TABLE SUBFOOT "TOTALS"
"PRODUCTIVITY  <R_PROD"
"EFFICIENCY <R_EFFI"
ON TABLE SUMMARIZE ESH DL IDL TOTAL_HRS EFFICIENCY PRODUCTIVITY AS ' LPC TOTAL'
END

Does this help?

Something is clearing the tot.* fields because both the productivity and efficiency are 0
If I take the noprint off the tot.dl , idl and esh they indeed have amounts but on the subfoot totals the productivity and efficiency are 0

Actually, you do not need the tot fields. Recap automaticaaly takes the total of the level where the recap is defined. Consider the following code (without TOT's):

TABLE FILE CAR
PRINT CAR SEATS DCOST RCOST
BY COUNTRY SUBTOTAL
ON TABLE RECAP
R_PROD/D20.2% = (DCOST / RCOST) * 100;
R_EFFI/D20%   = (RCOST / SEATS);
END

It produces correct recaps.

Thanks for all the help I got it to work

That great!

Anything in particular that did it?
Please edit your first post to have the caption say [SOLVED].

REsolved