Is it possible to display the data by selective? Table & Display sample is below:

TABLE FILE ITEM1
SUM PRICE1 OVER
PRICE2 OVER
PRICE3
ACROSS THEMONTH
BY ITEM
END

Display like:
ITEM AA
PRICE1 111
PRICE2 222
BB
PRICE2 333
CC
PRICE1 123
PRICE2 234
PRICE3 456


My question is WHEN ITEM is AA, it will print PRICE1 & 2. WHEN ITEM is BB, it will print PRICE2. AND WHEN ITEM is CC, it will print PRICE1 & 2 & 3.

How can I do like this? Please help me !

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

What you could do is a

BY ITEM

ON ITEM SUBFOOT
"PRICE1 <PRICE1"
"PRICE2 <PRICE2"
WHEN ITEM EQ 'AA'

ON ITEM SUBFOOT
"PRICE2 <PRICE2"
WHEN ITEM EQ 'BB'

ON ITEM SUBFOOT
"PRICE1 <PRICE1"
"PRICE2 <PRICE2"
"PRICE3 <PRICE3"
WHEN ITEM EQ 'CC'


Good Luck and Hope this helps

Sorry about that maybe I'm not post the question clearly!



TABLE FILE CAR
SUM PRICE1 OVER
PRICE2 OVER
PRICE3
ACROSS THEMONTH
BY ITEM
END
[/code]My question is the output will be like below display:
[code]
THEMONTH
1 2 3 4 5 6 7 8 9 10 11 12
ITEM
AA PRICE1 11 12 13 14 15 16 17 18 19 20 21 22
BB PRICE1 11 12 13 14 15 16 17 18 19 20 21 22
PRICE2 21 22 23 24 25 26 27 28 29 30 31 32
CC PRICE1 11 12 13 14 15 16 17 18 19 20 21 22
PRICE2 21 22 23 24 25 26 27 28 29 30 31 32
PRICE3 31 32 33 34 35 36 37 38 39 40 41 42
ITEM AA only prints PRICE1
ITEM BB only prints PRICE1 and PRICE2
ITEM CC only prints PRICE1, PRICE2 and PRICE3

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

Ringo,

This one is going to be hard to answer with the info provided. We need (I do, at least) a master file description for ITEM1. Also: Is the selection scheme (Price 1 for AA, 1 & 2 for BB, etc)chosen by the data or by you? In other words, did you decide not to print Price 2 & 3 data exist for AA, or is it not printing becuase it does not exist for AA?

Actually, I knew the PRICE1, PRICE2 & PRICE3 are existed for ITEM AA, BB & CC. But I do want to print the report will be look like my posted question above!

Ringo, could you post the Master File Description (.MAS). Are price1 price2 and price3 seperate fields or are they the same column but have a field that tell you they are price1, price2, price3.

My .MAS is below:


FILE=CAR , SUFFIX=SQLODBC ,$
SEGNAME=CAR , SEGTYPE=S0 ,$
FIELD=ITEM ,ITEM,
A4 ,A4 ,MISSING=OFF,$
FIELD=PRICE1 ,PRICE1,
I10 ,I4 ,MISSING=ON ,$
FIELD=PRICE2 ,PRICE2,
I10 ,I4 ,MISSING=ON ,$
FIELD=PRICE3 ,PRICE3,
I10 ,I4 ,MISSING=ON ,$
FIELD=THEMONTH ,THEMONTH,
I10 ,I4 ,MISSING=ON ,$
[/code]

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

Have you heard of the McGuyver Technique. and print multiple rows from 1 record. you could define price to = price1 when counter eq 1 or price2 when counter eq 2 or price3 when counter eq 3. If you have tech support you could search for mcguyver.

I didn't hear the McGuyver Technique before. Would you mind to provide me more information ?

Thanks a lot !

Try this link:

http://techsupport.informationbuilders.com/tech/contents/cof_tcn_toc.html

Give something like this a try:

SET ASNAMES = ON;
-* ITEM AA
TABLE FILE CAR
SUM
ITEM
PRICE1 AS 'PRICE'
BY THEMONTH
WHERE ITEM EQ 'AA'
ON TABLE HOLD AS H0 FORMAT ALPHA
END

-* ITEM BB
TABLE FILE CAR
SUM
ITEM
PRICE1 AS 'PRICE'
BY
THEMONTH
WHERE ITEM EQ 'BB'
ON TABLE HOLD AS H1 FORMAT ALPHA
END

TABLE FILE CAR
SUM
ITEM
PRICE2 AS 'PRICE'
BY
THEMONTH
WHERE ITEM EQ 'BB'
ON TABLE HOLD AS H2 FORMAT ALPHA
END

-* ITEM CC
TABLE FILE CAR
SUM
ITEM
PRICE1 AS 'PRICE'
BY
THEMONTH
WHERE ITEM EQ 'BB'
ON TABLE HOLD AS H3 FORMAT ALPHA
END

TABLE FILE CAR
SUM
ITEM
PRICE2 AS 'PRICE'
BY
THEMONTH
WHERE ITEM EQ 'BB'
ON TABLE HOLD AS H4 FORMAT ALPHA
END

TABLE FILE CAR
SUM
ITEM
PRICE3 AS 'PRICE'
BY
THEMONTH
WHERE ITEM EQ 'BB'
ON TABLE HOLD AS H5 FORMAT ALPHA
END

-* FINAL OUTPUT
TABLE FILE H0
PRINT
PRICE
BY
ITEM
ACROSS
THEMONTH
MORE
FILE H1
MORE
FILE H2
MORE
FILE H3
MORE
FILE H4
MORE
FILE H5
END

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

Thanks Jack & Curtis.

For Jack:
I cannot access the link since I don't have User ID and Password.

For Curtis:
It seems work. But, I don't want to hardcode the ITEM field for 'AA, BB, CC'. I have over two hundred records. If using this way, I need to hold hundred tables.

Try the following approach

Create a focus master to use as a driver file and populate it with your item/price combinations

FILE=itemcref, SUFFIX=FOC
SEGNAME=ROOT_SEG, SEGTYPE=S0, $
FIELD=ITEMCRF, ALIAS=ITEMCRF, FORMAT=A10, FIELDTYPE=I, $


CREATE FILE ITEMCREF
-RUN
MODIFY FILE ITEMCREF
FIXFORM ITEMCRF/10
MATCH ITEMCRF
ON MATCH INCLUDE
ON NOMATCH INCLUDE
DATA
AA PRICE1
AA PRICE2
BB PRICE1
CC PRICE1
CC PRICE2
CC PRICE3
END
-RUN

Create a master as follows to use with the hold file that will be created
(note you might have to adjust the ACTUAL because the master for the data file I used may not match yours

FILE=R1, SUFFIX=FIX
SEGNAME=ROOT_SEG, SEGTYPE=S1, $
FIELD=FILLER1, ALIAS=FILLER1, USAGE=A2, ACTUAL=A2, $
FIELD=ITEM, ALIAS=ITEM, USAGE=A4, ACTUAL=A4, $
FIELD=FILLER2, ALIAS=FILLER2, USAGE=A2, ACTUAL=A2, $
FIELD=PRICECOL, ALIAS=PRICECOL, USAGE=A6, ACTUAL=A6, $
FIELD=MTH1, ALIAS=MTH1, USAGE=I11, ACTUAL=A11, $
FIELD=MTH2, ALIAS=MTH2, USAGE=I11, ACTUAL=A11, $
FIELD=MTH3, ALIAS=MTH3, USAGE=I11, ACTUAL=A11, $
FIELD=MTH4, ALIAS=MTH4, USAGE=I11, ACTUAL=A11, $
FIELD=MTH5, ALIAS=MTH5, USAGE=I11, ACTUAL=A11, $
FIELD=MTH6, ALIAS=MTH6, USAGE=I11, ACTUAL=A11, $
FIELD=MTH7, ALIAS=MTH7, USAGE=I11, ACTUAL=A11, $
FIELD=MTH8, ALIAS=MTH8, USAGE=I11, ACTUAL=A11, $
FIELD=MTH9, ALIAS=MTH9, USAGE=I11, ACTUAL=A11, $
FIELD=MTH10, ALIAS=MTH10, USAGE=I11, ACTUAL=A11, $
FIELD=MTH11, ALIAS=MTH11, USAGE=I11, ACTUAL=A11, $
FIELD=MTH12, ALIAS=MTH12, USAGE=I11, ACTUAL=A11, $
FIELD=FILLER3, ALIAS=FILLER3, USAGE=A2, ACTUAL=A2, $


Table your input file and hold format WP

TABLE FILE PRICEFILE
SUM
PRICE1 OVER
PRICE2 OVER
PRICE3
ACROSS THEMONTH
BY ITEM
ON TABLE HOLD AS R1 FORMAT WP
END
-RUN

Join the wp hold file to your cross-reference file and simply print the report
(You must reference the field in the cross-reference file for short pathing to eliminate those rows you do not want.)

SET ALL=OFF
JOIN ITEMCRF WITH ITEM IN R1 TO ALL ITEMCRF IN ITEMCREF TAG T2 AS J1
DEFINE FILE R1
ITEMR/A4=IF ITEM EQ ' ' THEN LAST ITEMR ELSE ITEM;
ITEMCRF/A10= ITEMR | PRICECOL;
END
TABLE FILE R1
PRINT ITEM PRICECOL
T2.ITEMCRF NOPRINT
MTH1 AS '01'
MTH2 AS '02'
MTH3 AS '03'
MTH4 AS '04'
MTH5 AS '05'
MTH6 AS '06'
MTH7 AS '07'
MTH8 AS '08'
MTH9 AS '09'
MTH10 AS '10'
MTH11 AS '11'
MTH12 AS '12'
END

Thanks for JG's approach, but it cannot solve my problem!