Requirement/Issue:
Need to achieve dynamic sorting based on user choice i.e. sorting the records based on user specified field and in user specified order
Approach/Solution:
Step 1: Create a parameter called 'par_DynamicSort' which contains the field name on which user needs the sorting and sort order can be either "ASC" or "DESC" separated by "~" operator
Ex: "EMP_ID~ASC"
Step 2: Create a formula called 'fr_Txt_Sort_Col' and extract the sort column from 'par_DynamicSort' parameter and assign data base field to sort_col variable
@fr_Txt_Sort_Col formula can be written as follows:
sort_col:=left({?par_DynamicSort},length(left(({?par_DynamicSort}),instr({?par_DynamicSort},"~")-1)));
if sort_col ="EMP_ID" then totext({COMMAND.EMP_ID},"###########")
else if sort_col = "EMP_NM" then {COMMAND.EMP_NM}
else if sort_col = "JOIN_DT" then totext({COMMAND.JOIN_DT},"yyyyMMdd")
else if sort_col = "EMP_LOCATION" then {COMMAND.EMP_LOCATION}
else totext({COMMAND.EMP_SAL},"###########.####");
Step 3: Create a 'Group' with group key as 'fr_Txt_Sort_Col'
Step 4: Create a formula called 'fr_Lbl_Sort_Typ' to extract the sort order from 'par_DynamicSort' parameter
@fr_Lbl_Sort_Typ:
stringvar sort_type;
sort_type:=right({?par_DynamicSort},(Length ({?par_DynamicSort})-instr({?par_DynamicSort},"~")));
Step 5: In the 'Change Group Options' we need to write below formula under 'Use Formula as Group Sort Order'.
if {@fr_Lbl_SortType}='ASC' then
crAscendingOrder
else
crDescendingOrder
If par_DynamicSort parameter value is 'EMP_ID~DESC' then report data will be sorted in descending order of EMP_ID
4 Comments
