ALV Sorting with reordering of Fieldcat

We recently had a requests in a report to offer sorting options at selection time (not in the ALV Grid later).

So basically what you do is offer Parameters for Sortorder or even better a restricted Select-Option if you have a Domain of the Columns.

Then you have a iTab representing the Sort Order in the ata Definition.

TYPES: BEGIN  OF SORT_HELP,
          POS     TYPE N LENGTH 2,
          TABNAME TYPE YI_BDSCOLS.
TYPES: END    OF SORT_HELP.


DATA: IT_SORTLIST          TYPE TABLE OF SORT_HELP,
     VAR_SORTITEM         TYPE SORT_HELP.


You can write a Function to add Columns to the sorting

*&---------------------------------------------------------------------*
*&      Form  ADD_SORTITEM
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->F_POS  Position of Col in Sorting
*      -->F_TABNAME Col Name
*----------------------------------------------------------------------*
FORM CUST_ADD_SORTITEM  USING    F_POS TYPE N
                            F_TABNAME TYPE CHAR20.

  IF F_POS > 0 .
    CLEAR VAR_SORTITEM.
    READ TABLE IT_SORTLIST WITH KEY POS = F_POS TRANSPORTING NO FIELDS.
    "Wenn Schlüssel schon vorhanden haben wir eine doppelte Zeile
    IF SY-SUBRC = 0.
        MESSAGE E685(yb).
    ELSE.
      VAR_SORTITEM-TABNAME = F_TABNAME.
      VAR_SORTITEM-POS = F_POS.
      APPEND VAR_SORTITEM TO IT_SORTLIST.
      SORT IT_SORTLIST BY POS ASCENDING.
    ENDIF.

  ENDIF.

ENDFORM.                    " ADD_SORTITEM


And wherever you fill your ALV Grid you generate the Sorting upfront:

*# Sort
  DATA L_WA_SORT                  TYPE LVC_S_SORT.          "#EC *

  CLEAR C_T_SORT. REFRESH C_T_SORT.

  LOOP AT IT_SORTLIST INTO VAR_SORTITEM.

    CLEAR: L_WA_SORT.
    L_WA_SORT-SPOS        = SY-TABIX.           "Level of Sortierung
    L_WA_SORT-FIELDNAME   = VAR_SORTITEM-TABNAME.  "Sortcolumn
    L_WA_SORT-UP          = 'X'.         "Sortierung ascending
    L_WA_SORT-DOWN        = ' '.         "Sortierung descending
    APPEND L_WA_SORT TO C_T_SORT.

  ENDLOOP.

The neat thing now is, that you also are able to redo the Fieldcat of the ALV Grid in Order of the Sorting upfront.

I did it like this, there may be a better way. Comments are welcome.



  DATA: L_IT_FIELDCAT_NEW TYPE LVC_T_FCAT,
        L_VAR_COL_POS_CNT TYPE I VALUE 1.

  FIELD-SYMBOLS <L_WA_FIELDCAT_COL> TYPE LVC_S_FCAT.

  "Copy all FieldCat Fields not associated with viewable Cols
  LOOP AT C_T_FIELDCAT ASSIGNING <L_WA_FIELDCAT_COL>
      WHERE TABNAME EQ ''.
    APPEND <L_WA_FIELDCAT_COL> TO L_IT_FIELDCAT_NEW.
    DELETE C_T_FIELDCAT.
  ENDLOOP.

  "Insert Cols from the SortList and remove them from the initial Fieldcat
  LOOP AT IT_SORTLIST INTO VAR_SORTITEM.
    READ TABLE C_T_FIELDCAT
                WITH KEY FIELDNAME = VAR_SORTITEM-TABNAME
                INTO L_WA_FIELDCAT.
    IF SY-SUBRC = 0.
      DELETE C_T_FIELDCAT
        WHERE FIELDNAME = VAR_SORTITEM-TABNAME.
    ENDIF.
    APPEND L_WA_FIELDCAT TO L_IT_FIELDCAT_NEW.
  ENDLOOP.

  "Now add all Cols from the Initial Fieldcat after Sorted Ones
  LOOP AT C_T_FIELDCAT ASSIGNING <L_WA_FIELDCAT_COL> .
    APPEND <L_WA_FIELDCAT_COL> TO L_IT_FIELDCAT_NEW.
  ENDLOOP.

  "At last you have to assign new Col_Positions for all Entriew in the FieldCat
  LOOP AT L_IT_FIELDCAT_NEW ASSIGNING <L_WA_FIELDCAT_COL>
    WHERE TABNAME NE ''.
    <L_WA_FIELDCAT_COL>-COL_POS = L_VAR_COL_POS_CNT.
    L_VAR_COL_POS_CNT = L_VAR_COL_POS_CNT + 1.
  ENDLOOP.

  C_T_FIELDCAT = L_IT_FIELDCAT_NEW.


There you go :)

Restricting Select-Options

If you ever wanted to restrict the possibilities of Select-Options, there is good news.

The FM 'SELECT_OPTIONS_RESTRICT' is the way to go.

 
  #---------------------------------------------------------------------*
*# Sortierung-Options restrict to EQ


  DATA: L_WA_RESTRICT TYPE SSCR_RESTRICT,
        L_WA_OPTLIST  TYPE SSCR_OPT_LIST,
        L_WA_ASS      TYPE SSCR_ASS.

* Only 'EQ' 
  L_WA_OPTLIST-NAME = 'OBJECTKEY1'.
  L_WA_OPTLIST-OPTIONS-EQ = 'X'.
  APPEND L_WA_OPTLIST TO L_WA_RESTRICT-OPT_LIST_TAB.

  L_WA_ASS-KIND = 'S'.
  L_WA_ASS-NAME = 'S_SORT'.
  L_WA_ASS-SG_MAIN = 'I'.
  L_WA_ASS-SG_ADDY = SPACE.
  L_WA_ASS-OP_MAIN = 'OBJECTKEY1'.
  APPEND L_WA_ASS TO L_WA_RESTRICT-ASS_TAB.


  CALL FUNCTION 'SELECT_OPTIONS_RESTRICT'
    EXPORTING
      RESTRICTION = L_WA_RESTRICT.
  IF SY-SUBRC <> 0.
* Implement suitable error handling here
  ENDIF.

Date Conflicts, Intersections and Co.

Often you will have to deal with Dates and Ranges. Lets have a look at a tricky case that often occurs when looking for date intersections ( conflicting dates ). The best and fastest way to solve this is actually not to test if a date (range) is inside the allowed date intevall, but to test if it is not outside the intervall.

Here is sample of intersection tests (green) on date ranges.

As you can see, the green ones are intersections the red ones are not. So it the easiest way to this as a Select statement would be:

SELECT * FROM t1 WHERE NOT ( data.start_date  > start_date OR data.end_date < end_date )

which could also be expressed as:

SELECT * FROM t1 WHERE  data.end_date >= start_date AND data.start_date <= end_date