SAP BW Create own reversal entry

Lately, all my posts started with "In my current project", so now something else, even if it was developed in the current project. The problem we are facing with is that we get a data extraction which deliver us only the new data records, not the reverse data record.

Old booked value
Old booked value
New booked value
New booked value

So we need to build your "own reversal entry" to set the values of a record to 0. For this we write our ADSO in itself with a formula which delivers the negative value.

SAP BW Negative formula
SAP BW Negative formula

I build a transformation which the formula above and the magic will be done in the DTP filter. First, let me briefly explain our data model. We use the following layers:

  • Acquisition Layer
  • Propagation Layer
  • Transformation Layer

But back to the magic. The routine is writen in the DTP Transformation Layer in Transformation Layer.

Write an Advanced DSO in itself
Write an Advanced DSO in itself

First I determine all requests from the Acquistion Layer DSO. 

"Determine all requests from source DSO

SELECT DISTINCT ( reqtsn ) FROM /bic/avf0aperia1 INTO TABLE lt_requests.

 

After I have all requests in my internal table, I sort it descending to get the right order.

"Sort Table descending
SORT lt_requests DESCENDING BY request.

 

Now I select the first request. It is the newst.

LOOP AT lt_requests INTO ls_requests FROM 1 TO 1.

  "Puffer last request

  lv_last_request = ls_requests-request.

ENDLOOP.

 

Now we know the right request and select all data from the advanced DSO (Aquisition Layer).

SELECT DISTINCT field1 field2 calyear field3 FROM DSO_AL INTO TABLE lt_new_data WHERE reqtsn = lv_last_request.

 

After we now have all necessary information in our internal table, we have to fill the DTP filter.

DATA: l_idx LIKE sy-tabix.

READ TABLE l_t_range WITH KEY fieldname = 'field2'.

l_idx = sy-tabix.

LOOP AT lt_new_data ASSIGNING <fs_new_data>.

  l_t_range-fieldname = 'field2'.

  l_t_range-iobjnm = 'field2'.

  l_t_range-sign = 'I'.

  l_t_range-option = 'EQ'.

  l_t_range-low = <fs_new_data>-field2.

  IF l_idx <> 0.

    MODIFY l_t_range INDEX l_idx.

  ELSE.

    APPEND l_t_range.

  ENDIF.

ENDLOOP

After I execute the DTP and activate the request in the transformation layer advanced DSO you see there are only 18 entries which are corrected.

Number of entries which are false
Number of entries which are false

When you now look into the active table of the advanced DSO, you see all values which are shown above are now 0.

Active table values
Active table values

The changelog also show the new, the before and the after image for the entries. So you can understand what was booked.

Changelog View
Changelog View

And when we now execute all DTPs to write the data from the Acquistion Layer to the Tranformation Layer, we see that there are only 18 entries which are new.

Requests of the advanced DSO
Requests of the advanced DSO

And also the new booked values are the same we have above in our second extract.

New values in advanced DSO
New values in advanced DSO

As you can see it is very easy to create your own reversal entry if the source system don't deliver one. I hope someone can need it.

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
49,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 €
Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
9,99 €

Write a comment

Comments: 0