TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR COLUMNS AUDI AND DATSUN AND JAGUAR AND PEUGEOT
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END

quote:

Originally posted by Doug:
Or, are you talking about MISSING data / values?

quote:

I want the workcenter to show up even if no one has charged any hours in the time frame prompted.

 
DEFINE FILE.....
BEND_SHOP/I9=IF PLANT EQ 'BEND SHOP' THEN HOURS ELSE 0;
WELD_SHOP/I9=IF PLANT EQ 'WELD SHOP' THEN HOURS ELSE 0;
.
.
.
TABLE FILE....
SUM BEND_SHOP WELD_SHOP......
BY JOB
END  

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Capuccino';
ON TABLE HOLD AS TMP1
END
-RUN

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Espresso';
ON TABLE HOLD AS TMP2
END
-RUN

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Latte';
ON TABLE HOLD AS TMP3
END
-RUN

TABLE FILE GGSALES
SUM COMPUTE DOL /I8 = 0;
    COMPUTE BDG /I8 = 0;
BY REGION
BY COMPUTE PRODUCT /A16 = 'Regular';
ON TABLE HOLD AS TMP4
END
-RUN

TABLE FILE TMP1
SUM DOL
    BDG
BY REGION
BY PRODUCT
ON TABLE HOLD AS BUCKETFIL
MORE
FILE TMP2
MORE
FILE TMP3
MORE
FILE TMP4
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS    AS 'DOL'
    BUDDOLLARS AS 'BDG'
BY REGION
BY PRODUCT
WHERE CATEGORY EQ 'Coffee';
ON TABLE HOLD AS EXTDATA
END
-RUN

TABLE FILE BUCKETFIL
SUM DOL
    BDG
BY REGION
BY PRODUCT
ON TABLE HOLD AS RPTDATA
MORE
FILE EXTDATA
END
-RUN

TABLE FILE RPTDATA
SUM DOL
    BDG
BY REGION
ACROSS PRODUCT
END
-RUN

</>

-*COMPONENT=Join_J001
JOIN MO_HDR.MO_HDR.MO_ID IN MO_HDR
TO UNIQUE MO_ROUTING.MO_ROUTING.MO_ID IN MO_ROUTING TAG J001 AS J001
END
-*COMPONENT=Join_J002
JOIN LEFT_OUTER J001.MO_ROUTING.MO_ID AND J001.MO_ROUTING.WC_ID AND J001.MO_ROUTING.MO_OPER_SEQ_NO IN MO_HDR
TO UNIQUE TS_LN_MO.TS_LN_MO.MO_ID AND TS_LN_MO.TS_LN_MO.WC_ID AND TS_LN_MO.TS_LN_MO.MO_OPER_SEQ_NO IN TS_LN_MO TAG J002 AS J002
END
-*COMPONENT=Define_MO_HDR
DEFINE FILE MO_HDR
 Define_1/A16=IF (J001.MO_ROUTING.WC_ID EQ '04-HARP' AND   J001.MO_ROUTING.MO_OPER_SEQ_NO EQ 460) THEN '03.92-SOCKET' ELSE  IF (J001.MO_ROUTING.WC_ID EQ '04-HARP' AND   J001.MO_ROUTING.MO_OPER_SEQ_NO EQ 450) THEN '03.91-HDR WELD' ELSE  IF (J001.MO_ROUTING.WC_ID EQ '04-HARP' AND   J001.MO_ROUTING.MO_OPER_SEQ_NO IN (330, 340, 350)) THEN '03.9-SF' ELSE IF J001.MO_ROUTING.WC_ID EQ '04-HARP' THEN '04-HARP' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0460' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0450' THEN '02-WELD SHOP' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0110' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0530' THEN '19.0 OVR MACH' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0540' THEN '19.1 REPAIR' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0120' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0105' THEN '01.1 JOINT' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0140' THEN '01.3 FIT_TACK' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0210' THEN '02.1 TUBE CLN' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0230' THEN '02.2 FIN WELD' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0250' THEN '02.3 PNL WLD' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0700' THEN 'Z-ENG' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0710' THEN 'Z-ENG' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '2640' THEN 'Z-ENG' ELSE IF J001.MO_ROUTING.MO_OPER_SEQ_NO EQ '0710' THEN 'Z-ENG' ELSE J001.MO_ROUTING.WC_ID;
 ACTUAL_HOURS/D12.2C=IF J002.TS_LN_MO.CHG_HRS IS MISSING THEN 0 ELSE J002.TS_LN_MO.CHG_HRS ;
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE MO_HDR
SUM ACTUAL_HOURS
BY J001.MO_ROUTING.MO_ID
ACROSS Define_1
WHERE J002.TS_LN_MO.TS_DT GE DT('&TS_DT_BEG.TS_DT: BEG DATE.') AND J002.TS_LN_MO.TS_DT LE DT('&TS_DT_END.TS_DT: END DATE.');
WHERE MO_HDR.MO_HDR.MO_ID LIKE 'B2%' OR 'B5%' OR 'B0%' OR 'B9%';
ON TABLE PCHOLD FORMAT EXL07 FORMULA
ON TABLE ROW-TOTAL
ON TABLE RECOMPUTE 
ON TABLE SET CACHELINES 100
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/combine_templates/ENSilver_Light2.sty,$
TYPE=REPORT, FONT='HELVETICA', SIZE=11, COLOR=RGB(0 0 0), STYLE=NORMAL, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=OFF, BACKCOLOR=(RGB(255 255 255) RGB(255 255 255)), $
TYPE=DATA, FONT='HELVETICA', SIZE=11, COLOR=RGB(0 0 0), STYLE=NORMAL, BACKCOLOR=(RGB(255 255 255) RGB(255 255 255)), $
TYPE=TITLE, FONT='HELVETICA', SIZE=11, COLOR=RGB(0 0 0), STYLE=NORMAL, BACKCOLOR=RGB(255 255 255), $
ENDSTYLE
END

-RUN

quote:

Originally posted by MartinY:
Hi Pops,

As I mentioned above, the two important thing are :
1- "from the selected period, it doesn't extract any data for WELD SHOP because no data exist" {The data [the workcenter] exists in the MO_Router even if there is not a timesheet}

2- "you need to force each "categories" to be displayed no matter what" {That was my question from the start....}

Unfortunately, we're not here to do the code for you. {I've never asked you to do the code for me, I just asked for help. Thought that was what this forum was for? Maybe I was wrong there, too.}

So you have to understand the three sample that we gave you and apply one of them to your situation. {If I could apply them, I surely would... Sorry to have bothered you!}

What you have so far it's the data extract (the step where in my sample I HOLD it as EXTDATA).
From there you need to use one of the method to create the "empty" buckets to result in what you want.

Regards,

quote:

The data [the workcenter] exists in the MO_Router even if there is not a timesheet

quote:

That was my question from the start....}

quote:

I just asked for help

quote:

Sorry to have bothered you

quote:

Originally posted by Tom Flynn:
Hey Pops,
Try this and see if your work station is part of the output:

JOIN LEFT_OUTER MO_HDR.MO_HDR.MO_ID IN MO_HDR
TO UNIQUE MO_ROUTING.MO_ROUTING.MO_ID IN MO_ROUTING TAG J001 AS J001
END
TABLE FILE MO_HDR
PRINT *
ON TABLE PCHOLD FORMAT EXL07 or EXL2K or XLSX <---- WHATEVER EXCEL OUTPUT WORKS FOR YOU HERE
WHERE MO_ID LIKE 'B2%' OR 'B5%' OR 'B0%' OR 'B9%';
END
-EXIT

quote:

Originally posted by jfr99:
This might work for you. Here's an example using the CAR file.

TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END
-*
TABLE FILE CAR
SUM RETAIL_COST
ACROSS CAR COLUMNS AUDI AND DATSUN AND JAGUAR AND PEUGEOT
BY COUNTRY
WHERE COUNTRY IN ('ENGLAND','FRANCE','ITALY','JAPAN')
END

The third report's ACROSS statement displays 4 specific CAR columns regardless if any values or not.

This might work for you if you know all the workcenter values you want.

 	01-BEND SHOP	01.1 JOINT	01.3 FIT_TACK	02-WELD SHOP	02.1 TUBE CLN	02.2 FIN WELD
MO ID						
B00043-01	.	40.00	.	.	.	.
B00043-02	.	.	.	.	.	.
B00043-03	8.00	.	.	.	.	.
B00043-08	.	.	.	.	20.00	.
B00045-01	84.50	.	.	10.00	.	.
B00047-01	40.00	.	.	.	.	.
B00047-02	.	.	.	.	8.00	.
B51076-01	8.00	.	.	.	.	.
B51083-01	12.00	.	.	.	.	.
B90124-01	.	.	.	.	.	.
TOTAL	152.50	40.00	.00	10.00	28.00	.00