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!
– 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?
Note: I like having a full featured ALV using form display_data, but to only display the result, one of course could also just use
and save a few lines that way.
thanks Joachim! I tried out your report and found it very helpful to understand these OpenSQL features
thanks for your kind feedback!
Great to learn my blog was helpful to you!
By the way:
The select also works perfectly in the SQL-Console of AdT / Eclipse:
[sorry, it seems I currently can't upload a screenshot! 🙁 ]
Another way to quickly try out new things™ !