Let`s have a following situation: you have a colleague (or even a group of them), that can do SAP Query very well, but they`re no developers in any way. That means SAP Query is their only way how to “create” something “new”. That means that if you want to include them into the development process, unleash their potential for the company and/or help them build something bigger than they could build on their own, you must find a way how to build on top or around their queries.
This article is about the technical way how to build on top/ around queries and also day-dreaming how can help SAP Query enabled colleagues to be more efficient (I see it that way). It is possible that the topic is naïve for someone, who knows SAP Query well. I learnt it the hard way so I hope someone finds this article useful as usual.
This article can also attract attention of the SAP Query enabled folks, who would find the technical ABAP stuff boring, so I start with the day dreaming. How to do what I am describing in the first part comes in the second part (not a rocket science to figure that out, right…).
Day-dreaming: What can one accomplish with SAP Query
Again: I am not a SAP Query expert, so maybe the things I am going to suggest are something that Query folks do every day. In that case I would love to learn something via your comments below the article. I hope to teach something as well as learn something every time I blog on SDN.
Ideas how to use the output of the query:
- start a workflow for example for every item of the query output
- (in HR or CRM) you want to be flexible with selection but you have some heavy-weight process behind the data processing (you want to process bonuses every month and your HR colleagues can do queries, so you build them a program that reads people using their queries and then send every person on the list a bonus; you want to offer your customer some special sale so your “query-enabled” colleague prepares the data – so you send the offer only to customer that are likely to be interested – and your program does the formatting of the emails, recording whom you`ve sent the email etc.).
- You want to do some nice charting using the data; maybe using the Google Chart tools (I found a nice example how to do it in this blog).
- You want to build a powerful cockpit for you manager that would show all his favorite queries in one fancy launchpad with some nice drill-down options
- You want to generate a file from your data and send it via your XI/ PI
- You want to generate a fancy formatted Excel
- and/ or more…
Boring technical stuff: How to build around SAP Queries
The most important thing (actually this is the “trick” to make all described happen) is the function module called RSAQ_QUERY_CALL. With this function you can call a query, provide input using the query selection screen (only if you want), get the output of the query as a formatted table (no lists to memory, no spools, no dirty memory reading, just formatted output you can use very quickly for further processing) and continue with processing of your code.
Parameters of the function are pretty straight-forward, but let me explain briefly for better immediate orientation (without the system on in the second window): workspace (put G if you want the global one), query and user-group you will get from your query colleague (or from SQ01). You can either skip the selection screen (if you a) have a variant that provides enough information via VARIANT parameter or b) a query without input or c) you provide selection data via SELECTION_TABLE TABLES parameter). Because you want to get the data out of the query, set DATA_TO_MEMORY to ‘X’.
Do not forget to “remember” the LISTDESC TABLES parameter, which will describe the format of the output. Output table (REF_TO_LDATA) is created by a CALL METHOD cl_alv_table_create=>create_dynamic_table or an equivalent construct, so you need the description of the output to be able to further work with it.
Notes about how to work with the output description and convert it to ALV field catalog:
- If you want to see how that is done in SAP standard, you can check RSAQ_ALV function. Just put a breakpoint at the beginning and start a query from SQ01.
- Shortly: The code combines the information from the LISTDESC and calls RSAQ_READ_QREPORT_ALIAS_NAMES in order to get additional information. Output of the second function module contains the real TABLE and FIELD information. You can`t get the same information from the LISTDESC itself. Then you can query DD03L and get the information about the real data type (ROLLNAME) and then query DD04L.
- Performance note: check perform ddif_fieldinfo_get_single(rsaqddic) in the RSAQ_ALV, because that code heavily uses buffering. I don`t think you can do the same faster, so learn from there.
- An alternative would be to do everything yourself similarly to the following:
*&---------------------------------------------------------------------* *& Form convert_list_to_fcat *&---------------------------------------------------------------------* FORM convert_list_to_fcat USING p_list TYPE rsaqtldesc CHANGING p_fcat TYPE lvc_t_fcat. DATA ls_list LIKE LINE OF p_list. DATA es_fcat LIKE LINE OF p_fcat. LOOP AT p_list INTO ls_list. es_fcat-fieldname = ls_list-fname. es_fcat-outputlen = ls_list-folen. es_fcat-datatype = ls_list-ftyp. es_fcat-intlen = ls_list-flen. es_fcat-seltext = ls_list-fcol. es_fcat-scrtext_l = ls_list-fcol. es_fcat-scrtext_m = ls_list-fcol. es_fcat-scrtext_s = ls_list-fcol. APPEND es_fcat TO p_fcat. ENDLOOP. ENDFORM. "convert_list_to_fcat
If you want to read a OSS note related to the topic, search for 393160 SAP Query: Using queries.
Hint: it is easy to single test this function module via SE37, BUT(!!) when you do that, you won`t get the data back. Normally you get VALUE(REF_TO_LDATA) TYPE REF TO DATA, which does not work in the test frame call. As a workaround you can put a breakpoint on ENDFUNCTION.