I have the following define field:
SORT/A1 MISSING ON NEEDS SOME =
IF DPAY_PAY_RATE LT 7.50 THEN 'A' ELSE IF DPAY_PAY_RATE LT 9.49 THEN 'B'
ELSE IF DPAY_PAY_RATE LT 11.99 THEN 'C' ELSE IF DPAY_PAY_RATE LT 15.24 THEN 'D'
ELSE IF DPAY_PAY_RATE LT 19.24 THEN 'E' ELSE IF DPAY_PAY_RATE LT 24.49 THEN 'F'
ELSE IF DPAY_PAY_RATE LT 30.99 THEN 'G' ELSE IF DPAY_PAY_RATE LT 39.24 THEN 'H'
ELSE IF DPAY_PAY_RATE LT 49.74 THEN 'I' ELSE IF DPAY_PAY_RATE LT 63.24 THEN 'J'
ELSE IF DPAY_PAY_RATE LT 79.99 THEN 'K' ELSE IF DPAY_PAY_RATE GE 80.00 THEN 'L'
ELSE 'X';

Some customers only have employees in 2 or 3 of the grouping. However, when I print it out I need all of the groupings (A-L) to print. Any and all help will be appreciated.

Thanks,
Victoria

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

Try this

BY SORT ROWS 'A' OVER
'B' OVER
'C' OVER
'D' OVER
...
...
'X'

If I remember correctly, use the syntax:

BY SORT ROWS A OVER
B OVER
C... etc.

TexasStingray,

Thanks for the reply. I'm actually holing this and using it as an ACROSS in the print statement. Any thougths there?

Thanks!

With an ACROSS it's:

ACROSS SORT COLUMNS 'A' AND 'B' AND 'C' ... AND 'L'

That was the first thing I tried, but it didn't work. Here is the entire piece of code:

DEFINE FILE PCKPAY
SORT/A1 =
IF DPAY_PAY_RATE LT 7.50 THEN 'A' ELSE IF DPAY_PAY_RATE LT 9.49 THEN 'B'
ELSE IF DPAY_PAY_RATE LT 11.99 THEN 'C' ELSE IF DPAY_PAY_RATE LT 15.24 THEN 'D'
ELSE IF DPAY_PAY_RATE LT 19.24 THEN 'E' ELSE IF DPAY_PAY_RATE LT 24.49 THEN 'F'
ELSE IF DPAY_PAY_RATE LT 30.99 THEN 'G' ELSE IF DPAY_PAY_RATE LT 39.24 THEN 'H'
ELSE IF DPAY_PAY_RATE LT 49.74 THEN 'I' ELSE IF DPAY_PAY_RATE LT 63.24 THEN 'J'
ELSE IF DPAY_PAY_RATE LT 79.99 THEN 'K' ELSE IF DPAY_PAY_RATE GE 80.00 THEN 'L'
ELSE 'X';
END


MATCH FILE JOBDATA
SUM
JOB_FUNCTION
BY PARENT
BY PERID
RUN

FILE PCKPAY
BY PARENT
BY ID AS 'PERID'
BY SORT
WHERE PARENT IN (&parent)
WHERE ((BEGDT LE '&REFDATE') AND (ENDDT GE '&REFDATE'))
WHERE SUMGRP EQ 1 OR 8
AFTER MATCH HOLD AS FINAL OLD-AND-NEW
RUN

FILE CLIENT_DETAIL
SUM
NAME
BY PARENT_CLIENT_ID AS 'PARENT'
WHERE PARENT_CLIENT_ID IN (&parent)
WHERE CLIENT_STATUS_CD EQ 1
AFTER MATCH HOLD AS FINAL OLD-AND-NEW
END
-RUN


DEFINE FILE FINAL
CNTX/I4 = 1;
BLANK/A1 = '';
END


TABLE FILE FINAL
HEADING
"STATISTICS REPORT"
"SUM
CNTX
BY PARENT NOPRINT
BY BLANK NOPRINT
BY JOB_FUNCTION AS 'JOB FUNCTION'
ACROSS SORT AS '' COLUMNS 'A' AND 'B' AND 'C' AND 'D' AND 'E' AND 'F' AND 'G' AND 'H'
AND 'I' AND 'J' AND 'K' AND 'L'
ON PARENT ROW-TOTAL
ON BLANK SUBTOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END

Victoria

I could be wrong as I haven't used this syntax in a little while, but shouldn't it be

ACROSS SORT COLUMNS 'A' AND 'B'.. AS ' '

I have this feeling that the AS should be at the end.

You should also move the third part of your MATCH so that it is the FIRST part since it only has one sort (BY) field. Usually each successive MATCH portion has the same or more sorts. Those with less sorts should be coded first.

That's how I had it originally, then I got this error: (FOC002) A WORD IS NOT RECOGNIZED: AS
-- so I moved it. It runs with the AS '' right after the SORT, but I still only get the columns with data. The data that I'm running only has data in columns H and L. Regardless of that, I'd like every column to print.

Ah, I wonder. Mickey could have a point here.

Moving the 3rd Match to be the first and have increasing sort fields would be correct.

Yes, you're right, on checking the AS goes before the COLUMNS.

BUT, I tried a simple repro and cannot get the symptoms you get. This is 7.1.3 and I got all the columns I asked for, existing or not.

Really got me.

Alan,

Thanks for your help. I moved the 3rd match up, but it does nothing to the output. I usually pyramid the BYs, but I'm trying anything at this point!! I've asked a teammate here to take a look at it for me. I'll post a result if I find one.

Thanks!

Victoria,

Could you please show us what the Master for the FINAL HOLD file looks like? It's quite hard to make definitive suggestions when we can't create and run code using the IBI provided sample files, so having the layout of FINAL may help.

Cheers,

Well I figured out that if I comment out the second and third ACROSS fields, it works great and includes A - L. Now I just have to work those 2 back in somehow.

quote:

TABLE FILE FINAL
HEADING
"STATISTICS REPORT"
"
SUM
CNTX
BY PARENT NOPRINT
BY BLANK NOPRINT
BY JOB_FUNCTION AS 'JOB FUNCTION'
ACROSS SORT AS '' COLUMNS 'A' AND 'B' AND 'C' AND 'D' AND 'E' AND 'F' AND 'G' AND 'H'
AND 'I' AND 'J' AND 'K' AND 'L'
ON PARENT ROW-TOTAL
ON BLANK SUBTOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END

Sure looked like only one ACROSS statement and only one ACROSS field here.

Forcing columns to display with Multiple fields in one ACROSS statement appears to work as expected:

TABLE FILE CAR
SUM
SALES
DCOST
RCOST
ACROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
BY COUNTRY
END

Forcing columns to display with more than one across statement does not work:

TABLE FILE CAR
SUM
SALES
DCOST
RCOST
ACROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
ACROSS LENGTH
BY COUNTRY
END

Sorry, I'm not sure how that posted without the other 2 ACROSS fields. At any rate, if anyone knows how to make that work it would be much appreciated.

However, if you use
TABLE FILE CAR
.
.
ACROSS LENGTH
AROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
.
.
END

I bet it will work. COLUMNS only works as the last in the ACROSS sort because of something to do with the grouping of the sort columns in an across phrase when building the internal matrix. Would need someone (Gerry/Noreen)to give a full explanation of this as I cannot remember.

quote:

Sorry, I'm not sure how that posted without the other 2 ACROSS fields

Gosh, I bet you more than one person trying to help here thought there was only one ACROSS.

Meanwhile, as Alan suggests, making the ACROSS with the COLUMNS code as the last ACROSS will work:

TABLE FILE CAR
SUM
SALES
DCOST
RCOST
ACROSS LENGTH
ACROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
BY COUNTRY
END

Alan,

Thank you very much for your post. I'm going to try it. I apologize of not giving you the information on the other two across fields. It was of course not intential. I appreciate your help.

Here's another idea:

DEFINE FILE FINAL
CNT_A/I4 = IF DPAY_PAY_RATE LT  7.50 THEN 1 ELSE 0;
CNT_B/I4 = IF DPAY_PAY_RATE GE  7.50 AND DPAY_PAY_RATE LT  9.49 THEN 1 ELSE 0;
CNT_C/I4 = IF DPAY_PAY_RATE GE  9.49 AND DPAY_PAY_RATE LT 11.99 THEN 1 ELSE 0;
CNT_D/I4 = IF DPAY_PAY_RATE GE 11.99 AND DPAY_PAY_RATE LT 15.24 THEN 1 ELSE 0;
CNT_E/I4 = IF DPAY_PAY_RATE GE 15.24 AND DPAY_PAY_RATE LT 19.24 THEN 1 ELSE 0;
CNT_F/I4 = IF DPAY_PAY_RATE GE 19.24 AND DPAY_PAY_RATE LT 24.49 THEN 1 ELSE 0;
CNT_G/I4 = IF DPAY_PAY_RATE GE 24.49 AND DPAY_PAY_RATE LT 30.99 THEN 1 ELSE 0;
CNT_H/I4 = IF DPAY_PAY_RATE GE 30.99 AND DPAY_PAY_RATE LT 39.24 THEN 1 ELSE 0;
CNT_I/I4 = IF DPAY_PAY_RATE GE 39.24 AND DPAY_PAY_RATE LT 49.74 THEN 1 ELSE 0;
CNT_J/I4 = IF DPAY_PAY_RATE GE 49.74 AND DPAY_PAY_RATE LT 63.24 THEN 1 ELSE 0;
CNT_K/I4 = IF DPAY_PAY_RATE GE 63.24 AND DPAY_PAY_RATE LT 79.99 THEN 1 ELSE 0;
CNT_L/I4 = IF DPAY_PAY_RATE GE 79.99 THEN 1 ELSE 0;

BLANK/A1 = '';
END

TABLE FILE FINAL
HEADING
"STATISTICS REPORT"
"
SUM
CNT_A
CNT_B
CNT_C
CNT_D
CNT_E
CNT_F
CNT_G
CNT_H
CNT_I
CNT_J
CNT_K
CNT_L

BY PARENT NOPRINT
BY BLANK NOPRINT
BY JOB_FUNCTION AS 'JOB FUNCTION'
ACROSS ...
ACROSS ...
ON PARENT ROW-TOTAL
ON BLANK SUBTOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END

Victoria, did the last suggestion help at all? It should resolve trying to get all the columns to display and have multiple ACROSS statements.

Francis, I liked the idea in your second last post. But what if you don't know the number and the value of ACROSS column? I'm stuck in a dilemma that

1. If I use ACROSS, some ACROSS column that do not have values won't show up;

2. If I use DEFINE field, the ACROSS columns are dynamic and I don't know all of them.

If you have a table where you can find all possible values of the ACROSS column, you could build the ACROSS COLUMNS statement and use that instead of hardcoding them:

-SET&ECHO='ON';

SET HOLDLIST = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN

TABLE FILE CAR
SUM
COMPUTE ROWCOUNT/P6 = ROWCOUNT + 1; NOPRINT

COMPUTE AND/A5 = IF ROWCOUNT EQ 1 THEN '' ELSE ' AND ';

COMPUTE ACROSSCOLVALUE/A30 = '''' || BODYTYPE || '''';

BY BODYTYPE NOPRINT

ON TABLE HOLD AS HACROSSCOLS
END
-RUN

TABLE FILE CAR
SUM
SALES
BY COUNTRY
ACROSS BODYTYPE AS ''
COLUMNS
-INCLUDE HACROSSCOLS
END