Tuesday, August 15, 2017

Administration of BW workbooks

By term a workbook we refer to MS Excel file that contains a worksheet(s) having SAP BW queries inserted in. Basically when the workbook is stored it physically contains data pulled from SAP BW system via BEx Query. The workbooks can be either stored on the SAP BW backend or locally in user’s workstation.

In case the users are using the SAP BW system to store their workbooks after same time there can be a huge number of the workbooks pilling up on the server. The BW administrator therefore shall take care about it and do maintenance of the workbooks. There is couple of tools (ABAP reports) provided by SAP to support this task. Below I briefly introduce them.

I wrote similar post about the workbook deletion earlier here. This one is more general with regards to the workbook maintenance.

Report RSR_WORKBOOK_ADMINISTRATION (t-code RSRWBADMIN) – it takes care about mass deletion of the BW workbooks from the system. Multiple criteria are available on its selection screen. There are selection fields like Workbook Version/ ID/Owner, Last Changed by, Last Changed on, Workbook Size (Kb), Number of Changes, Number of Reads, Workbook Extension, Original System etc. Once the workbooks are found according the selection then either one or many can be deleted from report’s output screen. This report is dedicated to version 7.30 and 7.40 of the SAP BW.

Report RSR_WORKBOOK_ADMIN (t-code RSRWBADM) – same as above but dedicated to versions 7.00 and 7.11 of the SAP BW.

Report COMPONENT_REORG (t-code RSZDELETE) – generic t-code which also is performing a deletion of BW BEx queries. It utilizes FM RRMX_WORKBOOKS_DELETE  to perform deletion. It can be used as explained in my other post: Deletion of BEx workbooks

Report RRMX_WORKBOOKS_UNTOUCHED_PURGE - delete unreferenced (orphaned) workbooks. These are the workbooks that are not referenced to any role, there are no in any user’s favorites or there are no in any InfoCatalog. Report has also an option to specify on selection screen so called “Minimum age (in days)” however this field/variable is later not used in the code so the field has no meaning. Tested on version SAP_BW 740     SP09.

Report RRMX_WORKBOOKS_GARBAGE_COLLECT - identifies and removes orphaned workbooks. The report has no other option available on selection screen apart of checkbox “Delete workbooks found”. Basically it does the exactly the same as *_PURGE report

FM RSBB_WHERE_USED_LIST – provides a where used list of a workbook in activity groups.

Report RSWB_ROLES_REORG - Reorganization of the workbook and role storage. It checks the assignment of the workbooks to roles and the workbooks to favorites in both directions and it deletes assignments of non-existent IOs.

Friday, August 11, 2017

Error displayed in strange way while opening query in BEx QD

I came across a strange error in BEx Query Designer (QD) recently. While my user wasn’t authorized to edit the query I got below message from the message class R9 – msg no 108:

You do not have authorization for &1


While the error was shown in the QD it was displayed as follows, just plain msg ID and number. It was obvious that my user couldn’t change the query. But why regular description associated with the text ID and number is not displayed it is a mystery for me… Any thoughts? 


Wednesday, August 9, 2017

Determining what version of MS Excel was used to create BEx workbook

There are few of limitations when it comes to usage of BEx Query Designer within particular version of MS Excel. Mostly they are related to the fact that workbooks created in one version of MS Excel may do not work in other version. E.g. the workbooks created with MS Excel 2007 having format xlsx, xlsm or xlsb can’t be opened with Excel 2010. For more details about similar issues refer to Notes:

1498919 - Known Limitations of BExAnalyzer with Excel 2010
1175947 - Known Limitations of Analyzer with Excel 2007

Therefore fundamental question in issues like user is complaining that the workbook that worked in one version of the Excel doesn’t work in other is to check in what version of the Excel the workbook was created.


One way to find out that would be to have a look to extension of the Excel file. Up to version 2017 the Excel used only proprietary binary format – extension XLS. Afterwards it also uses Office Open XML as its primary file format – it has extension XLSM (or XLSX). These files are just a ZIP files with folder structure and several XML files. One of the XML files has in information about the version which was used to produce the file. The XLSM file just needs to be temporarily renamed to ZIP and in the folder called docProps a file called app.xml has to be displayed. In the XML element called the version is stored.


Now how to match the number from file (14 in this case) with the Excel version. Here below table (from wikipedia) becomes handy. In our case 14 means Excel 2010:
Excel 3          3.0    
Excel 4          4.0    
Excel 5          5.0    
Excel 95        7.0    
Excel 97        8.0    
Excel 2000     9.0    
Excel 2002     10.0
Excel 2003     11.0
Excel 2007     12.0
Excel 2010     14.0
Excel 2013     15.0
Excel 2016     16.0

In case one would try to use e.g. BEx workbook created in MS 2013 (version 15 would be stored in the app.xml file) in Excel of version 2010 (version 14) most likely it would not be possible as there would be libraries used by macros written in the Visual Basic incompatible between different versions of MS Excels. Attempt to open the Excel file would be ending up with errors like:

Error in loading DLL
or
Workbook corrupted error

The issue of missing or incompatible libraries can be spotted in editor of Microsoft Visual Basic for Application. Where in menu called Tools -> References a missing library would be highlighted like below:


More information:
1581104 - Workbooks created in Excel2007 cannot be opened in Excel2010



Sunday, August 6, 2017

Setting up a global template for BW workbooks

Templates used by BW (BEx) reports can be customized in BW system. There can be either global template for all users in the system or each user can have different template assigned. All this customizing is to be done in table RSRWBTEMPLATE – “Assignment Excel workbooks as personal templates”. I thought that I write some information about the table as it wasn’t much known to me until now.

If user has particular template customized there is an entry for such a user. Field TEMPLATEUSER contains user ID and the field WORKBOOKID contains the workbook ID.
All other users that do not have an entry in the table are using template that is stored in the table row that has blank TEMPLATEUSER field but there is a value in the filed WORKBOOKID. So this is how global default workbook is set up in the table.

Now how to actually customize the table? There is no maintenance view generated for the table either there is no entry in the SPRO t-code. All the customizing is actually done via BEx Analyzer. Here it is available in menu on BEx toolbar: BEx Analyzer -> Global Settings -> Select “Default Workbook” tab. In this popup there are following buttons available:

·         Use Current – user can set as global template current template that is opined in BEx
·         Use Default – if user has specific workbook setup this will overwrite it with default one
·         Use SAP Standard – this removes specific workbook set for user and SAP default one will be used. The SAP default workbook cannot be adjusted or modified.

There is also a checkbox called “Global Default Workbook” available on the same tab. By ticking off it the selected workbook as the default workbook will be used for all users.

One may ask if there is any difference between BW 3.x and 7.x with regards to the customizing of default workbook ID. BW 3.x there were only 2 fields in the table: TEMPLATEUSER and WORKBOOKID. In BW 7.x a third field was added: LEAST_BEX_VERSIO which tells about version of BEx. If the value in there is 70 it is obvious that the systems run BW 7.x.

The table values can be also manipulated by custom ABAP program. See as an example this ghist by JouryJonkergouw.

Also as per SAP Note 1963326 there is a bug that allows user w/o proper authorization to change the Global Default Workbook (template) used in BEx Analyzer. The Note explicitly says that it is happening in SAP GUI 7.3 but as per my testing also SAP GUI 74 is effected as well.


More information:
1963326 - BEx Analyzer: Global Default Workbook can be changed by enduser without having authorization

Minimal ABAP code to send out an email from ABAP NetWeaver system

Yesterday I blogged about minimal ABAP code needed to display data from internal table in ALV grid. Today I post similar “minimal ABAP code“ thing just this time related to sending an emails from SAP NetWeaver ABAP stack based systems.

I came up with below to example program demonstrating minimal ABAP code for sending email:

Minimal version:
REPORT zmm_email_send_01.

DATA: lt_recepeints TYPE TABLE OF somlreci1,
      ls_recepeints TYPE somlreci1,
      ls_docdata    TYPE sodocchgi1,
      lt_bodydata   TYPE TABLE OF solisti1,
      ls_bodydata   TYPE solisti1.

SELECT * FROM usr02 INTO TABLE @DATA(lt_users).

LOOP AT lt_users INTO DATA(ls_users).
  ls_bodydata-line = ls_users-bname.
  APPEND ls_bodydata TO lt_bodydata.
ENDLOOP.

ls_recepeints-receiver = 'x.x@domain.com'.
ls_recepeints-rec_type = 'U'.
ls_recepeints-express = 'X'.
APPEND ls_recepeints TO lt_recepeints.

CALL FUNCTION 'SO_NEW_DOCUMENT_SEND_API1'
  EXPORTING
    document_type  = 'RAW'
    put_in_outbox  = 'X'
  TABLES
    object_content = lt_bodydata
    receivers      = lt_recepeints.
COMMIT WORK.

Regular version (with subject etc):
REPORT zmm_email_send_01.

DATA: lt_recepeints TYPE TABLE OF somlreci1,
      ls_recepeints TYPE somlreci1,
      ls_docdata    TYPE sodocchgi1,
      lt_bodydata   TYPE TABLE OF solisti1,
      ls_bodydata   TYPE solisti1.

SELECT * FROM usr02 INTO TABLE @DATA(lt_users).

LOOP AT lt_users INTO DATA(ls_users).
  ls_bodydata-line = ls_users-bname.
  APPEND ls_bodydata TO lt_bodydata.
ENDLOOP.

ls_docdata-obj_name   = 'Email'.
ls_docdata-obj_descr  = 'Email from' && sy-sysid. “subject

ls_recepeints-receiver = 'x.x@domain.com'.
ls_recepeints-rec_type = 'U'.
ls_recepeints-express = 'X'.
APPEND ls_recepeints TO lt_recepeints.

CALL FUNCTION 'SO_NEW_DOCUMENT_SEND_API1'
  EXPORTING
    document_data  = ls_docdata
    document_type  = 'RAW'
    put_in_outbox  = 'X'
  TABLES
    object_content = lt_bodydata
    receivers      = lt_recepeints.
COMMIT WORK.


Source code of the example can be found here: gsofty12/MINIMAL_EMAIL_SENT 

Saturday, August 5, 2017

Minimal ALV code to display data from internal table

Displaying data via ALV is very popular among ABAP programmers. ALV or so called SAP List Viewer (in fact it shouldn’t it be called SLV? funny though) is a user interface element for displaying tabular data in applications. It has a format data very familiar to SAP users. By default it offers a lot of functions like sorting, filtering, summing data in tables etc. Moreover it can be relatively easy enhanced by custom or application specific functions (custom button in ALV’s toolbar etc.). The ALV is sometimes called ALV grid control as the data is displayed in the table or grid.

There are few of Function Modules and classes/methods that can be used by ABAP programmer to leverage power of the ALV. In my case I was wondering what can be minimal ABAP code that could display data from internal table of ABAP program.

I came up with below to example program demonstrating minimal ABAP code for the ALV grid:

REPORT zmm_minimal_alv_01.
 
SELECT * FROM usr02 INTO TABLE @DATA(lt_users).
 
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  EXPORTING
    i_structure_name = 'USR02'
    i_grid_title     = 'Title of the ALV grid:'
  TABLES
    t_outtab         = lt_users.

In the example a I’m passing an information about data to be displayed by a structure with general layout specifications for list layout. In this case the structure (USR02) is present in ABAP Dictionary. In case it would be a custom one no persistently present in the ABAP Dictionary I would need to either pass it via internal table (IMPORT param IS_LAYOUT) with the set of information to be outputted or to pass it via a field catalog (IMPORT param IT_FIELDCAT) in the form of an internal table.

By adding import param i_grid_title a title of the grid can be added:

REPORT zmm_minimal_alv_02.

SELECT * FROM usr02 INTO TABLE @DATA(lt_users).

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  EXPORTING
    i_structure_name = 'USR02'
    i_grid_title     = 'Title of the ALV grid:'
  TABLES
    t_outtab         = lt_users.

By adding below import param it is possible to display the grid in new popup window and params can control position of that popup:

REPORT zmm_minimal_alv_03.
 
SELECT * FROM usr02 INTO TABLE @DATA(lt_users).
 
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
  EXPORTING
    i_structure_name = 'USR02'
    i_grid_title     = 'Title of the ALV grid:'
    i_screen_start_column = 10
    i_screen_start_line   = 20
    i_screen_end_column   = 200
    i_screen_end_line     = 50
  TABLES

    t_outtab         = lt_users.

Just to add also FM POPUP_WITH_TABLE_DISPLAY could be used to display data from the internal table in simple way however it is not the ALV.


Source code of the example can be found here: github.com/softy12/MINIMAL_ALV