About Claudio Ciardelli

Find more about me on:

Here are my most recent posts

All posts by Claudio Ciardelli

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
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

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:


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

Download – ZBW_QUERY_EXTERNAL_UPDATE – 7.8 kB – 368   downloads

BW RSA6 Application Component Hierarchy – Creat Node and Transport

You can maintain the application component hierarchy in RSA6. There are options to create/delete/rename and move hierarchy nodes under the Hierarchy menu.

To transport a modified version of the RSA6 application component hierarchy you will have to manually add it to a transport by adding R3TR DSAA APCO (to transport the inactive version of the hierachy use R3TR DSAD APCO).

If the application hierarchy is in the $TMP package you first have to set it to a transportable package as follows:

If your application component hierarchy is assigned to local object then first you need to change the package.

Go to TCode: SE03
For Object Type Selection use: DSAA
Object: APCO
Now change object directory from $TMP to the one being used in your project.

See SAP note 382471 – BW-OLTP-APCO: How do I transport it?
Also check OSS note 542454

See also: http://scn.sap.com/thread/228915

ABAP Substract 2 timestamps

A Google search turns up this recommendation: http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/840ad679-0601-0010-cd8e-9989fd650822#q-8: use the class CL_ABAP_TSTMP. You can also see an example of how to use the class in this thread: http://scn.sap.com/thread/85476.

      syst_date = <wa_joblist>-strtdate
      syst_time = <wa_joblist>-strttime
      utc_tstmp = start_stamp ).

      syst_date = sy-datum
      syst_time = sy-uzeit
      utc_tstmp = now_stamp ).

  seconds = cl_abap_tstmp=>subtract(
      tstmp1 = now_stamp
      tstmp2 = start_stamp ).
See also: http://stackoverflow.com/questions/10539133/difference-between-2-timestamps

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 – 402   downloads

Modify an existing SAP BW Source System connection

Technique for switching ECC source systems in SAP BW Environment without affecting the
data modeling objects in the BW environment. By data modeling objects, we mean transformations, update rules and transfer rules getting inactive or getting deleted. Once the source system is switched, data reload needs to happen to have the initializations and deltas to come from the new source system. Also we have to ensure that the data from old source system( both transactional and master ) needs to be deleted in the BW system before reloading from the data from new source system to ensure consistency in the data from new system.

The following  SCN Article describes the procedure

Step by Step on Changing ECC Source Systems without Affecting Data Modeling Objects in SAP BW


See also 110849 – “Error during insert in port table” (create source system) if the error occurs during the RSA1 / Source System / Restore step

SAP REFX Conditions – BW Conditions DSO and One Time Conditions

Data from the standard REFX exctractor 0REFX_1 can be loaded into a DSO with keys related to the RE Object (Object ID, Company Code, Building, Rental Space…) the condition Type and a validity date (ex:0DATEFROM). The condition amounts are provided with cumulative Key Figures (see 0COND_CHG) in relation to time.

This works fine for normal condition type  that can have a start date, an end date and in some cases 1 or more dates where the value of the condition changes.


Condition start 01.01.2013 value 100. Condition change on 1.1.2014 value 80. Condition end on 31.21.2014. This results in 3 0REFX_1 records as follow:
VALUEVALIDFROM = 01.01.2013 COND_CHG = 100
VALUEVALIDFROM = 01.01.2014 COND_CHG = -20
VALUEVALIDFROM = 31.12.2014 COND_CHG = -80

There is however one problem with this configuration when a contract contains multiple one-time conditions related to the same condition type whose respective start and end dates are adjacent. 


One-Time Condition start 01.01.2013 value 100. Condition ends on 31.12..2013.
Second One-Time  Condition start 01.01.2014 value 80. Condition ends on 31.12..2014.

This results in 4 0REFX_1 records as follow:
VALUEVALIDFROM = 01.01.2013 COND_CHG = 100
VALUEVALIDFROM = 01.01.2014 COND_CHG = -100
VALUEVALIDFROM = 01.01.2015 COND_CHG = -80

You see that here there are 2 records on the 1.1.2014.  If the transformation rule is set to overwrite, one record will be lost.

What can be done in BW: Make sure that 2 distinct 0REFX_1 records with similar keys do not have the same valid from date. to do this. Locate  possible cases by identifiying the one time condition record and then shift the valid from date by 1 day in the past (and make sure no more overlaps exists any more).

However it is not easy to identify one time conditions in the standard extractor 0REFX_1

To do this you need field UNIQUECOND. This field is available in the extract structure REIS_CONDITION_TRAN and the FM that is executed by the extractor will fill this field. However the field is set to not visible in ROOSFIELD, thus it is not visible in the extraction. A field in an extract structure is set to invisible when field ROOSFIELD-SELECTION equals ‘A’. This can be changed directly in the table by creating and running a small ABAP program. This change is not transportable, you will need to do this in acceptance and production too, and then transport the extractor after having removed the ‘Hide’ flag in RSA6 for the now visible UNIQUECOND field.


REPORT  zorefx_1_roosfield_uniquecond.
DATA: ls_roosfield TYPE roosfield.

  INTO ls_roosfield
  WHERE oltpsource = '0REFX_1'
    AND objvers = 'A'
    AND field = 'UNIQUECOND'.

IF sy-subrc = 0.
  ls_roosfield-selection = ''.
  MODIFY roosfield FROM ls_roosfield.

See also note 931669 – BW conditions: Validity of one-time conditions