“Excel Style” Database Analytics with 1 Line of ABAP Code

The new ALV with IDA (Integrated Data Access)  is able to process a high number of records withouth negative impact on performance. this works for HANA but also for non-hana Databases (except for the Fuzzy Search feature which only works with column based tables)

basically the IDA reads only the records from the database, which are shown on the screen, as soon as the user scrolls down or defines a filter column, the database is reloading this slice of records.

(remember: the old alv was like select * into itab so loading all records into application server memory and from there to the gui)

this way i can show a big table like bseg or vbap with millions of records in a second:

and from there, the user can define filters, groups, sum/total columns, sorting, exel export etc

all this can be done with an abap report with only 1! line of code: (*method fullscreen is available from 7.40 SP5, IDA class is available from 7.40 SP0)

REPORT Z_ALV_HANA. cl_salv_gui_table_ida=>create(  iv_table_name = ‘VBAP‘ )->fullscreen( )->display( ).

i added some features to have a little more comfort: Dynamic Table Name, Title with number of records, possibility to Save ALV Layout Variants, Fuzzy Search

REPORT Z_ALV_IDA.

DATA layout  TYPE lvc_s_layo.

DATA columns TYPE lvc_t_fcat.

DATA column  TYPE lvc_s_fcat.

data lv_title type SYTITLE.

data: go_alv_display type ref to cl_salv_gui_table_ida.

DATA: ls_persistence_key TYPE if_salv_gui_layout_persistence=>ys_persistence_key.

data: lv_count type i.

parameters: p_tab(30) default ‘T000’.

parameters: p_sim type p length 2 decimals 1 default ‘0.8’.

parameters: p_search(50) lower case default ‘Walldorf’.

data: lo_alv type ref to IF_SALV_GUI_TABLE_IDA.

start-of-selection.

lo_alv = cl_salv_gui_table_ida=>create(  iv_table_name = p_tab ).

* create title test

   select count( * ) from (p_tab) into lv_count.

   write lv_count to lv_title. condense lv_title.

   concatenate p_tab ‘: number of records in table:’ lv_title  into lv_title separated by space.

   if p_search <> ‘ ‘.

   concatenate lv_title ‘,filtered by’ p_search into lv_title separated by space.

   endif.

lo_alv->display_options( )->set_title( lv_title ).   “#EC NOTEXT

* enable save of alv variants:

     concatenate sy–repid ‘_’ p_tab into ls_persistence_key–report_name.

*

     lo_alv->layout_persistence( )->set_persistence_options(

     EXPORTING

       is_persistence_key           = ls_persistence_key

       i_global_save_allowed        = ‘X’

       i_user_specific_save_allowed = ‘X’ “l_user_specific_save_allowed’

   ).

* Search Feature

if p_sim <> 0.

     lo_alv->text_search( )->set_field_similarity( p_sim ).

endif.

if p_search <> ‘ ‘.

     lo_alv->text_search( )->set_search_term( |{ p_search }| ).

endif.

     call method lo_alv->fullscreen( )->display( ).

to test the performance you can use table DD03L with 9 Millions of Records in an empty ERP System