BEx Analyzer features – Properties for characteristics: Cumulate Values

Here I’m starting couple of posts about not very known features of BEx Analyzer. One of them is called “Cumulate Values”. It is available in context menu (right click) on characteristic’s cell while BEx report is opened in BEx Analyzer tool.

What it does is following. It cumulates a value of KF for specific characteristics shown in the report. This works without considering what aggregation type is defined for particular KF.

Here’s example of report’s output. There is KF called “Total time” displayed in the report. Characteristics Calendar Day is displayed as well in the columns of the report. Without “Cumulate Values” feature enabled report looks like following:

To enable the feature a right click is performed on the characteristics Calendar Day and menu item Properties is chosen. On next pop-up windows the feature is enabled.

This is hoe the report looks like when it is ON. Value of the KF in first column stays the same as the feature wouldn’t be enabled. But value of 2^nd and other subsequent columns are cumulating values of all predecessors’ columns:

Online documentation:

Properties of characteristics

How to recognize between 3.x and 7.x BEx query?

SAP BW in its versions starting with 7.0 introduced many changes to BEx queries.  This resulted in a situation that there are two types of queries. First there are “old” or 3.x based queries. These were created with BEx Query Designer 3.x tool. On other hand we have “new” or 7.x based queries created in BEx Query Designer 7.x tool.

As a side note sometimes in SAP documentation they refer to 3.x BEx as “Business Explorer 3.5” or “BI BEx Suite 3” or “NW 2004” and to 7.x as “NW 2004s” or “BI BEx Suite 7” or “BEx 7”.

SAP advised to perform 3.x based queries migration to 7.x fashion right after the 7.x version popped up. However customers were neglecting it. The fact is that even in SAP BW 7.3 the 3.x based queries still run without problems. In version SAP BW 7.4 the BEx tools (Query Designer and Analyzer) of version 3.x are not available but Analytic Engine of BW 7.4 can still execute these queries.

Even we still can run the 3.x queries in BW 7.4 it is highly advisable to migrate it finally at this point of time. The BEx queries have gotten many new features in BEx 7.x. Also new BEx Query Designer of 7.x was redesigned so therefore it makes no sense to keep the query in old fashion.

Now how to recognize what queries are in the SAP BW system which the 3.x based? Version of the BEx tool that was used to edit particular BEx Query elements is captured in table RSZCOMPDIR. The table has column called VERSION which hold information about version of the reporting component. And exactly this is the field that tells us either it is query of 3.x or 7.x. As per value of the column; if it is higher than 100 it means that 7.x query if it is less than 100 means it is 3.x.

In case you want to perform the migration of old queries you might be interested to read further. The migration is quite easy in fact. All that needs to be done is to open the BEx 3.x query in BEx 7.0 Query Designer and save it. Once you hit SAVE button on the toolbar the query is migrated. It means you can open it only in 7.x BEx Query Designer and it can’t be opened in BEx 3.x Query Designer again. On such an attempt you will get a following message:

This component was edited with a more recent version of the Editor. You also have to use the more recent version to edit further. Further processing not possible. Update your front end'.

There is one more thing. If you open the old query in new BEx Query Designer the system performs automatic backup of the query. It stores the backup of the query and its component which was originally created in 3.x version of BW. The backup components can be recognized by value “B” of column OBJVER of table RSZCOMPDIR. Unless the query is saved in the BEx Query Designer 7.x it still can be opened in old BEx QD even it has backup version. There is an ABAP report (COMPONENT_RESTORE) provided which can be used to convert migrated query to 7.x back to 3.x version. Needless to say this is not foreseen to be used too much; of course. Also notice that once restore is done then all modifications done with QD 7.x are lost.

There is a possibility to prevent creation of the backup version of the query. It can be achieved by implementing following parameter in the table RSADMIN and setting its value to X: QDEF_NO_3X_BACKUP

Useful information:

Overview of BW 3.x to 7.x migration possibilities per BW objects

1932461 - SAP BW 3.5 BEx tools / BW 3.5 frontend add-ons are incompatible with BW7.4

1807522 - BEx 3.5 Objects Migration to BEx 7.0

1097674 - NW2004s: Administration of backup object version

949330 - NW04s: Query backup and restore

1410878 - Maintenance for BW 3.5 front-end add-ons

Are your BW users still using BEx 3.x?

To see BW of version lower than 7.x is quite rare case nowadays. Most of the present BW systems are running on version 7.0+. It has been almost decade now since first version of BW 7.0 was introduced in 2005. With evolution of BW backend its frontend system progressed as well. Despite of having many frontend tools in eyes of most of BW users the "main" frontend tool still remains BEx Analyzer.

There are two significant releases of BEx tools. Within introduction of BW 7.x the BEx which was present at that time was just renamed to BEx Analyzer 3.x and new release of BEx compatible with BW 7.x was released. The old BEx Analyzer 3.x (also known as BEx 3.5) was still shipped with SAP GUI and once deployed there are both version available for the user.

As the both versions are available some user may try to still using the old one maybe not knowing that it is the old one. It was not issue until version 7.4. Actually within the 7.4 BEx 3.5 cannot be used with BW 7.4x. This is official advice from SAP (see Note 1410878 - Maintenance for BW 3.5 front-end add-ons). This fact is even more stressed out in the note 1932461 - SAP BW 3.5 BEx tools / BW 3.5 frontend add-ons are incompatible with BW7.4.

But what will happen when we (despite all the advices from SAP) try to use old BEx? In case of Query Designer (QD) there is a warning pop up saying that component (BEx Query) was edited with recent version of BEx.

In case of BEx Analyzer 3.5 when we try to open the query running in BW of version 7.4 the BEx Analyzer crashes with following error:

Of course BW administrator will never hear about this until user doesn't complain. But there is also a dump associated with the crash of BEx Analyzer 3.5. it looks like follows:

FM RRY_SESSION_INITIALIZE

Runtime error: CALL_FUNCTION_NOT_FOUND

Terminated program: SAPMSSY1

So in case you are on BW 7.4 and you observe such a dump you may want to check with particular user and advise him/her to start using regular BEx Analyzer instead of old one. Because only this dump is sign that some users are still sticking into the old BEx!

Digging into user BEx Analyzer’s Favorites

Sometimes BW people are just ask to loot at user’s reports stored in his Favorites. Business users always do not want to deal with report’s technical names. They just refer to their Favorites and ask to do something with particular report. So be it and as BW developers we need to deal with it.

How to find reports which are stored in someone’s BEx Analyzer Favorites? Table which holds this information is called SMEN_BUFFC. It is common table which holds much more information than just BW’s Favorites. There is information on Favorites in SAP’s GUI SAP Easy Access (like tcodes, folders, URL.), BW reports, ABAP Queries, Report Writers reports etc. To recognize what type of the object is it use field REPORTTYPE, which can have following values:

<space>      ABAP

AQ               Query

BB                Report Portfolio Rpt

LS                Form

RE                Drilldown Reporting

RW               Report Writer Report

TR                Transactions

OT                Other

To get someone’s BW reports a filter on field REPORT and value SAP_BW_QUERY needs to be placed.

Further how to get the BEx query technical name. From the table SMEN_BUFFC take value of corresponding field named SAP_GUID. With value of this field go to table RSRREPDIR and put the value into the field GENUNID on selection.

Refresh Function for Individual Query in BEx Analyzer

Sometimes BW power users are maintaining huge workbooks with many queries. Kind of their “personal dashboards” with important data out of BW systems. The problem with these workbooks is that sometimes it takes very long time to get the workbook refreshed. To solve this; users were looking to kind of functionality where they could decide which query contained in the workbook should be refreshed.

Finally this function is available there. It is called "The Single Query refresh" functionality of the BexAnalyzer. It allows refreshing individual queries if there are several queries used in the workbook. Before once user clicked on refresh of one query; it refreshed it but also other queries were refreshed.

To implement this we need:

1. In BW server: enable parameter ANA_SINGLEDPREFRESH with value X in transaction RS_FRONTEND_INIT.

2. In the workbooks: on General tab of the Workbook in Settings dialog, a flag "Allow Refresh Function for Individual Queries" must be checked. Once this is done there in context menu of an item that belongs to the query a query it has one more option called "Refresh This Query".

ABAP Report that only carries texts

I found very interesting report. It has no ABAP code at all. But it has a lot of text in there instead. Name of the report mentioned in here is RSR_BEXANALYZER_TEXT. The texts which it carries are related to BW’s BEx Analyzer. So basically all the texts used within this Windows based tool are stored with the report.

The texts are downloaded to user’s folder (%temp%\BW\COMMON\texts\ into the file e.g. BExTexts_EN.xml as per language used for login) when first logon to BEx Analyzer is performed. There are many SAP Notes which deal with the report. Mostly they deliver new or changed texts. So once you are not able to see new/changed text delivered by Note you may want to delete this folder or file to get refreshed texts to your workstation. See SAP Note “1732598 - Text Elements do not appear correctly in BEx Analyzer” for details.

I was curious to know how many texts the report has. So I prepared short ABAP report to count that. Here it is:

DATA: lt_tpool TYPE TABLE OF textpool,
      lv_lin   TYPE i.

    CALL FUNCTION 'RS_TEXTPOOL_READ'
      EXPORTING  OBJECTNAME = 'RSR_BEXANALYZER_TEXT'
                 ACTION     = 'DISPLAY'
                 LANGUAGE   = 'E'
      TABLES     TPOOL      = lt_tpool
      EXCEPTIONS others     = 0.

DESCRIBE TABLE lt_tpool LINES lv_lin.
WRITE: / lv_lin.

BTW: in my system based on SAP_BW 731 SP 0002 it has 827 texts.

So basically this is how I was playing tonight J

Strange entries in table RSRREPDIR - queries created directly in frontend tool

From time to time BW people need to have a look into table RSRREPDIR. The table is directory of all BW’s BEx queries. One of cases when the table needs to be checked is if there is inconsistency within the query. If it is the case then OBJSTAT flag is set to inactive. To fix this the BEx query has to be regenerated in TA RSRT.

However I want to talk about something different here in this post. Within the data in the table there may be some strange values appearing. This fact was brought to my attention within following post in the SCN forum: SAP BEx query shows "!!A" in RSRREPDIR table.

Queries that names that follows patter like !INFOCUBE or !!INFOCUBE are virtual queries and are not real query objects. This means that such a queries cannot be transported. Mostly such queries were created in frontend tools like BEx Query Analyzer by just opening particular infocube and designing the report directly in the frontend tool.

!!A mystery:

Basically it is about COMPUID and COMPID fields. There can be a values in these starting with: !!A. As it is correctly answered in the mentioned forum post – it is caused that query which starts with !!A was directly created in BEx Query Analyzer on top of multiprovider or cube. So no BEx Query Designer was used to build the query. This practice is common when we need to get some data out of the infoprovider but we do not have authorization to run BEx Query Designer e.g. in test or production environment.

$$DEFAULT mystery:

Other strange characters that may popup in the RSRREPDIR table is double $ sign ($$). These two characters are usually present in table in form: $$DEFAULT in fields COMPUID and INFOCUBE.  The $$DEFAULT is technical object which needs to be located in the table. It represents default BEx query in BW system. There are few checks performed for this object (represented by cl_rsr_supprmess=>n_c_default) in standard SAP code. Seems even several lines in the table with this value are possible in some systems. Despite the fact that there are some SAP Notes mentioning the $$DEFAULT I wasn’t able to figure out what is its purpose. Fact is that such value is there and seems it is not causing any issues in BW system.

- update on 11/08/2017 -

There are few more of mystery entries that can be found in RSRREPDIR table:

!!1 mystery:

I wasn't able to find anything special on this one. Therefore I assume it is same case as !!AA as described above.

!!I mystery:

Again I wasn't able to find anything special on this one. Seems same case as  !!AA - see above.

!!O mystery:

Entries like this seems are being produced when query is created on top of BPC objects (falling under name space '/CPMB/’).

- update on 11/03/2020 -

!@ mystery:

Seems SAP support people are able to create such a queries starting with this prefix.

!/CPMB/* mystery:

Similar to !!O mystery – falling to BPC objects name space

IP/!IP* mystery:

Also part of virtual queries - !INFOCUBE