Because I have answered this question about three times in the last months I decided to write a weblog which could help others having the same problem. The question I had to cope with was to find queries which contain a specific characteristic, key figure or are based on a certain InfoCube. Sometimes you also want to find queries which you have modified last. There is no special search engine within SAP NetWeaver BW which helps you finding these queries; normally you would have to search for them manually.
Because SAP stores all information in database tables I have written a (I think you can call it small?) program which extracts the information from these tables and displays the resulting queries and their corresponding InfoCube within an ALV grid.
Before I explain the source code of the program and the included function module I want to tell you something about the used tables and how you have to search for your queries. Unfortunately there is more than one table which you have to search for. Another problem when searching for queries is that characteristics and key figures as well as queries are not stored with their technical name but with a unique 25 characters long number. This number is (in most cases) not visible for you. Now what you have to do is first to get the correct unique id of your characteristic or key figure; second you are able to search for queries in the tables. Third you can provide other information like long text or InfoCube on which the queries are based and add it to the result set.
Tables
Now here are the tables which you have to use. The first one I want to mention is the table RSZSELECT. The short description of RSZSELECT says “Selection properties of an element”, in fact it stores a lot more information than I want to select here. I just use it for selection the unique ID (which is stored in column ELTUID) of the InfoObject you provided on the input screen. Because an InfoObject can be a key figure, too, you have to search table RSZRANGE to get the unique ID of your InfoObject if it is a key figure. The unique ID is stored in column name ELTUID, again.
The main table which stores information about queries and their elements is RSZELTXREF. It contains the information, which query exists of which key figures and characteristics. Unfortunately you only get the unique ID of your query which doesn’t help you much, so you have to select the technical name (which you provided in BEx Query Designer) out of table RSRREPDIR. Afterwards you can get the description of your query out of table RSZELTTXT and the username which created or modified the query out of database view V_CMP_JOIN. Except tbale RSZSELECT you can handle all tables within a (long) select statement using the INNER JOIN construct.
Program
My program is a small one consisting of the following parts:
- Selection screen for user input: Here you can enter values for InfoObject, InfoCube and Username.
- Performing a small check whether all necessary information is provided, otherwise a message will be displayed.
- Performing the search via a function module called ZWUL_QUERY_SEARCH.
- Displaying the result in an ALV grid using function module REUSE_ALV_GRID_DISPLAY
And that’s all what you have to do. Now here is the source code for the function module. I have added comments which hopefully help you understanding the source. I put the source code in a wiki so it can be reviewed, too, you can find it here: https://wiki.sdn.sap.com/wiki/display/BI/Function+module+to+search+for+queries+by+certain+elements:
Before you can activate your function module you have to add the following lines in the top include of your function group. These type pools are necessary to use BW data types.
type-pools:
rs, "BW global
rrms, "message server
rsz, "Qry definition global
rzx0, "Qry RFC interface
rzd1. "Qry definition database
The function module is called within a program called ZWUL_QUERY. Create your program and insert the source code I have added in another wiki: https://wiki.sdn.sap.com/wiki/display/BI/Searching+for+queries+by+certain+elements+-+covering+progra...
As you have seen in the first lines of the program source code there are a few variables which you have to enter. Because the variable name itself isn’t very meaningful provide texts for the variables in the menu in “Goto” – “Text elements” – “Selection texts”. I have used the following texts in my program (see below picture).
In the same menu on the tab “Text symbols” you can enter the description of both blocks I have added in the source code.
Now if you call your program ZWUL_QUERY you will get the following screen where you can enter your values. A search help for InfoCubes and usernames is provided, too.
In one of our test systems I have searched for InfoObject “0HDCNT_LAST” on InfoCube “0PA_C01” and got the following result list, displayed within an ALV grid.
2 Comments
