Hi All,
i got a problem with sorting order, i am using Across, but no luck

here is the code I am using, ONE is Start time

TABLE FILE H_TSFCT2
SUM
STARTIN AS 'IN'
ENDIN   AS 'OUT'
TOT_HRS AS 'HOURS'
ACROSS ONE NOPRINT
BY ORG_LVL05_NAM        
BY ORG_LVL06_NAM         
BY WORK_LOC              
BY EMPLID                
BY NAME                  
BY JOB_C_DESC_T         
BY STARTDATE            
END
 

MY OUTPUT
SHIFT START DATE----IN----OUT- --HOURS----IN-------OUT ---HOURS---- IN-----OUT-----HOURS
08/12/2013 --------------------------------------------------------------------- --11:00---15:00--- 4.00
08/13/2013----------- 08:00---16:00-- 8.00
08/14/2013---------------------------------------- 09:00---13:00----4.00
08/15/2013 ---------------------------------------------------------------------- 10:00----14:3----- 04.50
08/17/2013----------- 08:00---12:00---4.00



I want to eliminate empty cells, Like my output should come with 11:00 in first "IN" column and 15:00 and 4.00 in first “OUT” and “HOURS” columns … Vice versa
I added lines in between because space is not taking in the forms

My output format is Excel
Please help me on this,
Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,

It would have helped if you would also have printed the value of ONE.

Apparently it has a different value for each column, or it would all go in one column.

P.S. Don't code-tags work for your output table?

Thanks for reply,

MY OUTPUT
------------------------ ONE
-------------------------08:00---------------------09:00------------------------- 10:00---------------------------11:00
SHIFT START DATE----IN----OUT- --HOURS----IN-------OUT ---HOURS---- IN-----OUT-----HOURS ---- IN-----OUT-----HOURS
08/12/2013 ---------------------------------------------------------------------------------------------------------11:00---15:00--- 4.00
08/13/2013----------- 08:00---16:00-- 8.00
08/14/2013---------------------------------------- 09:00---9:30----0.30------------------------------------------11:00----12:30---1:30
08/15/2013 ---------------------------------------------------------------------- 10:00----14:3----- 04.50
08/17/2013----------- 08:00---12:00---4.00

If I skip Across and if i do sum with BY i am getting 2 rows for some of the dates that has data (08/14/2013), but i want to display "IN", "OUT", "HOURS" in a single for each date in Multiple columns without empty columns as this is Excel output.
Across is doing fine but with empty fields that has no data, which I don’t want
Thanks,

Compute, for each set of data to appear in the report, the relative position (1, 2, ...) at which it should appear within its line of the report.

Then report "ACROSS relative_position" (in place of "ACROSS ONE"), and everything should fall in place.

TABLE FILE H_TSFCT2
SUM
STARTIN
ENDIN
TOT_HRS
BY ORG_LVL05_NAM
BY ORG_LVL06_NAM
BY WORK_LOC
BY EMPLID
BY NAME
BY JOB_C_DESC_T
BY STARTDATE
BY ONE
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
PRINT * AND COMPUTE
RELPOS/I2 =
 IF (ORG_LVL05_NAM  EQ LAST ORG_LVL05_NAM)
AND (ORG_LVL06_NAM  EQ LAST ORG_LVL06_NAM)
AND (WORK_LOC       EQ LAST WORK_LOC     )
AND (EMPLID         EQ LAST EMPLID       )
AND (NAME           EQ LAST NAME         )
AND (JOB_C_DESC_T   EQ LAST JOB_C_DESC_T )
AND (STARTDATE      EQ LAST STARTDATE    )
THEN LAST RELPOS + 1 
ELSE 1;
ON TABLE HOLD AS HOLD2
END

TABLE FILE HOLD2
SUM
STARTIN AS 'IN'
ENDIN   AS 'OUT'
TOT_HRS AS 'HOURS'
BY ORG_LVL05_NAM
BY ORG_LVL06_NAM
BY WORK_LOC
BY EMPLID
BY NAME
BY JOB_C_DESC_T
BY STARTDATE
ACROSS RELPOS NOPRINT
END

Hi J,

Thank you for your response, it is working fine, i want to add 3 more fields to that, when i try to do that, I am getting error

(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:

here are the fields i want to add

 
DEFINE FILE 
STARTINH/A2 = IF J7.STRT_PNCHEVNT_DTM IS MISSING THEN '' ELSE HNAME(STRT_PNCHEVNT_DTM, 'HOUR' ,STARTINH);
STARTINM/A2 = IF J7.STRT_PNCHEVNT_DTM IS MISSING THEN '' ELSE HNAME(STRT_PNCHEVNT_DTM,'MINUTE',STARTINM);
STARTIN/A7 = IF STRT_PUNCH_TIME EQ '' THEN 'NOPUNCH' ELSE STARTINH || ':' || STARTINM ;
ENDINH/A2 = IF J7.END_PNCHEVNT_DTM IS MISSING THEN '' ELSE  HNAME(END_PNCHEVNT_DTM, 'HOUR' ,ENDINH);
ENDINM/A2 = IF J7.END_PNCHEVNT_DTM IS MISSING THEN '' ELSE  HNAME(END_PNCHEVNT_DTM,'MINUTE',ENDINM);
ENDIN/A7 = IF END_PUNCH_TIME EQ '' THEN 'NOPUNCH' ELSE ENDINH || ':' || ENDINM ;
WORK1_LENGTH/D12.2 = HDIFF(END_PNCHEVNT_DTM, STRT_PNCHEVNT_DTM, 'MINUTE', 'D12.2') ;
END
TABLE FILE H_TSFCT2
SUM
STARTIN
ENDIN
TOT_HRS
BY ORG_LVL05_NAM
BY ORG_LVL06_NAM
BY WORK_LOC
BY EMPLID
BY NAME
BY JOB_C_DESC_T
BY STARTDATE
BY ONE
BY FST.STARTIN AS 'STARTO'
BY LST.ENDIN   AS ENDO'
BY WORK1_LENGTH_HOR AS 'LENGTH'
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
PRINT * AND COMPUTE
RELPOS/I2 =
 IF (ORG_LVL05_NAM  EQ LAST ORG_LVL05_NAM)
AND (ORG_LVL06_NAM  EQ LAST ORG_LVL06_NAM)
AND (WORK_LOC       EQ LAST WORK_LOC     )
AND (EMPLID         EQ LAST EMPLID       )
AND (NAME           EQ LAST NAME         )
AND (JOB_C_DESC_T   EQ LAST JOB_C_DESC_T )
AND (STARTDATE      EQ LAST STARTDATE    )
AND (STARTO         EQ LAST STARTO)
AND (ENDO           EQ LAST ENDO)
AND (LENGTH         EQ LENGTH)
THEN LAST RELPOS + 1 
ELSE 1;
ON TABLE HOLD AS HOLD2
END

TABLE FILE HOLD2
SUM
STARTIN AS 'IN'
ENDIN   AS 'OUT'
TOT_HRS AS 'HOURS'
BY ORG_LVL05_NAM
BY ORG_LVL06_NAM
BY WORK_LOC
BY EMPLID
BY NAME
BY JOB_C_DESC_T
BY STARTDATE
BY STARTO
BY ENDO
BY LENGTH
ACROSS RELPOS NOPRINT
END


 

Thanks

quote:

TABLE FILE H_TSFCT2
SUM
STARTIN
ENDIN
TOT_HRS
BY ORG_LVL05_NAM
BY ORG_LVL06_NAM
< snip >
BY STARTDATE
BY ONE
BY FST.STARTIN AS 'STARTO'
BY LST.ENDIN AS ENDO'
BY WORK1_LENGTH_HOR AS 'LENGTH'
ON TABLE HOLD AS HOLD1
END

It's not clear (from what you post) which piece of code occasioned the FOC282; there's usually a companion message saying "IN OR NEAR LINE nnn".

First check whether it's in the DEFINES you added. To trap the fox, start by tossing in a -EXIT before the first TABLE and see whether it persists.

In any event, "BY FST.STARTIN" (in the first TABLE) in not valid syntax.

-- As I understand it, you are trying to make the employee's overall first punch-in and last punch-out times, and total hours, appear on the left, followed by the three figures for the corresponding one or more work intervals.

If so, make
FST.STARTIN AS 'STARTO'
LST.ENDIN AS ENDO'
WORK1_LENGTH_HOR AS 'LENGTH'
verb objects, not sort fields, in the first TABLE (which produces HOLD1). Note that ONE (which governs the ACROSS order) must be the final BY.

No change should be needed in the second TABLE (giving HOLD2) I provided.

And the final TABLE (producing the report) as you coded it should be good.

Let us know how it goes.

Yes J,

you are exactly wright, i want to capture start and end trimmings, but when i use FST and LST, i am not able to capture them somehow, and also when i use these 3 columns in my last table request as BY, my IN OUT HOURS also effect, is there any way that i can print these columns without modified my IN OUT Hours values.

here is what i am doing

 
COMPUTE STARTINH/A2 = IF J7.STRT_PNCHEVNT_DTM IS MISSING THEN '' ELSE HNAME(STRT_PNCHEVNT_DTM, 'HOUR' ,STARTINH);
COMPUTE STARTINM/A2 = IF J7.STRT_PNCHEVNT_DTM IS MISSING THEN '' ELSE HNAME(STRT_PNCHEVNT_DTM,'MINUTE',STARTINM);
COMPUTE STARTIN/A7 = IF STRT_PUNCH_TIME EQ '' THEN 'NOPUNCH' ELSE STARTINH || ':' || STARTINM ;
COMPUTE ENDINH/A2 = IF J7.END_PNCHEVNT_DTM IS MISSING THEN '' ELSE  HNAME(END_PNCHEVNT_DTM, 'HOUR' ,ENDINH);
COMPUTE ENDINM/A2 = IF J7.END_PNCHEVNT_DTM IS MISSING THEN '' ELSE  HNAME(END_PNCHEVNT_DTM,'MINUTE',ENDINM);
COMPUTE ENDIN/A7 = IF END_PUNCH_TIME EQ '' THEN 'NOPUNCH' ELSE ENDINH || ':' || ENDINM ;


 

calling these compute fields in my first table as you guided me
FST.STARTIN AS 'STARTO'
LST.ENDIN AS 'ENDO'
WORK1_LENGTH_HOR AS 'LENGTH'

and then I am doing BY IN MY 3RD TABLE

BY STARTO
BY ENDO
BY LENGTH

Please guide me...

Thanks

Thank you J,
This issue was resolved, Thank you for your Guidance,

Thanks
Krish