Product Information
Dynamic Selection Screen with ALV IDA and Excel
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:
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:
Rich Heilman – Dynamic Select Options:
Very nice demo.
Is there any real use case for the report or was it implemented just for getting experienced with the new features?
I can see a use case where my internal customers would rather load a spreadsheet than SAP. Some of them download the Excel and then change it. They would want to upload after that. It would be interesting to use on my next project just to see how pieces of it work out for me. It could speed up my development time.
For that I would recommend using ABAP2XLSX, but I don't see how much this scenario (Dynamic Selection Screen imported from Excel and ALV IDA) may help there.
I love ABAP2XLSX! It's one of the things I try to import in a new system. However, ABAP2XLSX only goes one way. (At least that's all I've used it for) If you don't have that code - it is available in Github. Many thanks to Ivan Femia and all the others who worked on this project.
It looks like this would take generic selects from my Excel - that would mean I would only retrieve the data from the SAP system if I wanted to replace the Excel data with the latest SAP data. At least that's how I envision it. Remember I'm only using part of the code.
Then the new version of the report would be displayed. The data that is refreshed from the SAP system, and the data pulled from the Excel spreadsheet.
There are things in the Excel document that the user can never change. (ABAP2XLSX is the download - I'll protect some cells.) Depending on what is changed they may want to keep that value when I re-display the data or they might want to replace it again - yes, I'll create an ALV with some fields editable.
That's just my morning thoughts. So I could be missing something. I also need to think it through more before I create the app. Excel and Fiori do not play well with each other. The excel can't be displayed on the screen. But I have been able to successfully display an ALV.
I love to make my Excel documents look better - and it has word wrap! Something that I haven't been able to do easily in an ALV.
And yes, this does showcase some of the new things we can do.
First, ABAP2XLSX, despite its name, does include also a reader (XLSX2ABAP) 🙂
Regarding your project, selecting data from SAP into Excel is indeed a common requirement (There are even some products which do exactly that).
However, in this demo program, it's the other way around: Both input criteria and selection results are displayed in SAP GUI. Excel is only using for selection of input fields (not the criteria itself). I wonder if there is a real use case for it.
I'll have to look at ABAP2XLSX a little better. I like the fact that it will read the Excel. Thank you!
It does 🙂
Check demo 15 as example.
Thanks Shai. The more you think about it, the more use cases may come to mind. But yes, was originally intended to demonstrate some of the cool libraries available. Personally, I've had many use cases for the various pieces of this, the dynamic select options being a frequently used one. The Excel piece is simply because developers seem to always be looking for this, so I included it. I love the ABAP2XLSX library, and have used it many times over the years, but good luck installing it at a large, very secure SAP customer who refuses to install SAPLINK.
One very simple use case, if you have a CDS View Expert Developer, this is a very quick way to simply point to their CDS View, embed some hardcoded select-options, and have a fully functional report in minutes, non-Fiori, SAPGUI style.
This is a great blog that throws up a number of interesting possibilities. I will certainly try out and hopefully make use of the dynamic selection-screen functionality.
With regard to class CL_FDT_XL_SPREADSHEET I recently considered using this in a project but I was deterred by SAP note 2468709 which states it is designed for use in BRFplus only and should not be used in custom programs.
Regards, JB