Tag Archives: bex

BEx Analyzer Macro to Locate position of GRID_1 and position of first data cell

The below macro will locate the top left cell of the BEx DataGrid GRID_1 and the position of the top left data cell.

See also the following post for additional info on VBA and BEx Analyzer
http://sapbwbw.com/bex-analyzer-workbooks-and-vba/ ‎

To make this macro work you need to add the BEx Macros (go to Workbook Settings in the BEx Add In, Exits, Add Macros)

 

===============================================
[Visual Basic light=”true”]
Sub Macro1()
Dim oBexItem As BExItem
Dim oBexItems As BExItems

Dim oBex As Object

Dim range1 As Range

‘ Connect to Bex Analyzer
Set oBex = Application.Run(“BEXAnalyzer.xla!GetBex”)

‘ Obtain all BEx Items in the worksheet
Set oBexItems = oBex.Items

‘ Find the GRID item by name and unhide it plus a range that is offset to the row below it
For Each oBexItem In oBexItems
If (oBexItem.Name = “GRID_1”) Then
‘Set range1 = oBexItem.Range
‘set the correct worksheet
Application.Worksheets(oBexItem.Worksheet.Name).Activate
Dim lFirstDataCell As Variant
lFirstDataCell = oBexItem.DataProvider.Result.Grid.firstdatacell
MsgBox oBexItem.Range.Row
MsgBox oBexItem.Range.Column
MsgBox lFirstDataCell.X
MsgBox lFirstDataCell.Y

GridName = oBexItem.Name
MsgBox GridName
End If
Next

End Sub
[/Visual Basic]
===============================================

Program to mass update BEx External Access flag

I found  a blog post describing a program that allows you to change the BEx query “External Access” flag en masse. Very useful if you start integrating SAP BO and SAP BW and want to expose your existing queries.

The blog author is named Campbell Skene and here is the link to his blog:

http://www.skeneintelligence.com/blog/2012/04/05/bex_external_flag/

You can download the program he proposes in Saplink format here below.

Download – ZBW_QUERY_EXTERNAL_UPDATE – 7.8 kB – 374   downloads

ABAP Program to do mass deletion of Process Chains

The following small program allows you to do a mass deletion of process chains.

I tried to remove all popup messages that occur during PC deletion, but there is stil some problem with that and you have to press OK at each process chain.

You can download the code of the programhere in Saplink format.
Download – PROG_ZBW_RSPC_MASS_DELETE.slnk – 3.3 kB – 408   downloads

APD – Limitations on BEx Query as source

Additional infos:

 

OBIEE Reporting on SAP BW – BEx Report Key Date

OBIEE Reporting on SAP BW runs via MDX. SAP states that ‘The term time dependency does not exist in Microsoft’s MDX specification”.  This statement comes from the “Mapping Metadata” page of the “Open Analysis Interfaces”  pages for SAP BW.

Time Dependency in MDX

The term time dependency does not exist in Microsoft’s MDX specification. According to this specification, the same hierarchy, and therefore the same key date, has to be used in both MDX and in function BAPI_MDPROVIDER_GET_MEMBERS. Because the current date is always used when you call BAPI_MDPROVIDER_GET_MEMBERS, BW hierarchies with time-dependent names or time-dependent structures are also evaluated with the current key date. Key date variables are also ignored in MDX.

However, you can set a date other than the current date for the current session using function BAPI_MDPROVIDER_SET_KEY_DATE. For consistency reasons, the query key date of all subsequent MDX executions are also replaced by this date.

You use BAPI_MDPROVIDER_GET_KEY_DATE  to get the value that you set using BAPI_MDPROVIDER_SET_KEY_DATE. If no value has been set, this function returns the current date.

You can use these two functions like you use other BAPIs delivered by SAP. Enter the date in the SAP-internal format YYYYMMDD.

Here are some simple situations:
Case 1. Static Key Date value in Query Definition.
InfoObject 0EMPLOYEE with Time Dependent Master Data 0COSTCENTER.
Employee A with Costcenter A in 2013 and Costcenter B in 2014
BEx Query 1
Rows = Employee, Display Attribute Costcenter of Employee, Cot Center
Columns = Number of Records
Filter on Employee A
Key Date = 1/12/2013
BEx Query 2
Same as BEx Query 1 but Key Date set to 1/2/2014

In OBIEE Create 2 Subject Areas for the 2 BEx Queries
Analysis on the subject areas will return the correct Costcenter relative to the Key Date.

Case 2. BEx Ready for Input Single Value Mandatory Variable for Key Date.
BEx Query 3
Same as BEX Query 1 above, but Key Date set to the BEx Variable.
In RPD (OBI Administration the Key Date Variable is visible as Cube Variable)
However when running an OBIEE Analysis an error is returned <>, as stated in the SAP Help.

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 – 335   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 Variables – RRMS_MESSAGE_HANDLING popup disabled when calling RSDRI_INFOPROV_READ in customer exit.

I had this peculiar issue while working on a BEx report where I used FM RRMS_MESSAGE_HANDLING to report errors in the BEx Variable Exit in i_step = 3.

I needed to define the value of a BEx Variable by reading the content of a cube with FM RSDRI_INFOPROV_READ. This worked fine.

However, based on the result I also wanted to generate a popup error message in I_STEP = 3 of the BEx Variable Exit.

This can be achieved by calling FM RRMS_MESSAGE_HANDLING.

When I combined the call to FM RSDRI_INFOPROV_READ with a subsequent call to FM RRMS_MESSAGE_HANDLING, the BEx Variable exit considers that the Variable selection is in error (does not close variable screen, and expexts a new input) BUT, there is no popup message displayed in the user interface.

I had to submit an Incident (formerly Customer Message) in SAPs serivce site to figure out a solution to this. Finally, SAP told me that a call to FM RRMS_MESSAGE_OFF_RESET would solve the problem. This was the case, problem solved.