Tag Archives: workbook

Program listing the workbooks and queries in a BEx Role

To build a list of BEx Workbooks and BEx Queries in a role use table AGR_HIER

keys = AGR_NAME + OBJECT_ID

  • AGR_NAME = role
  • OBJECT_ID  = ID fo the object in the role (1 is the root folder)
  • PARENT_ID = ID of the parent folder (1 for objects that are in the root folder)
  • FOLDER = X if it is a folder
  • TEXT = Either Role menu descripiption or report description
  • REPORT = RRMX for WBK / SAP_BW_QUERY for query
  • SAP_GUID = WBK ID or Query TECH NAME

The following program lists the Role / Workbook ID / Workbook name and all the queries that are in the workbook. Additionally it will list any replacement path variable that uses the result of another query as input.

Download – ZBW_WB_PROPERTIES – 17.4 kB – 323   downloads

BEx Analyzer – Default Workbook

Via the “Global Settings” option in the BEx Analyzer add-on menu you can define the Workbook ID of the Default Workbook. The default workbook is the workbook into which queries are opened. By default, the “SAP Default Workbook” is used, but this can be changed to a customized Default Workbook.

References

How to identify the Workbook ID of the SAP Default Workbook?

Go to table RSRWBINDEXT

Search a record with
LANGU = EN OBJVERS
OBJVERS = D
TITLE = “SAP Default Workbook”

FindSAPDefaultWbk1FindSAPDefaultWbk2

How to check which Default Workbook is being used by a user

Check table RSRWBTEMPLATE

See following SCN Post Setting the Global Workbook Template in SAP NetWeaver 2004s BI or BW 3.x

How to open the SAP Default Workbook to make a copy => TCD RRMXP

BEx Workbook – Popup to pass values to Planning Function

passveluestofunction

Below are the information I gathered regarding techniques to allow some kind of dynamic selection of values to be passed to Planning Functions.

1) See note 1121654 – Note 1121654 – Problems transferring variables to planning func/sequence

2) Extract from a SDN Discussion:
Iin BEx workbook there is no Possibility to call an automatic pop up for variables in planning functions. The functions in BEx Workbook take the entries from cells like described in the notes. If you want to give the user more usability with pop-ups then you have to program VBA coding which writes the entry in the VBA pop up in the cell from which the planning function take the entry and then call the planning function from VBA code
1.) You have your button for the planing function and the command range for the variables. Let’s assume the variable value is in Cell D4. The name of the planning function is BUTTON_34 (you see that in the properties window of the BEX Button. You can implment the command range in the button in an extra sheet and then hide it.

2.) On the sheet with your query or cockpit (from where you want to run the planning function) you implement a second button – but not from the BEx menubar, from the VBA forms. Then you assign a VBA macro to this button.
3.) in this button you have to:
 a.) call a Msgbox for prompt asking for the value of the period
 b.) assign the value entered from the user to CELL D4 in the hidden sheet
 c.) run the planning funtion button in the hidden Sheet

A sample coding could look something like this:

Public sub Call_Funtion()

Dim Useranswer as string

Useranswer = InputBox("Please enter the period in the format MM/YYYY", "Period")

Table1.Range(D4).Value = Useranswer

Table1.Calculate

Application.Run "'" & ThisWorkbook.Name & "'!TABLE1.BUTTON_34_Click

end sub

This should do fine. If you are more experienced in VBA you can design a form with more input fields. It would look more comofortable for the user. But to explain this how to do it would be done in a different board.

See original post: https://scn.sap.com/message/7776844
If you do not have a single value variable but an interval the command range must look like this:

VAR_NAME_3                       1               MY_VAR_SO_TO
VAR_VALUE_LOW_EXT_3     1               04.2003 (Your Start value)
VAR_SIGN_3                         1               I
VAR_OPERATOR_3               1               BT
VAR_VALUE_HIGH_EXT_3     1               06.2003 (Your End value)

In the VBA code you then have to call 2 Propmts for input – one for start, one for end or you design a form if you are experienced. Then you assign the entered values to the ranges and call the buttoon via Application.run.

BEX Workbook Size Analysis report

One element influencing the performance of a BEx Workbook is its size. Each time you open a BEX Workbook from your BEx Analyzer, the excel file corresponding to the BEx Workbook is retrieved from the BW servers (it is stored as blob format in table RSRWBSTORE) and transferred to the front end.

Thus, before even doing a first refresh, there might be a perceivable delay due to the workbook transfer.

You can monitor the sizes of all the workbooks in your BW system with the following report available in Saplink format:

Download – PROG_ZBI_WORKBOOK_SIZES.slnk –  2.4 kB – 251   downloads

The size displayed by the report is in lines in table RSRBWSTORE. The real size is about 2000kb multiplied by the number of lines.

(Note: The BEx Workbooks are also stored in temp directory on the PC when executed. I do not know however if this means that the entire workbook is not always transferred on opening. If you have any info on the subject, please leave a comment.)

The below code also displays the French and Dutch workbook title.  (Just change the F and N to E and D to have it in English and German for instance.

*&---------------------------------------------------------------------*
*& Report  ZBI_WORKBOOK_SIZES
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  zbi_workbook_sizes.

TYPES:
   BEGIN OF ty_output,
     workbookid TYPE rsrwbindex-workbookid,
     lines TYPE i,
     title_n TYPE rsrwbindext-title,
     title_f TYPE rsrwbindext-title,
   END OF ty_output.

DATA:
      l_lines TYPE i,
      lt_output TYPE TABLE OF ty_output,
      ls_output TYPE ty_output,
      ls_rsrwbindex TYPE rsrwbindex,
      ls_rsrwbindext_n TYPE rsrwbindext,
      ls_rsrwbindext_f TYPE rsrwbindext,
      lt_rsrwbindex TYPE TABLE OF rsrwbindex.

" Selection parameters
SELECT-OPTIONS s_wbk FOR ls_rsrwbindex-workbookid.

" Get list of workbooks
SELECT * FROM rsrwbindex INTO TABLE lt_rsrwbindex
  WHERE workbookid IN s_wbk
    AND objvers = 'A'.

LOOP AT lt_rsrwbindex INTO ls_rsrwbindex.
  SELECT MAX( linenumber ) FROM rsrwbstore INTO l_lines
    WHERE objvers = 'A'
      AND workbookid = ls_rsrwbindex-workbookid.
  SELECT SINGLE * FROM rsrwbindext INTO ls_rsrwbindext_n
    WHERE objvers = 'A'
     AND langu = 'N'
     AND  workbookid = ls_rsrwbindex-workbookid.

  SELECT SINGLE * FROM rsrwbindext INTO ls_rsrwbindext_f
WHERE objvers = 'A'
 AND langu = 'F'
 AND  workbookid = ls_rsrwbindex-workbookid.
  ls_output-workbookid = ls_rsrwbindex-workbookid.
  ls_output-lines = l_lines.
  ls_output-title_n = ls_rsrwbindext_n-title.
  ls_output-title_f = ls_rsrwbindext_f-title.
  APPEND ls_output TO lt_output.
ENDLOOP.
SORT lt_output BY lines DESCENDING.
LOOP AT lt_output INTO ls_output.
  WRITE:  ls_output-title_n, ls_output-title_f, ls_output-lines, ls_output-workbookid .
  NEW-LINE.
ENDLOOP.