Is there a way to do this, I get error...

TABLE FILE CAR
SUM
LENGTH
WIDTH
ACROSS SEATS AS ''
RETAIL_COST
DEALER_COST
ACROSS CAR AS ''
BY MODEL
END


Basically, I need LENGTH and WIDTH displayed ACROSS SEATS
and
RETAIL_COST and DEALER_COST displayed ACROSS CAR


Thanks

Not with an ACROSS statement. All the values displayed must be valid for the complete sort path. You can't sort some fields on way and other fields a different way. That doesn't work for BY fields either.

Maybe I'm not understanding what it is you're looking for but that sounds like a very confusing report. I'd be interested to see an example of what your report would look like.

If you add a SUM verb to make this a multi-verb request, it will run without error. But as Darin remarked, it's difficult to understand how the resulting report could be useful.

TABLE FILE CAR
SUM
LENGTH
WIDTH
ACROSS SEATS AS ''
SUM
RETAIL_COST
DEALER_COST
ACROSS CAR AS ''
BY MODEL
END

Like Darin said
"Maybe I'm not understanding what it is you're looking for but that sounds like a very confusing report. I'd be interested to see an example of what your report would look like."

However, consider this:

TABLE FILE CAR
SUM
LENGTH 
WIDTH
RETAIL_COST
DEALER_COST
ACROSS SEATS 
ACROSS CAR 
BY MODEL
END

If not, please explain further...

I think getit posted an interesting question. Recently, our user asked if he can check an item's on-hand qty in all warehouses at the same time see the multiple discount levels of item prices. I was hoping WF allows me to have multiple ACROSSes.

...
SUM
      ON_HAND_QTY
BY ITEM
ACROSS WAREHOUSE
PRINT
      PRICE
BY ITEM
ACROSS DISCOUNT_LEVEL
...
  

Hua

I wonder if this is what you have in mind??

-* File doubleaccross.fex
TABLE FILE CAR
SUM
LENGTH
WIDTH
ACROSS CAR AS ''
ACROSS SEATS
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END


TABLE FILE CAR
PRINT
RETAIL_COST
DEALER_COST
ACROSS CAR NOPRINT
ACROSS SEATS NOPRINT
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

There are quite a few problems in Getit's question.
For each value of MODEL he wants to see a list of LENGTH and WIDTH summed by SEATS followed by a list of RCOST and DCOST summed by CAR. So there is no question that one cannot have 2 sorts one of SEATS and then one of CAR since then the second is an inner sort of the first.
Hence, one has to look for a solution that would put both SEATS and CAR on the same level.
I think that the following is in the desired direction:

  
DEFINE FILE CAR
F1/D6=LENGTH;
F2/D6=WIDTH;
A1/A16=FTOA(SEATS, '(D3c)', 'A3');
V/A5='SEATS';
END
TABLE FILE CAR
SUM F1 F2
BY V
BY MODEL
BY A1
ON TABLE HOLD AS GETIT FORMAT ALPHA
END
FILEDEF GETIT DISK GETIT.FTM (APPEND
DEFINE FILE CAR
F1/D6=RCOST;
F2/D6=DCOST;
A1/A16=CAR;
V/A5='CAR';
END
TABLE FILE CAR
SUM F1 F2
BY V
BY MODEL
BY A1
ON TABLE SAVE AS GETIT 
END
TABLE FILE GETIT
SUM F1 F2
ACROSS HIGHEST V AS ''
ACROSS A1 AS ''
BY MODEL
END

What do you say?

This message has been edited. Last edited by: Danny-SRL,

Seems like Getit may have lost interest - so for the sake of conversation--

My reasoning was the same as Daniel explained, but the approach was completely different. (Actually I couldn't get his code to work but I think it just has to do with the save file being overwritten instead of appended.)

Anyway, I used Dialogue Manager to manually control what is calculated and displayed as an across column without actually using the ACROSS syntax. Grabs all the possible values of CAR and SEAT and manually generates necessary code. Yup, it's ugly.

As a side note, this report layout is still way too confusing to be very useful, in my opinion. Here's my stab at it.

-SET &ECHO=ALL;
-*get all values of seats
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM DST.SEATS BY SEATS NOPRINT
ON TABLE SAVE AS SEATS FORMAT ALPHA
END
-RUN
-SET &SEATCNT=&LINES;
-*get all values of car
TABLE FILE CAR
SUM CAR BY CAR NOPRINT
ON TABLE SAVE AS CARS FORMAT ALPHA
END
-RUN
-SET &CARCNT=&LINES;
-*SET XRETRIEVAL=OFF
DEFINE FILE CAR
-*read values of SEATS and calculate columns
-SET &COUNTER=1;
-REPEAT SEATDEF &SEATCNT TIMES
-READ SEATS &SEATVAL.A3. NOCLOSE
-SET &LFLDNM='LENGTH_'|LJUST(3,'&SEATVAL.EVAL','A3');
-SET &WFLDNM='WIDTH_'|LJUST(3,'&SEATVAL.EVAL','A3');
&LFLDNM.EVAL/D5=IF SEATS EQ &SEATVAL THEN LENGTH ELSE 0;
&WFLDNM.EVAL/D5=IF SEATS EQ &SEATVAL THEN WIDTH ELSE 0;
-SET &COUNTER=&COUNTER+1;
-SEATDEF
-*just to close file
-READ SEATS &SEATVAL.A3.
-*read all values of CAR and calculate columns
-SET &COUNTER=1;
-REPEAT CARDEF &CARCNT TIMES
-READ CARS &CARVAL.A16. NOCLOSE
-SET &RFLDNM='RCOST_'|STRIP(16,LJUST(16,'&CARVAL.EVAL','A16'),' ', 'A16');
-SET &DFLDNM='DCOST_'|STRIP(16,LJUST(16,'&CARVAL.EVAL','A16'),' ', 'A16');
&RFLDNM.EVAL/D7=IF CAR EQ '&CARVAL.EVAL' THEN RCOST ELSE 0;
&DFLDNM.EVAL/D7=IF CAR EQ '&CARVAL.EVAL' THEN DCOST ELSE 0;
-SET &COUNTER=&COUNTER+1;
-CARDEF
-*just to close file
-READ CARS &CARVAL.A16.
END
TABLE FILE CAR
PRINT
-*display values of LENGTH/WIDTH by SEATS that were calculated
-SET &COUNTER=1;
-REPEAT SEATPRT &SEATCNT TIMES
-SET &CLOSE=IF &COUNTER NE &SEATCNT THEN NOCLOSE ELSE '';
-READ SEATS &SEATVAL.A3. &CLOSE.EVAL
-SET &LFLDNM='LENGTH_'|LJUST(3,'&SEATVAL.EVAL','A3');
-SET &WFLDNM='WIDTH_'|LJUST(3,'&SEATVAL.EVAL','A3');
&LFLDNM.EVAL AS '&SEATVAL SEATS,LENGTH'
&WFLDNM.EVAL AS '&SEATVAL SEATS,WIDTH'
-SEATPRT
-*display values of RCOST/DCOST by CAR that were calculated
-SET &COUNTER=1;
-REPEAT CARPRT &CARCNT TIMES
-SET &CLOSE=IF &COUNTER NE &CARCNT THEN NOCLOSE ELSE '';
-READ CARS &CARVAL.A16. &CLOSE.EVAL
-SET &RFLDNM='RCOST_'|STRIP(16,LJUST(16,'&CARVAL.EVAL','A16'),' ', 'A16');
-SET &DFLDNM='DCOST_'|STRIP(16,LJUST(16,'&CARVAL.EVAL','A16'),' ', 'A16');
&RFLDNM.EVAL AS '&CARVAL.EVAL,RCOST'
&DFLDNM.EVAL AS '&CARVAL.EVAL,DCOST'
-CARPRT
BY MODEL
END

This message has been edited. Last edited by: Darin Lee,

I have to reframe myself not to take possession of getit's post. Let's say I am the supporter of it.

Darin, your effort is amazing and is close to what I have in mind. It's ugly because you have to twist the car example to fit the requirement.

It is quite logical(IMO), in my example, ITEM is the highest common key to both paths - warehouse on-hand qty & price file. Both across-fields are finite (only a handful). So am I really dreaming to have double ACROSS?


Hua

You CAN have a double ACROSS, but all values will fall in the same sort path. That is, the second ACROSS is a sub-sort of the first ACROSS and all fields are tabulated and displayed for each combination of sorts. That's what you get with Doug's example. You're not going to be able to have independent ACROSS statements in the same TABLE request.

If the number of ACROSS values can be pre-determined and is somewhat manageable (i.e. small) then just do the calculations without all the Dialogue Manager stuff and you're done. The Dialogue Manager was making it possible to be used with an undetermined (and possibly large) number of ACROSS values for each sort field.