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,
8.0.03/Windows7
October 23, 2014, 02:04 AM
Ram Prasad E
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
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.
Alan. WF 7.705/8.007
October 23, 2014, 07:46 AM
MartinY
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
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
October 23, 2014, 07:56 AM
linus
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
WF 7.7.05 HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files HTML, Excel, PDF
October 23, 2014, 09:34 AM
George Patton
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.