Skip to Content

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

Screen shot of Change Group Options and Group sort Order

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Michelle Crapo
    Did you think about calling an SAP function module?  If I’ve totally missed the point, feel free to tell me.

    Now that you have this “code” you really don’t have to.  Amazing work.  I can barely create a Crystal report, so I know how hard this must have been.

    Nice one!

    Michelle

    (0) 
    1. yugender balasa Post author
      Hi Michelle,

      In Crystal reports 2008 we have Dynamic sort control which basically takes care of sorting based on user interaction. If the user clicks on the drop down of sort column(sort control) the data will be sorted i.e. if the data is in ‘ASC’ order of that column, it will sort it in ‘DESC’ or vice versa.

      My code will be more helpful in earlier version of crystal reports.

      (0) 
      1. Michelle Crapo
        I see – so a trick to use early!  Now I understand it better.

        Sorry about the lack of experience on my part.  We try to run these for quick data.  Of course we run ABAP query too.  But that’s not as easy.

        Our user base actually creates the nicer looking Crystal reports.  A friend of mine supports some of the more difficult to write Crystals.

        And I try to learn as much as I can.  Crystal reporting will help me out.  If for now other reason than to quickly get test data!  I just never seem to get the chance to explore it.

        Thank you for the extra information!

        Michelle

        (0) 
        1. yugender balasa Post author
          Hi Michelle,

          No problem. If you need any help in crystal reports please let me know I will help you out.

          I will try to post one more blog which covers basic formatting of amount fields which will be helpful for beginners.

          Thanks
          Yugender

          (0) 

Leave a Reply