Skip to Content

A lot of times we need to view data from different tables in a single view to perform analysis.

It could be analysis for development or production. In those scenerios we can create quick SAP Query – but problem with Query is that if two tables are not joined you cannot view data from two more more independent tables.

In those cases we can use the below report which will fetch data from different tables and present it in single ALV or Browser page. The report logic is as given below.

Selection screen

The selection screen would consist of 5-6 different set of fields for table name and the where clause.

If you want data from maximum 5 tables you can have 10 fields – one for table name and one for where clause as given below.

You can add buttons to Copy Where to all, Delete All where clauses etc.

You can also create Join checkbox to join two tables and fetch common data.

 

Dynamic Itab creation

Then using RTTC you can create the internal table dynamically for the input table name on selection screen as given below.

 

      lo_struct ?= cl_abap_typedescr=>describe_by_name( p_tab ).

      lt_comp  = lo_struct->get_components( ).

      APPEND LINES OF lt_comp TO lt_tot_comp.
 

      lo_new_type = cl_abap_structdescr=>create( lt_tot_comp ).

      lo_new_tab = cl_abap_tabledescr=>create(

                  p_line_type  = lo_new_type

                  p_table_kind = cl_abap_tabledescr=>tablekind_std

                  p_unique     = abap_false ).

   

      CREATE DATA w_tref TYPE HANDLE lo_new_tab.

      ASSIGN w_tref->* TO <dyn_tab1>.

 

You need to declare field symbol <dyn_tab1> as many times as the number of tables allowed on selection screen.

Fetching data from tables

You can fetch data from tables from selection screen using below format giving dynamic table name and where clause.

  SELECT * FROM (p_tab) UP TO 100 rows

    INTO TABLE p_itab

  WHERE (p_where)   .

In case of Join the Select query will look like this – you need to do that based on Join checkbox.

SELECT * FROM (p_tab) UP TO 100 rows
 INTO TABLE p_itab2
FOR ALL ENTRIES IN p_itab1
 WHERE (p_where).

 

Calling browser functionality

You can use the cl_abap_browser class to write the fetched data into it. Declaration is given below.

     oref TYPE REF TO cl_demo_output_stream,

     output_stream TYPE xstring.

 

To add data to it use below.

   oref->write_data( p_tab ).

   oref->write_data( p_table ).

 

After all the data is fetched you can use below statement to display browser window with the tables all in single page.

    output_stream = oref->close( ).

 

You can use REUSE_ALV_BLOCK_LIST_APPEND, REUSE_ALV_FIELDCATALOG_MERGE and REUSE_ALV_BLOCK_LIST_DISPLAY to display it in standard ALV format.

 

Example output in ALV format

 

 

Example Browser format

 

 

Uses

  • It is useful to see multiple table contents in single window so you can analyze data.
  • For eg. to see all the information about a message type you can check tables – EDP13, EDP21 and EDP12 in a single window to get process code, FM etc.
  • You can create variants with multiple tables to see various information.
  • The program can be expanded to use Joins and get data from multiple tables.

Caution

The program should only be used in Development or Quality environment as there can be malicious code injected using SQL. Or the program should be modified to check the WHERE clause for malicious code before executing.

 

Let me know your feedback if any.

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  1. Jelena Perfiljeva

    Since you’ve asked for a feedback… Not a bad blog but here are my notes:

    1. It does not have references to any external resources. E.g. what is RTTC? Not every ABAPer knows probably. Even if you have not read and used this blog on RTTC from 2014 and this blog on CL_DEMO_OUTPUT from 2016, it makes me suspect you have not searched before posting. We should always search first before posting SCN blogs, to make sure the same material has not been covered already. Even if someone feels they have more to add to what’s already been said, it’s best to include a reference. E.g. “I read this blog [link] and felt it did not cover X that I’d like to talk about here”.
    2. It is 2017 and you are suggesting to use REUSE_ALV… C’mon now.
    3. Even though you’ve explained a possible use case (and thank you for that!) I’m still skeptical it’s worth writing such program. Personally, I have no problem opening several SE16 sessions and if I need to look at some disjointed data on the same screen I can take a screenshot or copy into Excel.
    4. Usage of the word “browser” is confusing here. I was expecting to see like an IE or Chrome screen but it is just some SAP viewer for HTML data (not sure if there is an official term for it). The class name a bit misleading here IMHO but you could’ve clarified it that it’s not the “browser browser”.
    5. You couldn’t have added a selection text? That speaks poorly of your attention to details. Just like “never trust a skinny chef”, I’m usually immediately skeptical of the content that follows in such cases.
    6. The blog is called “sample program” but there is no actual executable program included. This might be disappointing to the copy-pasting crowd. And others could probably put together the same fragments from the other two blogs I’ve mentioned.
    7. There are some other technical items like why do you have JOIN option for what is designed to show “disjointed” data and then you use FAE; hard-coded 100 rows limitation; variable naming, etc. But I’ll let more qualified people to speak to that.

    Overall it’s nice of you to share but, going forward, please see how your blogs could work better with the existing SCN content and make sure your code follows the current best ABAP practices.

    Thank you.

    (5) 
  2. Michelle Crapo

    Hi Deepak Hada,

    First of all great blog!    It was your first and I have to say good job!!!!!   The first blog is the hardest.   Questions to yourself about if what you want to share is relevant.

    Me – I think it’s all relevant, and I can usually learn something from a blog I read.

    So is it relevant?   Sure.   Why not? It’s a utility program that some of us will decide will not help.   But it may help ONE person.   And what do I always say?  If you help one person it was worth the effort.

    Also you’re going to learn from the comments.   Jelena has some great points!   I’m sure you can learn from them.    And yes you really do need to use the OO of ALV.   It’s quick and easy to use.  That might be something you’d like to look up.

    I know you are showing us your quick and dirty program.

    Thank you and keep blogging!

    MIchelle

    (1) 
    1. deepak hada Post author

      Thanks, my first blog so still learning 🙂

      Yes it is a very niche utility to analyze data in development or quality preferably. I use it get idoc details in one page when I dont want to go to all the idoc related transactions to get the complete picture. It is a bit of old-school tool if you know your tables well. Although there are more advanced tools (ADT) on eclipse and now you can create better views in Eclipse quickly – this could still be useful if you are on old SAP version.

      (1) 
    1. deepak hada Post author

      Thanks :).

      I am not sure if SALV allows multiple internal tables to be added to a single page. CL_ABAP_BROWSER is perfect for this kind of output. I just added about REUSE_ALV_BLOCK_LIST_APPEND since I had used it before discovering CL_ABAP_BROWSER. Let me know if multi itabs is still possible with SALV.

      (1) 
  3. Suhas Saha

    the program should be modified to check the WHERE clause for malicious code before executing.

    So you are presenting a half-baked program & want the users to plug the gaps?

    (0) 
    1. deepak hada Post author

      I don’t know how actual SQL injection is done. If anyone wants to make the program robust against such attacks – it was just an advice.

      (0) 
      1. Sandra Rossi

        That was a good advice. For information, there is a helper class named CL_ABAP_DYN_PRG for checking the variables in a SELECT statement. There is some good information in the chapter “SQL injection” in the ABAP documentation.

        (1) 
  4. Joachim Rees

    Hi deepak,

    congratulations on your first blog!

    I think you have found a solution useful to you and wanted to share that with others, that’s great!
    It’s also a good idea that you asked for feedback, and great that you already got some of it!

    When reading you blog, a thought occurred to me: Are RTTS (Run time Type Services) are still realy needed this days in such a case? Couldn’t you just use inline declaration to create exactly the type you need?.

      SELECT * FROM (p_tab) UP TO 100 rows
        INTO TABLE @DATA(p_itab)  "<-- inline decalration
      WHERE (p_where)   .
    

     

    (Note: I have not tried this or extensively thought it through; I still think the though is still worth sharing. …and if it is to find out that I’m wrong!)

     

    All the best and keep on learning an blogging!

    Joachim

     

    [Edit:] PS: One more thought: I actually do like that you don’t provide a copy-and-paste full report, but instead highlighting and explaining the code-parts relevant to your topic!

    (2) 
    1. Suhas Saha

      Tbh, there’s no need for RTTC here. Check this code snippet

      PARAMETERS p_table TYPE tablenam DEFAULT 'T001' OBLIGATORY.
      
      START-OF-SELECTION.
      
        DATA: dref TYPE REF TO data.
      
        FIELD-SYMBOLS: <table> TYPE INDEX TABLE.
      
        CREATE DATA dref TYPE STANDARD TABLE OF (p_table).
        ASSIGN dref->* TO <table>.
      
        SELECT * FROM (p_table) UP TO 10 ROWS
          INTO TABLE @<table>.
      
        cl_demo_output=>display_data(
          value = <table>
          name  = CONV #( p_table )    " Name
        ).

       

      (3) 
      1. deepak hada Post author

        I think this would be a more elegant solution. Just need to repeat this for the number of tables you want on your output.

        (1) 
        1. Suhas Saha

          Just need to repeat this for the number of tables you want on your output.

          Didn’t get your point. You don’t need to do it if you use RTTC?

          (1) 
    2. deepak hada Post author

      Thanks for your words Joachim.

      I found the solution unique and hence thought of sharing. I was creating a lot of standalone reports which were extracting data from tables so this idea came to me :).

      I could not share the full code so tried to explain the relevant parts.

      (0) 
  5. Łukasz Pęgiel

    Hi Deepak,

    we have similar solution in our company, means a transaction in which you can easily prepare simple reports on a base of data selection from tables with or without joins. Actually in our case you can additionally put some ABAP into it and save the variants (aka reports) so they can be reused. Whole logic is to prepare at the end dynamic but strictly structured temporary program and then to run it.

    Although the solution was quite helpful to me in the past, I never liked to give such tool to be used by final users, even if the were able only to run variant. Such temporary programs can have performance problems (like all the others btw), which are harder to find and trace.

    Additionally if you’re at least on 7.40 SP8 then you can run SQL console in Eclipse and then you have simplest way to select data from DB or to check if your select fetches correct entries without running whole program.

     

    Cheers
    Łukasz

    (2) 
    1. deepak hada Post author

      That sounds like an interesting solution, yes but performance will need to be tweaked.

      One option would be to generate a bare bones report and then tweak it individually for performance and then use it.

      SQL console can be used yes, but above solution is more about seeing everything in a single page.

       

      (0) 

Leave a Reply