A custom report catalog for SAP NetWeaver BI

In this post I will describe how we developed a custom catalog of SAP BI reports for one of our projects. The purpose was to leverage the BW Metadata Repository and turn it into a central instrument that – at the same time – can be used by the business and is useful for IT.

The business part

The catalog lists all the reports in our production environment. For each report, it contains among other things a short description, a link to the full documentation set, a pointer to where the report can be found in the SAP portal and the name of the business owner.

The whole idea is to increase the level of self-service by the business and maximize the use of existing reports. When a user is looking for specific information, the short description allows him to identify potential helpful reports. From there, the user can browse through the documentation, try the report (provided he has access) or seek assistance from the business owner.

The IT part

The catalog also contains more technical information. For each report, it lists the web template, the query name and technical name (more than 1 possible per template!), the infoprovider type and the infoprovider’s name and technical name. We have also added several descriptive attributes (i.e. hierarchies) like e.g. the data source(s) used.

Since we have a large number of reports and we obviously intend to keep the list up to date, we wanted to collect as much information as possible in an automated way.

Our first idea was to export the metadata repository (transaction RSOR). The program RSO_REPOSITORY_EXCHANGE_XML seemed ideal for that. To execute it, run transaction se38, enter RSO_REPOSITORY_EXCHANGE_XML as the program name and press F8.

We got the best results by clicking on the “All Object with Details” button.

After you have provided a location (directory) for the output, lots of files will be created.

Although we found some of these files quite interesting, we believe that some fundamental information is missing in order to link the different objects (template-query-infoprovider) to each other.

So we started down another path, this time using transaction se11. Below I will list all the tables for which we ran this transaction. Enter the name of each table after the “Database table” prompt and click Display.

Next, press CTRL+SHIFT+F10 (Contents).

Clear the last 2 input boxes (Width of Output List and Maximum No. of Hits) to make sure you receive all information. Then press F8 (Execute).

Click Settings – User Parameters

and on the Data Browser Tab, select ALV Grid display.

Then press CTRL+SHIFT+F7 (Table Entry, List, Export, Spreadsheet).

For the format, choose All available Formats and then Excel (in Office 2003 XML Format). We tried other formats, but had best results with this one. You will notice that the files can get pretty big. However, if you open them in Excel 2007 and then save them in Excel’s most recent format, they will shrink substantially.

Once you have exported all tables listed below in this way, the last step is to link them all together. You can do this using your favorite database or if you don’t have one at hand, just use Excel (try VLOOKUP).

Get OBJID as Web Template from RSZWBTMPXREF T1
Get COMPID as Query Technical name from RSRREPDIR T2 on T1.OBJNM = T2.COMPUID
Get TXTLG as Query name from RSZELTTXT T3 on T1.OBJNM = T3.ELTUID
Get INFOCUBE as Infoprovider Technical name from RSRREPDIR T2 on T1.OBJNM = T2.COMPUID
Get TXTLG as Infoprovider name from RSDCUBET T4 on T2.INFOCUBE = T4.INFOCUBE
Get CUBETYPE as Infoprovider type from RSDCUBE T5 on T2.INFOCUBE = T5.INFOCUBE

If you know your way around in SAP and have the necessary privileges you may consider creating a view directly in SAP that does all this for you automatically.

The one part that is missing in this overview is the link to the portal. Since the portal uses its own schema (which can be in a different database), we decided to leave this part out for the first iteration. So currently this information is still filled in by hand. If we ever get to add this last bit, I will post a new article about it.