Introduction

 

In this Blog, we will combine an Excel upload, Dynamic Select Option generation, display on a Popup Screen and show the results in ALV IDA created for HANA. Continuing this blog series, the goal is to focus on comparing older ABAP language features with a detailed explanation of the new ABAP syntax and S/4HANA features.

See version 2 of this program with the ability to store the report settings here.

This Blog is organized as follows:

• Program Overview

  • Program Flow Diagram

  • Excel File Format

• Program Walkthrough

• HANA and CDS Views

• Method Tutorial

  • Method Chaining and NEW

  • Read Excel Data

  • Build Select Options

  • Popup Selection Screen

  • ALV IDA

• Complete Program

• References

 

Software Versions:

• S/4HANA (SAP_BASIS 752, SAP_ABA 75C)

• SAPGUI Version 750

• Eclipse Version: Oxygen.3a Release (4.7.3a)

• Microsoft Excel (.xlsx capable)

 

Program Overview

 

Program Flow Diagram

Excel File Format

The Excel file will contain either a Table, View or CDS View with the fields that you’d like to appear as Select-Options on your popup window.

Format:

Column A will only contain 2 labels:

• A1 = Table, View or CDS View Name
• A2 = Selection Fields

Column B simply contains the Table/View/CDS View name and a list of the fields you want to be included as Select Options on the Popup window:

• B1 = Table, View or CDS View Name
• B2+ = List of fields to be included as Select Options

Example:

For the CDS View “S_BOOKINGS”, include the fields CARRIERID, FLIGHTDATE, CUSTOMERID, LOCALCURRENCYAMOUNT on the popup window as Select-Options.

The popup window for the above, would look like the following:

With this, you can simply change your Excel Spreadsheet to point to any Table/View/CDS View and type in the fields you want on the Selection Screen and re-run it to read your updated spreadsheet and execute your new report.

An Excel upload is used here, but you can easily substitute the Excel upload with your own way of passing in these values. You simply need to populate an internal table with these values. In this program, populate the <excel_table> in the build_select_options method.

Here are some other ideas to setup the program runtime parameters for the Table/View/CDS View and the selection fields:

• Create a Z Table maintained in SM30
• Hardcode the select options and tie the program to a Transaction Code
• Setup an Excel file on a share drive, and the user simply runs the program and it pulls the entries and auto-generates their report.

 

Program Walkthrough

Let’s walk through the program and see how it works…

Note, the S_BOOKINGS related tables are part of SAP’s SFLIGHT demo application. If your system doesn’t have data in these tables, there is a standard SAP program to create data called “SAPBC_DATA_GENERATOR”. You can read more about it here:

https://help.sap.com/doc/saphelp_nw70/7.0.31/en-US/cf/21f304446011d189700000e8322d00/content.htm?no_cache=true

The first screen has only the input file for the Excel Spreadsheet with a “.xlsx” extension:

We will read the spreadsheet with the following entries:

Our CDS View is “S_BOOKINGS”.

When executing for this spreadsheet, we get the following 4 Select Options in a popup window:

• Airline = CARRIERID
• Flight Date = FLIGHTDATE
• Customer Number = CUSTOMERID
• Amount (loc.currncy) = LOCALCURRENCYAMOUNT

We will enter 2 values for “Airline” by clicking on the “Multiple Selection” arrow to the right of “Airline:”

Next, enter “AA” and “AZ” for 2 Airline codes:

Click Execute to get the ALV IDA results:

To validate my data results, I like to use the Filter option, which allows you to click on the search help, which contains only a unique list of items in your data set. To confirm that we only selected “AA” and “AZ” airlines, we can select the “Airline” column, and click on the “Filter” button:

When you click on the drop-down for “ID:”, you will only see 2 codes contained in your data set:

If you leave the select options blank, all data is selected. Not a problem with ALV IDA, which we’ll discuss later.

Here are some other sample reports you can try, by simply updating your spreadsheet:

 

ACDOCA – The new General Ledger table, a regular HANA table:

Popup Select Options:

Results:

 

MARA – Material Master table, a regular HANA table:

Popup Select Options:

Results:

There was a lot going on in the above, so let’s dive into the code and walkthrough it in detail…

 

HANA and CDS Views

Many ABAPers spent many years becoming highly skilled at performance turning – Runtime analysis, defining indexes, carefully designing our reports to select only on key fields, selecting only on those fields with indexes, etc.

…Ya, you don’t need that anymore…

For ABAPers, you must learn CDS Views. With the right CDS annotations, you can auto-generate an entire Fiori app with zero coding. There is a ton of excellent documentation out there, so I won’t go into detail, but this is your future, so time to learn it. This also means you must start using Eclipse, as this is the only way to create CDS Views. Point an ALV IDA grid to a CDS View, and you can select millions of records, and simply paginate and filter thru them. With ALV IDA and HANA, there is no longer a risk of selecting too many records into memory, and causing a runtime short dump. For this tutorial, when we select on the S_BOOKINGS CDS View, we are simply pre-filtering our data (with our select options) prior to rendering the ALV IDA grid.

It’s important to note, that it doesn’t have to be a CDS View for ALV IDA to paginate, etc. It can be any HANA table. You’ll notice on the system, most tables in S4 are now defined as HANA tables. For example, display the accounting GL Header table BKPF in SE11 and display the “Technical Settings > DB-Specific Properties”:

Column Store = HANA

Let’s look at the various ways to look at the definition for a CDS View and comparisons between Eclipse and SE11.

First, let’s look at S_BOOKINGS in SE11:

Click the “Display” button, and you will get the following message:

Let’s open up Eclipse, and see the CDS View there.

We have the following definition for our CDS View:

@AbapCatalog.sqlViewName: 'S_BOOKINGSV'
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'Flight Bookings'
define view S_Bookings as select from sbook
   association [1]    to tcurc            as _ForeignCurrency    on $projection.ForeignCurrencyCode =   _ForeignCurrency.waers
   association [1]    to tcurc            as _LocalCurrency      on $projection.LocalCurrencyCode   =   _LocalCurrency.waers
   association [1]    to t006             as _WeightUnit         on $projection.WeightUnit          =   _WeightUnit.msehi
   association [1]    to S_Flights        as _Flight on $projection.CarrierId =  _Flight.CarrierId 
                                                           and $projection.ConnectionId = _Flight.ConnectionId
                                                           and $projection.FlightDate = _Flight.FlightDate                                                        
  association [1]    to S_Customers      as _Customer   on $projection.CustomerId =   _Customer.CustomerId                                                                    
 {
 
 key carrid  as CarrierId, 
 key connid  as ConnectionId, 
 key fldate  as FlightDate, 
 key bookid  as BookId, 
  customid   as CustomerId, 
  custtype   as CustomerType, 
  smoker     as Smoker, 
  luggweight as LuggageWeight, 
  wunit      as WeightUnit, 
  invoice    as InvoiceId, 
  class      as Class, 
  
  @Semantics.amount.currencyCode: 'ForeignCurrencyCode'
  forcuram   as ForeignCurrencyAmount, 
  forcurkey  as ForeignCurrencyCode , 
  @Semantics.amount.currencyCode: 'LocalCurrencyCode'
  loccuram   as LocalCurrencyAmount, 
  loccurkey  as LocalCurrencyCode ,
  order_date as OrderDate, 
  counter    as Counter, 
  agencynum  as AgencyNumber, 
  cancelled  as Cancelled, 
  reserved   as Reserved, 
  passname, 
  passform, 
  passbirth,
  
  _ForeignCurrency,
  _LocalCurrency,
  _Flight,
  _WeightUnit,
  _Customer
}

Note there is an annotation at the first line that says:

@AbapCatalog.sqlViewName: 'S_BOOKINGSV'

Go back to SE11, and enter this View name:

Here, we can see the definition, DDL Source, etc.

From here, we can click on the Contents Button

…and display the entries like a regular SAP table or view:

Go back to SE11, and double-click on the “DDL Source”:

Here, you can see the DDL Definition, as we saw in Eclipse:

Now, let’s look at the data from Eclipse. Right-click on the S_BOOKINGS CDS View and go to the menu path “Open With > Data Preview”:

This opens a tab with a view of the data:

 

Method Tutorial

 Method Chaining and NEW

The program defines and uses a local class named “lcl_dynamic_sel”.

Before the NEW operator and method chaining, we would do something like this:

  data: l_obj    type ref to lcl_dynamic_sel,
        l_string type string.
  l_string = p_ifile.
  create object l_obj.
  l_obj->execute( l_string ).

We could simplify the above, with the following single statement:

NEW lcl_dynamic_sel( )->execute( CONV string( p_ifile ) ).

Notice that the lcl_dynamic_sel class doesn’t even need a constructor. We simply want to execute the program and any variables declared are unnecessary after the call. We can use the “NEW” statement, and there is no need for an interim variable to retain the instance of the class in l_obj. Also, we can use the conversion operator (CONV) inline, to turn the p_ifile parameter into a string, also without the need to declare a temporary variable.

 

Read Excel Data

If you want to utilize this method in your own program to read an Excel (xlsx) file, you can just plug the following into any program:

Method Definition:

      get_excel_data
        IMPORTING im_file         TYPE string
        RETURNING VALUE(et_table) TYPE REF TO data

Method Implementation:

  METHOD get_excel_data.
    DATA: lt_xtab TYPE cpt_x255,
          lv_size TYPE i.
    CALL METHOD cl_gui_frontend_services=>gui_upload
      EXPORTING
        filename   = im_file
        filetype   = 'BIN'
      IMPORTING
        filelength = lv_size
      CHANGING
        data_tab   = lt_xtab
      EXCEPTIONS
        OTHERS     = 1.
    IF sy-subrc NE 0.
      MESSAGE |Invalid File { im_file }| TYPE 'I'.
      RETURN.
    ENDIF.
    cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab    = lt_xtab
                                              im_size    = lv_size
                                    IMPORTING ex_xstring = DATA(lv_xstring) ).
    DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
                                                xdocument     = lv_xstring ).
    lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING worksheet_names = DATA(lt_worksheets) ).
    et_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
  ENDMETHOD. "get_excel_data

Usage:

    DATA: lo_excel    TYPE REF TO data.
    FIELD-SYMBOLS: <excel_table> TYPE table.

    lo_excel = get_excel_data( my_excel_file ).
    ASSIGN io_excel->* TO <excel_table>.

These standard SAP libraries do all of the heavy lifting for us and parse the xlsx, which is really a very complex xml file. Note that you can go thru all of the worksheets within the Excel workbook, and retrieve some or all by simply referencing in this call:

    lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING worksheet_names = DATA(lt_worksheets) ).
    et_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).

Simply reference lt_worksheets[ 2 ] to get the second worksheet into et_table, etc.

The method get_excel_data receives an Excel File name and path as an import parameter, and returns a generic data reference variable. After the call, you then assign it to a field symbol of type “table” and process your internal table. Since this method has a single “Returning” parameter, we can use it inline in our method call:

    "Retrieve the file and build the Select Options for the Pop-up...
    build_select_options( EXPORTING io_excel        = get_excel_data( im_file )
                          IMPORTING e_cds_view_name = DATA(lv_cds_view_name)
                                    et_selopts      = DATA(lt_select_options)
                                    e_cds_view      = DATA(lv_is_cds_view) ).

The method build_select_options, receives the results of the Excel file read into it’s import parameter io_excel.

Notice with the above, we can eliminate data declarations for the importing parameters, and create them on the fly with the following:

IMPORTING e_cds_view_name = DATA(lv_cds_view_name)
                                    et_selopts      = DATA(lt_select_options)
                                    e_cds_view      = DATA(lv_is_cds_view) ).

Now, we have lv_cds_view_name, lt_select_options and lv_is_cds_view declared and populated with data without declaring variables separately with a “Data:” statement. The compiler analyzes the build_select_options method at runtime and can determine the data types needed to automatically declare these variables for us.

 

Build Select Options

We will talk about the magical cl_ci_query_attributes=>generic program next, but first we need to setup an internal table that contains our select options to be passed to this program.

This code took a little bit of arm wrestling, so let’s walk through it…

First, we are receiving our internal table from the Excel Reader method:

    IF io_excel IS INITIAL. RETURN. ENDIF.
    ASSIGN io_excel->* TO <excel_table>.

We now have our rows and columns from the Excel file in <excel_table>.

Because we want to support SAP Tables, Views or CDS Views, we need to keep track of which one it is, so that we can properly create our ALV IDA grid later on.

Here is the method signature:

      build_select_options
        IMPORTING io_excel        TYPE REF TO data
        EXPORTING e_cds_view_name TYPE ddlname
                  et_selopts      TYPE ltty_selopt
                  e_cds_view      TYPE boolean.

e_cds_view is simply a flag that tells if it’s a CDS View. The parameter e_cds_view_name will contain the name of the Table, View or CDS View name. The parameter et_selopts will contain our dynamically generated Select Options for the Popup dialog.

The get_field_labels method will be called to retrieve the field labels that will be used for our Select Options on our Popup dialog. Read thru this method to see what’s going on, and notice that we need to retrieve the field labels differently for a CDS View (i.e. the @AbapCatalog.sqlViewName annotation). The key point for this method, is that CDS views and their related ABAP catalog names are stored in the table ddldependency.

To validate that the fields they entered in the spreadsheet, are actual fields in the Table/View/CDS View, we will store them in the internal table “lt_components”. The following code gets a list of fields:

    "Get all of the fields in the CDS View and process/validate...
    CREATE DATA lo_cds_data TYPE (<cds_view_name>).
    ASSIGN lo_cds_data->* TO <cds_struct>.
    DATA(lt_components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( <cds_view_name> ) )->components.

That last line of code is pretty cool, and you may want to keep it in your toolbox. A single line of code, that gets the information for all fields in a structure. Before the newer ABAP, this took quite a bit of programming. The lt_components internal table will now contain the following information (for CDS View S_BOOKINGS):

Simply Googling “SAP Dynamic Select Options”, I was able to find the following by Rich Heilman:

https://archive.sap.com/discussions/thread/1184924

I utilized this code to build the rest of this method and dynamically create the range tables needed for the Select Options (see below).

A couple of notes on the new ABAP Goodness:

String Templates:

I love string templates, and when SAP added them, it triggered some wonderful Groovy GString memories (yes, funny). I am very grateful that these were added to ABAP.

Old ABAP:

  data: l_string type string,
        l_cds  type ddlname.
        l_cds = 'BOGUS_CDS_VIEW'.
        concatenate 'Invalid CDS View, Table or View Name' l_cds into l_string
          separated by space.
        MESSAGE l_string TYPE 'I'.

New ABAP:

MESSAGE |Invalid CDS View, Table or View Name '{ <cds_view_name> }'.| TYPE 'I'.

Simply use the curly bracket notation to embed your variable within the string. Surround your string with pipes (|), and all literal spaces, etc. are taken into account and the string appears exactly as you type it. Anyone who has arm wrestled with spaces in the “CONCATENATE” statement, should be very happy.

Dynamic Append:

Old ABAP:

  TYPES: BEGIN OF lty_flabel,
           name TYPE fieldname,
           text TYPE rstxtmd,
         END OF lty_flabel,
         ltty_flabel_tab TYPE SORTED TABLE OF lty_flabel WITH UNIQUE KEY name,
         BEGIN OF lty_selopt,
           name          TYPE string,
           text          TYPE rstxtmd,
           select_option TYPE sci_refdat,
         END OF lty_selopt,
         ltty_selopt TYPE STANDARD TABLE OF lty_selopt.
  DATA: lw_flabel     TYPE lty_flabel,
        lt_flabel_tab TYPE ltty_flabel_tab,
        lt_selopt_tab TYPE ltty_selopt,
        lw_selopt     TYPE lty_selopt,
        lo_data       TYPE REF TO data.
  READ TABLE lt_flabel_tab INTO lw_flabel WITH TABLE KEY name = 'BUKRS'.
  lw_selopt-name = 'BUKRS'.
  lw_selopt-text = lw_flabel-text.
  lw_selopt-select_option = lo_data.
  APPEND lw_selopt TO lt_selopt_tab.

New ABAP:

APPEND VALUE #( name          = <sel_fname>
                text          = lt_fdesc[ name = <sel_fname> ]-text
                select_option = REF #( <lfs_tab> ) )
                TO et_selopts.

No need for a temporary work area, just append the value into the table et_selopts, with the above statement. Also, we can use the bracket notation [ ] to read the record in lt_fdesc where name = <sel_fname>, without the need to declare a temporary work area variable. The compiler is able to recognize the type of record for the append, by analyzing the structure type for the table et_selopts. Since et_selopts is defined in our method signature, we can use the pound sign (#) and don’t need to specify the data type (i.e. lty_selopt).

Here is the complete method:

  METHOD build_select_options.
    DATA: lo_cds_data    TYPE REF TO data,
          lr_structdescr TYPE REF TO cl_abap_structdescr,
          lr_tabledescr  TYPE REF TO cl_abap_tabledescr,
          lr_datadescr   TYPE REF TO cl_abap_datadescr,
          lr_typedescr   TYPE REF TO cl_abap_typedescr,
          lt_selopts     TYPE abap_component_tab,
          lw_component   TYPE abap_componentdescr,
          lo_wa          TYPE REF TO data,
          lo_tab         TYPE REF TO data.
    FIELD-SYMBOLS: <cds_struct>  TYPE any,
                   <excel_table> TYPE table.

    IF io_excel IS INITIAL. RETURN. ENDIF.
    ASSIGN io_excel->* TO <excel_table>.

    "Cell B1 in the Excel spreadsheet must be a CDS View, Table or View Name...
    ASSIGN COMPONENT 2 OF STRUCTURE <excel_table>[ 1 ] TO FIELD-SYMBOL(<cds_view_name>).
    "Validate the CDS View, Table or View name...
    get_field_labels( EXPORTING im_tabview     = <cds_view_name>
                      IMPORTING et_field_labels = DATA(lt_fdesc)
                                e_cds_view      = e_cds_view ).
    IF lt_fdesc[] IS INITIAL.
      MESSAGE |Invalid CDS View, Table or View Name '{ <cds_view_name> }'.| TYPE 'I'.
      RETURN.
    ENDIF.
    "Get all of the fields in the CDS View and process/validate...
    CREATE DATA lo_cds_data TYPE (<cds_view_name>).
    ASSIGN lo_cds_data->* TO <cds_struct>.
    DATA(lt_components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( <cds_view_name> ) )->components.
    LOOP AT <excel_table> ASSIGNING FIELD-SYMBOL(<wa>).
      ASSIGN COMPONENT 2 OF STRUCTURE <wa> TO FIELD-SYMBOL(<sel_fname>).
      IF line_exists( lt_components[ name = <sel_fname> ] ).
        CLEAR: lr_structdescr, lr_tabledescr, lr_datadescr, lr_typedescr,
               lt_selopts[], lw_component, lo_wa, lo_tab.
        lw_component-name = 'SIGN'.
        lw_component-type ?= cl_abap_elemdescr=>get_c( p_length = 1 ).
        INSERT lw_component INTO TABLE lt_selopts.
        CLEAR lw_component.
        lw_component-name = 'OPTION'.
        lw_component-type ?= cl_abap_elemdescr=>get_c( p_length = 2 ).
        INSERT lw_component INTO TABLE lt_selopts.
        CLEAR lw_component.
        lw_component-name = 'LOW'.
        lw_component-type ?= cl_abap_elemdescr=>describe_by_name( |{ <cds_view_name> }-{ <sel_fname> }| ).
        INSERT lw_component INTO TABLE lt_selopts.
        CLEAR lw_component-name.
        lw_component-name = 'HIGH'.
        lw_component-type ?= cl_abap_elemdescr=>describe_by_name( |{ <cds_view_name> }-{ <sel_fname> }| ).
        INSERT lw_component INTO TABLE lt_selopts.
        lr_structdescr ?= cl_abap_structdescr=>create( lt_selopts ).
        CREATE DATA lo_wa TYPE HANDLE lr_structdescr.
        ASSIGN lo_wa->* TO FIELD-SYMBOL(<lfs_wa>).
        lr_datadescr ?= lr_structdescr.
        lr_tabledescr ?= cl_abap_tabledescr=>create( lr_datadescr ).
        CREATE DATA lo_tab TYPE HANDLE lr_tabledescr.
        ASSIGN lo_tab->* TO FIELD-SYMBOL(<lfs_tab>).
        APPEND VALUE #( name          = <sel_fname>
                        text          = lt_fdesc[ name = <sel_fname> ]-text
                        select_option = REF #( <lfs_tab> ) )
                        TO et_selopts.
      ELSEIF sy-tabix <> 1. "B1 is the CDS View Name, so only validate B2+...
        MESSAGE |Invalid field { <sel_fname> }| TYPE 'I'.
      ENDIF.
      UNASSIGN: <lfs_tab>, <lfs_wa>, <sel_fname>.
    ENDLOOP.
    e_cds_view_name = <cds_view_name>.
  ENDMETHOD.

Popup Selection Screen

Wow cl_ci_query_attributes=>generic, where have you been all my life?! A popup window class where we simply pass in an internal table of select-options. After our call to the build_select_options method, we call the popup window:

    DATA(lv_result) = cl_ci_query_attributes=>generic(
      EXPORTING
        p_name       = CONV #( sy-repid )
        p_title      = 'Enter Field Selections'
        p_attributes = lt_popup_selections
        p_display    = abap_false    " General Flag
    ).
    IF lv_result = 'X'. RETURN. ENDIF.

The lt_popup_selections table was not easily built, as you see, but after the call to the popup, we have data references to all of the Select Options that the user entered stored nicely in our internal table. From here, all we need to do is build the range objects and pass them to the ALV IDA class.

 

ALV IDA

Due to the nature of the HANA in-memory database and support for Big Data, SAP provides a new ALV grid called ALV IDA. With the regular ALV, you select the data into an internal table, then pass it to the ALV Grid where the data is displayed. If the dataset is too big, it crashes. With HANA, you simply provide a viewport to a HANA Table/View/CDS View and the database handles the pagination and filtering. The user only sees a subset of the data, as they page thru it. Upon paging, sorting or filtering thru the data, a new database call is made. Because it’s fast, the user doesn’t notice that it’s actually pushing down a new SQL call to the database, then returning the results to their viewable page. For more information, just do a search on ALV IDA, and there is a ton of documentation and tutorials out there. One big difference – you can’t run ALV IDA in the background as a batch job.

Here is the code we have to render our ALV IDA Grid:

    "Our selections are now in lt_select_options via pointers from lt_popup_selections.
    "Next, display the Table/View/CDS View in ALV with IDA (for HANA)...
    IF lv_is_cds_view = abap_true.
      DATA(lo_alv_cds) = cl_salv_gui_table_ida=>create_for_cds_view( iv_cds_view_name = CONV dbtabl( lv_cds_view_name ) ).
    ELSE.
      DATA(lo_alv) = cl_salv_gui_table_ida=>create( iv_table_name = CONV dbtabl( lv_cds_view_name ) ).
    ENDIF.
    DATA(lo_collector) = NEW cl_salv_range_tab_collector( ).
    LOOP AT lt_select_options INTO DATA(lw_sel).
      ASSIGN lw_sel-select_option->* TO FIELD-SYMBOL(<range_table>).
      IF <range_table> IS NOT INITIAL.
        lo_collector->add_ranges_for_name( iv_name = lw_sel-name it_ranges = <range_table> ).
      ENDIF.
      UNASSIGN <range_table>.
    ENDLOOP.
    lo_collector->get_collected_ranges( IMPORTING et_named_ranges = DATA(lt_name_range_pairs) ).
    IF lv_is_cds_view = abap_true.
      lo_alv_cds->set_select_options( it_ranges = lt_name_range_pairs ) .
      lo_alv_cds->fullscreen( )->display( ) .
    ELSE.
      lo_alv->set_select_options( it_ranges = lt_name_range_pairs ) .
      lo_alv->fullscreen( )->display( ) .
    ENDIF.

 

Notice that we make a different call when it’s a CDS View, however, our range collector can be the same for both. The 2 methods are:

• CDS View: cl_salv_gui_table_ida=>create_for_cds_view
• Table or View: cl_salv_gui_table_ida=>create

 

Complete Program

REPORT z_dynamic_select.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE fil_lbl.
PARAMETERS: p_ifile TYPE localfile DEFAULT 'c:\1\SeloptFile.xlsx'.
SELECTION-SCREEN END OF BLOCK b1.

CLASS lcl_dynamic_sel DEFINITION CREATE PUBLIC FINAL.
  PUBLIC SECTION.
    CONSTANTS: c_excel TYPE string VALUE 'Excel files(*.xlsx)|*.xlsx'.
    CLASS-METHODS:
      select_file
        IMPORTING i_filename     TYPE string
                  i_type         TYPE string
        RETURNING VALUE(re_file) TYPE localfile.
    METHODS:
      execute
        IMPORTING im_file TYPE string.
  PRIVATE SECTION.
    TYPES: BEGIN OF lty_flabel,
             name TYPE fieldname,
             text TYPE rstxtmd,
           END OF lty_flabel,
           ltty_flabel_tab TYPE SORTED TABLE OF lty_flabel WITH UNIQUE KEY name,
           BEGIN OF lty_selopt,
             name          TYPE string,
             text          TYPE rstxtmd,
             select_option TYPE sci_refdat,
           END OF lty_selopt,
           ltty_selopt TYPE STANDARD TABLE OF lty_selopt.
    METHODS:
      get_excel_data
        IMPORTING im_file         TYPE string
        RETURNING VALUE(et_table) TYPE REF TO data,
      get_field_labels
        IMPORTING im_tabview      TYPE string
        EXPORTING et_field_labels TYPE ltty_flabel_tab
                  e_cds_view      TYPE boolean,
      build_select_options
        IMPORTING io_excel        TYPE REF TO data
        EXPORTING e_cds_view_name TYPE ddlname
                  et_selopts      TYPE ltty_selopt
                  e_cds_view      TYPE boolean.
ENDCLASS. "lcl_dynamic_sel

CLASS lcl_dynamic_sel IMPLEMENTATION.
  METHOD execute.
    DATA: lt_popup_selections TYPE sci_atttab.

    "Retrieve the file and build the Select Options for the Pop-up...
    build_select_options( EXPORTING io_excel        = get_excel_data( im_file )
                          IMPORTING e_cds_view_name = DATA(lv_cds_view_name)
                                    et_selopts      = DATA(lt_select_options)
                                    e_cds_view      = DATA(lv_is_cds_view) ).
    IF lv_cds_view_name IS INITIAL. RETURN. ENDIF.
    IF lt_select_options[] IS INITIAL.
      MESSAGE |No selection fields specified for '{ lv_cds_view_name }'.| TYPE 'I'.
      RETURN.
    ENDIF.

    "Display a pop-up with the Selections from the Excel Spreadsheet...
    LOOP AT lt_select_options ASSIGNING FIELD-SYMBOL(<selopt>).
      APPEND VALUE #( ref  = <selopt>-select_option
                      kind = 'S'
                      text = <selopt>-text )
                      TO lt_popup_selections.
    ENDLOOP.
    DATA(lv_result) = cl_ci_query_attributes=>generic(
      EXPORTING
        p_name       = CONV #( sy-repid )
        p_title      = 'Enter Field Selections'
        p_attributes = lt_popup_selections
        p_display    = abap_false    " General Flag
    ).
    IF lv_result = 'X'. RETURN. ENDIF.

    "Our selections are now in lt_select_options via pointers from lt_popup_selections.
    "Next, display the Table/View/CDS View in ALV with IDA (for HANA)...
    IF lv_is_cds_view = abap_true.
      DATA(lo_alv_cds) = cl_salv_gui_table_ida=>create_for_cds_view( iv_cds_view_name = CONV dbtabl( lv_cds_view_name ) ).
    ELSE.
      DATA(lo_alv) = cl_salv_gui_table_ida=>create( iv_table_name = CONV dbtabl( lv_cds_view_name ) ).
    ENDIF.
    DATA(lo_collector) = NEW cl_salv_range_tab_collector( ).
    LOOP AT lt_select_options INTO DATA(lw_sel).
      ASSIGN lw_sel-select_option->* TO FIELD-SYMBOL(<range_table>).
      IF <range_table> IS NOT INITIAL.
        lo_collector->add_ranges_for_name( iv_name = lw_sel-name it_ranges = <range_table> ).
      ENDIF.
      UNASSIGN <range_table>.
    ENDLOOP.
    lo_collector->get_collected_ranges( IMPORTING et_named_ranges = DATA(lt_name_range_pairs) ).
    IF lv_is_cds_view = abap_true.
      lo_alv_cds->set_select_options( it_ranges = lt_name_range_pairs ) .
      lo_alv_cds->fullscreen( )->display( ) .
    ELSE.
      lo_alv->set_select_options( it_ranges = lt_name_range_pairs ) .
      lo_alv->fullscreen( )->display( ) .
    ENDIF.
  ENDMETHOD.

  METHOD select_file.
    re_file = cl_openxml_helper=>browse_local_file_open(
      iv_title      = 'Select File'
      iv_filename   = i_filename
      iv_extpattern = i_type ).
  ENDMETHOD.                    "select_file

  METHOD get_excel_data.
    DATA: lt_xtab TYPE cpt_x255,
          lv_size TYPE i.
    CALL METHOD cl_gui_frontend_services=>gui_upload
      EXPORTING
        filename   = im_file
        filetype   = 'BIN'
      IMPORTING
        filelength = lv_size
      CHANGING
        data_tab   = lt_xtab
      EXCEPTIONS
        OTHERS     = 1.
    IF sy-subrc NE 0.
      MESSAGE |Invalid File { im_file }| TYPE 'I'.
      RETURN.
    ENDIF.
    cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab    = lt_xtab
                                              im_size    = lv_size
                                    IMPORTING ex_xstring = DATA(lv_xstring) ).
    DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
                                                xdocument     = lv_xstring ).
    lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
      IMPORTING worksheet_names = DATA(lt_worksheets) ).
    et_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
  ENDMETHOD. "get_excel_data

  METHOD get_field_labels.
    DATA: lw_dtel  TYPE rsddtel.
    "Retrieve the @AbapCatalog.sqlViewName for the CDS View, then
    "build a table with field name and the medium text label...
    SELECT l~fieldname, l~rollname INTO TABLE @DATA(lt_elements) FROM dd03l AS l
      JOIN  ddldependency AS d ON l~tabname = d~objectname
      WHERE d~ddlname = @im_tabview AND d~state = 'A' AND d~objecttype = 'VIEW'.
    IF sy-subrc <> 0.
      "Not a CDS View, so just retrieve the data elements...
      SELECT fieldname rollname INTO TABLE lt_elements FROM dd03l
        WHERE tabname = im_tabview.
    ELSE.
      e_cds_view = abap_true.
    ENDIF.
    LOOP AT lt_elements INTO DATA(lw_elem).
      CLEAR lw_dtel.
      CALL FUNCTION 'RSD_DTEL_GET'
        EXPORTING
          i_dtelnm       = lw_elem-rollname
        IMPORTING
          e_s_dtel       = lw_dtel
        EXCEPTIONS
          dtel_not_found = 1
          doma_not_found = 2
          illegal_input  = 3
          OTHERS         = 4.
      IF sy-subrc <> 0. CONTINUE. ENDIF.
      INSERT VALUE lty_flabel( name = lw_elem-fieldname
                               text = lw_dtel-txtmd ) INTO TABLE et_field_labels.
    ENDLOOP.
  ENDMETHOD.

  METHOD build_select_options.
    DATA: lo_cds_data    TYPE REF TO data,
          lr_structdescr TYPE REF TO cl_abap_structdescr,
          lr_tabledescr  TYPE REF TO cl_abap_tabledescr,
          lr_datadescr   TYPE REF TO cl_abap_datadescr,
          lr_typedescr   TYPE REF TO cl_abap_typedescr,
          lt_selopts     TYPE abap_component_tab,
          lw_component   TYPE abap_componentdescr,
          lo_wa          TYPE REF TO data,
          lo_tab         TYPE REF TO data.
    FIELD-SYMBOLS: <cds_struct>  TYPE any,
                   <excel_table> TYPE table.

    IF io_excel IS INITIAL. RETURN. ENDIF.
    ASSIGN io_excel->* TO <excel_table>.

    "Cell B1 in the Excel spreadsheet must be a CDS View, Table or View Name...
    ASSIGN COMPONENT 2 OF STRUCTURE <excel_table>[ 1 ] TO FIELD-SYMBOL(<cds_view_name>).
    "Validate the CDS View, Table or View name...
    get_field_labels( EXPORTING im_tabview     = <cds_view_name>
                      IMPORTING et_field_labels = DATA(lt_fdesc)
                                e_cds_view      = e_cds_view ).
    IF lt_fdesc[] IS INITIAL.
      MESSAGE |Invalid CDS View, Table or View Name '{ <cds_view_name> }'.| TYPE 'I'.
      RETURN.
    ENDIF.
    "Get all of the fields in the CDS View and process/validate...
    CREATE DATA lo_cds_data TYPE (<cds_view_name>).
    ASSIGN lo_cds_data->* TO <cds_struct>.
    DATA(lt_components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( <cds_view_name> ) )->components.
    LOOP AT <excel_table> ASSIGNING FIELD-SYMBOL(<wa>).
      ASSIGN COMPONENT 2 OF STRUCTURE <wa> TO FIELD-SYMBOL(<sel_fname>).
      IF line_exists( lt_components[ name = <sel_fname> ] ).
        CLEAR: lr_structdescr, lr_tabledescr, lr_datadescr, lr_typedescr,
               lt_selopts[], lw_component, lo_wa, lo_tab.
        lw_component-name = 'SIGN'.
        lw_component-type ?= cl_abap_elemdescr=>get_c( p_length = 1 ).
        INSERT lw_component INTO TABLE lt_selopts.
        CLEAR lw_component.
        lw_component-name = 'OPTION'.
        lw_component-type ?= cl_abap_elemdescr=>get_c( p_length = 2 ).
        INSERT lw_component INTO TABLE lt_selopts.
        CLEAR lw_component.
        lw_component-name = 'LOW'.
        lw_component-type ?= cl_abap_elemdescr=>describe_by_name( |{ <cds_view_name> }-{ <sel_fname> }| ).
        INSERT lw_component INTO TABLE lt_selopts.
        CLEAR lw_component-name.
        lw_component-name = 'HIGH'.
        lw_component-type ?= cl_abap_elemdescr=>describe_by_name( |{ <cds_view_name> }-{ <sel_fname> }| ).
        INSERT lw_component INTO TABLE lt_selopts.
        lr_structdescr ?= cl_abap_structdescr=>create( lt_selopts ).
        CREATE DATA lo_wa TYPE HANDLE lr_structdescr.
        ASSIGN lo_wa->* TO FIELD-SYMBOL(<lfs_wa>).
        lr_datadescr ?= lr_structdescr.
        lr_tabledescr ?= cl_abap_tabledescr=>create( lr_datadescr ).
        CREATE DATA lo_tab TYPE HANDLE lr_tabledescr.
        ASSIGN lo_tab->* TO FIELD-SYMBOL(<lfs_tab>).
        APPEND VALUE #( name          = <sel_fname>
                        text          = lt_fdesc[ name = <sel_fname> ]-text
                        select_option = REF #( <lfs_tab> ) )
                        TO et_selopts.
      ELSEIF sy-tabix <> 1. "B1 is the CDS View/Table/View Name, so only validate B2+...
        MESSAGE |Invalid field { <sel_fname> }| TYPE 'I'.
      ENDIF.
      UNASSIGN: <lfs_tab>, <lfs_wa>, <sel_fname>.
    ENDLOOP.
    e_cds_view_name = <cds_view_name>.
  ENDMETHOD.
ENDCLASS. "lcl_dynamic_sel

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_ifile.
  p_ifile = lcl_dynamic_sel=>select_file( i_filename = CONV string( p_ifile )
                                i_type     = lcl_dynamic_sel=>c_excel ).

INITIALIZATION.
  %_p_ifile_%_app_%-text = 'Excel SelOpt File'.
  fil_lbl = 'Excel Input File'.

START-OF-SELECTION.
  NEW lcl_dynamic_sel( )->execute( CONV string( p_ifile ) ).

 

You may be wondering, wouldn’t it be nice if you didn’t have to read an Excel file every time you run the report? See version 2 of this program, with the ability to store and maintain your report settings in a table, without the need to re-import the Excel file each time.

References

ALV IDA Documentation:

https://help.sap.com/viewer/b1c834a22d05483b8a75710743b5ff26/7.4.19/en-US/efeb734c8e6f41939c39fa15ce51eb4e.html

Rich Heilman – Dynamic Select Options:

https://archive.sap.com/discussions/thread/1184924