Exploring SQL-Features using a demo report

Not sure if it’s helpful for others, but I think it’s for me and I’ll gladly share it with you here:

The combination of dynamically creating a target table for a select (using inline declaration) and the capability of cl_salv_table to take any table and just display it, makes it very easy to play around with select statements (e.g. exploring advanced OpenSQL-Features) and getting your results displayed in a report with very little overhead.

Those are the key statements:

Select ....

 into table @data(lt_data).

 CALL METHOD cl_salv_table=>factory

That’s the main idea of this blog: Create a little report once, then play around with the select, immediately seeing if and how things work.
And here’s my full report:

REPORT ZZ_SQL_play_report.


start-of-selection.
perform start_of_selection.




form start_of_selection.

*Select
SELECT

*Using constants
'Demo_Const' as demo_of_constant,
@abap_true as demo_another_const,

*Sy-fields
@sy-uname as current_user,
@sy-datum as current_date,

SNWD_BPA~BP_ID,
SNWD_BPA~BP_ROLE ,

*case
case SNWD_BPA~BP_ROLE
when '01' then 'Customer'
when '02' then 'Suplier'
else 'un-known'
end as bp_role_text,

*coalesce
 coalesce( SNWD_BPA~COMPANY_NAME, 'n/a' ) as Company_Name,
 snwd_ad~country,
 snwd_ad~city,
 snwd_ad~address_type,
 snwd_so~so_id,
 snwd_so~currency_code,
*coalesce
 coalesce( snwd_so~currency_code, 'n/a' ) as curr_code_coalesce,
*rounding
 round( snwd_so~gross_amount, 1 ) as gross_amount,
*case - multi-level
 case snwd_so~lifecycle_status
 when 'N' then 'new'
 when 'P' then
    case snwd_so~billing_status
        when 'P' then'paid'
        else 'pending'
        end
     else 'undefined'
     end
 as calculated_status,

*text functions
 snwd_ad~country && '-' &&  snwd_ad~city as country_city,
*combining sy-fields and concatenate
@sy-sysid && '/' && @sy-mandt as sys_info

*[further ideas here!] :-)

 FROM SNWD_BPA
 INNER JOIN snwd_ad
 on snwd_bpa~address_guid = snwd_ad~node_key
 left outer JOIN snwd_so on
 snwd_bpa~node_key = snwd_so~buyer_guid

 into table @data(lt_data).

*display
 perform display_data changing lt_data.

endform.



 FORM display_data USING pt_data TYPE ANY TABLE.

  DATA: lr_alv          TYPE REF TO cl_salv_table.
  DATA: lr_functions    TYPE REF TO cl_salv_functions_list,
        lr_layout       TYPE REF TO cl_salv_layout,
        ls_key          TYPE salv_s_layout_key.
  TRY.
      CALL METHOD cl_salv_table=>factory
        EXPORTING
          list_display   = if_salv_c_bool_sap=>false
        IMPORTING
          r_salv_table   = lr_alv
        CHANGING
          t_table        = pt_data
      .
    CATCH cx_salv_msg .
  ENDTRY.

* Layout
  lr_layout = lr_alv->get_layout( ).

  ls_key-report = sy-repid.
  lr_layout->set_key( ls_key ).
  lr_layout->set_default( abap_true ).
  lr_layout->set_save_restriction( if_salv_c_layout=>restrict_none ).

*Functions
  lr_functions = lr_alv->get_functions( ).
  lr_functions->set_all( abap_true ).

  CALL METHOD lr_alv->display.

ENDFORM.                    "display_data

Copy and paste and try it out for yourself, if you like!

(Notes:

– I created this on an NW7.51 System

– I think the SNWD*-Tables should be part of a default installation – if not, you can easily replace the select with tables fitting more to your daylie work.

)

How do you take on exploring new ABAP Features?

Joachim