I have a need for the user to be able to select a variable number of BY fields. They might need 1 level, 2 levels, 3 levels, etc. If they only select values for the first variable then the report needs only 1 BY if they select 2 then the report needs 2 BY columns, etc. Below is a sample .fex but they would need to select values for all three variables.

-* Default Mode: Report Painter

-SET &CNT1 = &CTR.(MODEL,CAR,COUNTRY).CNT1.;
-SET &CNT2 = &CTR1.(MODEL,CAR,COUNTRY).CNT2.;
-SET &CNT3 = &CTR2.(MODEL,CAR,COUNTRY).CNT3.;

SET EXPANDBYROW = ON
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
BY &CNT1
BY &CNT2
BY &CNT3
ON TABLE SET EXPANDABLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

I've searched the forum and cannot find a solution.

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

  
-DEFAULTS &CNT1 = 'FOC_NONE', &CNT2 = 'FOC_NONE', &CNT3 = 'FOC_NONE'
-PROMPT &CNT1.(<NONE,FOC_NONE>,<MODEL,MODEL>,<CAR,CAR>,<COUNTRY,COUNTRY>.Select Sort 1.
-PROMPT &CNT2.(<NONE,FOC_NONE>,<MODEL,MODEL>,<CAR,CAR>,<COUNTRY,COUNTRY>.Select Sort 2.
-PROMPT &CNT3.(<NONE,FOC_NONE>,<MODEL,MODEL>,<CAR,CAR>,<COUNTRY,COUNTRY>.Select Sort 3.
-SET &SORT1 = IF &CNT1 NE 'FOC_NONE' THEN 'BY ' | &CNT1 ELSE '';
-SET &SORT2 = IF &CNT2 NE 'FOC_NONE' THEN 'BY ' | &CNT2 ELSE '';
-SET &SORT3 = IF &CNT3 NE 'FOC_NONE' THEN 'BY ' | &CNT3 ELSE '';

SET EXPANDBYROW = ON
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
&SORT1
&SORT2
&SORT3
ON TABLE SET EXPANDABLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-EXIT 

Not tested, we don't use -PROMPT, we use HTML

That will work, Thanks for the quick response.

  
-PROMPT &CNT1.(<NONE,FOC_NONE>,<MODEL,MODEL>,<CAR,CAR>,<COUNTRY,COUNTRY>.Select Sort 1.
-PROMPT &CNT2.(<NONE,FOC_NONE>,<MODEL,MODEL>,<CAR,CAR>,<COUNTRY,COUNTRY>.Select Sort 2.
-PROMPT &CNT3.(<NONE,FOC_NONE>,<MODEL,MODEL>,<CAR,CAR>,<COUNTRY,COUNTRY>.Select Sort 3.

SET EXPANDBYROW = ON
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
CAR.BODY.RETAIL_COST
BY &CNT1
BY &CNT2
BY &CNT3
ON TABLE SET EXPANDABLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

When WF replaces the paramters with their values, the lines with BY FOC_NONE will not be placed in the FOCSTACK. Hence the simplicity.

The easiest way to do this is in DevStudio.
You first put any number of BY fields on the canvas, then multi-select them, and depress the
button next to the WHERE/IF. It asks for some options and all the coding is done for you.