I want to be able to give my users the option to select their sort columns at runtime.
I want to have a dropdown list with two options(Region and Country). If the user selects Region the report should sort by Region and then by Country. If she selects Country then the sort should be by Country then by Region.

I've looked at other posts on this forum using the search phrase "dynamic column sorting" but everything I found was about sorting the same column from lowest to highest and vice/versa using a hyperlink.

Can anyone help with this?

This message has been edited. Last edited by: Michael Watts,

Hi,

You can achieve this using dialog manager commands. Below example shows sorting BY COUNTY and BY CAR based on user selection.

TABLE FILE CAR
PRINT
SALES
DEALER_COST
-IF &USER_SELECTION EQ 'COUNTRY' THEN GOTO L_SRT_CNTY ELSE GOTO L_SRT_CAR;
-L_SRT_CNTY
BY COUNTRY
BY CAR
-GOTO L_SRT_END;
-L_SRT_CAR
BY CAR
BY COUNTRY
-L_SRT_END
END

Hope this helps.

Thanks,
Ram

You can use parameter promoting for fields, including sorting:

.
BY  &Sort.(<COUNTRY,COUNTRY>,<CAR,CAR>,<None,_FOC_NULL>).Please select sort field.
.

This can be used by both parameter promoting and recognised in HTML composer.

You can also use this way to avoid the DM command in the TABLE FILE ... END part:

-* EITHER WAY USING IF OR DECODE WORKS, DECODE TAKES LESS CODE
-*-SET &BY1 = IF &USER_SELECTION EQ 'COUNTRY' THEN 'COUNTRY' ELSE IF &USER_SELECTION EQ 'CAR' THEN 'CAR'     ELSE 'MODEL';
-*-SET &BY2 = IF &USER_SELECTION EQ 'COUNTRY' THEN 'CAR'     ELSE IF &USER_SELECTION EQ 'CAR' THEN 'COUNTRY' ELSE BODYTYPE';

-SET &BY1 = DECODE &USER_SELECTION ('COUNTRY' 'COUNTRY' 'CAR' 'CAR'     ELSE 'MODEL');
-SET &BY2 = DECODE &USER_SELECTION ('COUNTRY' 'CAR'     'CAR' 'COUNTRY' ELSE 'BODYTYPE');

TABLE FILE CAR
PRINT SALES
      DEALER_COST
BY &BY1
BY &BY2
END

My example is close to Martin's example. Mine assumes you have an HTML page which contains your selection dropdown box. The value selected from the drop down will be passed into your fex as an &VAR. In my example I'm setting '&SORT' since I don't have an HTML page in my example:

 
-SET &SORT = 'CAR';
-SET &SORT2 = IF &SORT EQ 'COUNTRY' THEN 'CAR' ELSE 'COUNTRY';

TABLE FILE CAR
PRINT MODEL
	  SEATS
BY &SORT.EVAL
BY &SORT2.EVAL
END
-EXIT
 

Or you could do it like this:

-SET &SORTLIST = IF &USER_SELECTION EQ 'COUNTRY' THEN 'BY COUNTRY BY CAR' ELSE 'BY CAR BY COUNTRY';

TABLE FILE CAR
PRINT 
SALES
DEALER_COST
&SORTLIST
END

Note, however,that if you open this in the DevStudio GUI editor it will complain about the &SORTLIST inside the table. However it runs just fine.