I have fex program that looks something like the below;

TABLE FILE KPIDATA
SUM
FIGURE
ACROSS COUNTRY
FOR
FIELD
"KPI" LABEL R1 OVER
..........

Is there a way to arrange the order of the ACROSS COUNTRY the way I want, rather than using the standard HIGHEST or other predefined sort?

Yes,

Search on COLUMNS...

Additionally, it's obvious you "didn't" update your profile

Learner
this is the easy way
DEFINE FILE CAR
sortorder/I1=IF COUNTRY IS 'W GERMANY' THEN 1 ELSE
IF COUNTRY IS 'ENGLAND' THEN 2 ELSE
IF COUNTRY IS 'FRANCE' THEN 3 ELSE
IF COUNTRY IS 'CHINA' THEN 4 ELSE 5 ;
END
-RUN
TABLE FILE CAR
SUM SALES
ACROSS sortorder NOPRINT
ACROSS COUNTRY
...whatever else
END
..
the idea here is the NOPRINT concept
It means 'do it, just don't tell me about it'
and we've DEFINEd a variable to put your elements in order.

Tom's way works for GRAPH FILE, where NOPRINT doesnt seem to work.
TABLE FILE CAR
SUM SALES
ACROSS COUNTRY
COLUMNS 'W GERMANY' AND 'ENGLAND' AND 'FRANCE' AND ... you get the idea

The alternative to hardcoding your values is to retrieve the values to be used in the COLUMNS or ROWS syntax and then repetatively read the values in to build your code. Have a look at this article for the idea.

T

Thank you Tom and Susannah and Tony,

After viewing your suggestions and putting into my code, it is working exactly the way i want it. I have used Susannah method because it works better in my program.. Thanks Susannah for the solution. Very much appreciate.

Simon

you're most welcome, California,
now please go to your 'Personal Zone' , 'Profile', and edit the signature block in that profile, so that we know what your system/version is , or are.
-S.

Just for the record I would like to provide a simple example so you see easy it is to use the 'columns' feature.
TABLE FILE CAR
SUM SALES
ACROSS BODYTYPE COLUMNS SEDAN AND COUPE AND WAGON AND WAGON
BY COUNTRY
END

Thanks GeraldCohen for the additional solutions. I gain so much after i joined this forum. It is very useful!

Learner

The "columns" function gives you also the possibility to add a column even if there is no data available.

Just add an "non existing" item to see the result.

wow. that is so handy to know...
It would be amusing to know how exactly you tripped over that little factoid

Susanahh,

That "little factoid" is mentioned in the link above.

T

Yeah, COLUMNS creates a sort of Procrustean bed: lop off any uninvited values, and insert any no-shows.

aha! i knew i had seen that somewhere before.
thanks T.
this is getting good , jg
1 : of, relating to, or typical of Procrustes
2 : marked by arbitrary often ruthless disregard of individual differences or special circumstances

i apologize to Learner for hijacking his thread..we get carried away sometimes.

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

Did I miss something? Without the "SET HOLDLIST = PRINTONLY" (Or, is that the default setting at this installation?) I get the following:

ERROR AT OR NEAR LINE 22 IN PROCEDURE rowscommand
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: CONVERTIBLE
CONVERTIBLE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Adding the -SET &ECHO = ALL; reveals the following:

TABLE FILE CAR
SUM CAR
BY COUNTRY
BY BODYTYPE
ROWS 'CONVERTIBLE CONVERTIBLE' OVER 'COUPE COUPE' OVER 'HARDTOP HARDTOP' OVER 'ROADSTER ROADSTER' OVER 'SEDAN SEDAN'
BY CAR NOPRINT
END

Susannah -

Actually more extreme than Procrustes -- he would either stretch or shorten his visitors; COLUMNS may do both