Exploring SQL-Features using a demo report

[joachimrees1]

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