Simple Program to view tables in single view
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.
Since you've asked for a feedback... Not a bad blog but here are my notes:
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.
Thanks for your feedback.
I put the program at below location for reference.
https://github.com/humanusa/ABAP/blob/master/ZTOOL_TABLE_VIEW_updated.txt
Hope it helps.
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
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.
Nice work,Keep blogging. Add SALV flavour to it.
best regards
Mohinder
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.
So you are presenting a half-baked program & want the users to plug the gaps?
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.
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.
Thanks Sandra - that sounds very useful.
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?.
(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!
Nope, doesn't work. You need to define the fields statically 🙂 (Ref.: SAP Documentation)
Tbh, there's no need for RTTC here. Check this code snippet
I think this would be a more elegant solution. 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?
For RTTC also we need to repeat it for the number of tables you want to show. But above is more elegant solution.
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.
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
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.