BW · 26. November 2018

SAP Business Warehouse DTP Filter with ABAP Routine

In my current project I have to filter data with a lot of logic. So I build some ABAP routines in a DTP filter to receive the necessary data. First you have to open the DataTransferProcess (DTP) in change mode and select the Filter button on the Extraction tab.

Now a new window is opened. You see the selection for the DTP. If your field is not visible, click on Change Selection and select the necessary field. When you have the field you want to filter, use the small icon on the right to create a ABAP routine.

Enter a name for the routine and you see the following screen.

And now we have the full power of ABAP to build some really cool stuff. My example is we read a hierarchy and select all leaves under a node and set the DTP Filter with it. Or you read the attributes of an InfoObject and select all entries which are equal to the attributes and enter them into the filter.

CONSTANTS: lc_iobjnm_zsaleschannel TYPE rsiobjnm VALUE 'ZSALESCHANNEL'.

DATA: ls_rssh_hiedir TYPE rshiedir,
      lt_rssh_hiedir TYPE rssh_t_hiedir,
      ls_subtreesel  TYPE rssh_s_nodebyname,
      lt_hierarchy   TYPE /bic/whzsaleschannel,

FIELD-SYMBOLS: <ls_hierarchy> TYPE /bic/hzsaleschannel,

"Get all hierarchies from the object
CALL FUNCTION 'RSSH_HIER_OF_IOBJ_GET'
  EXPORTING
    i_objvers = rs_c_objvers-active
    i_iobjnm  = lc_iobjnm_zsaleschannel
    i_langu   = sy-langu
  IMPORTING
   e_t_rshiedir = lt_rssh_hiedir.

"Get technical hierarchy id to hierarchy name
CLEAR ls_rssh_hiedir.

READ TABLE lt_rssh_hiedir INTO ls_rssh_hiedir WITH KEY hienm   = i_zsaleschannel_h
                                                       objvers = rs_c_objvers-active.

"Sub-Tree to Note/Leaf
CLEAR: ls_subtreesel.
ls_subtreesel-iobjnm = lc_iobjnm_zsaleschannel
ls_subtreesel-nodename = i_node.

"Get Hierarchy Elements
CALL METHOD cl_rssh_hierarchy_func=>get
  EXPORTING
   i_objvers      = rs_c_objvers-active
   i_hieid        = ls_rssh_hiedir-hieid
   i_s_subtreesel = ls_subtreesel
  IMPORTING
   e_t_hiestrucall = lt_hierachy

After I have now all elements in an internal table, I can add them to the DTP Filter.

"Loop over the internal table and assign the value to the dtp filter
LOOP AT lt_hierarchy ASSIGNING <ls_hierarchy>.
  l_t_range-fieldname = '/BIC/ZSALESCHANNEL'.
  l_t_range-iobjnm    = '/BIC/ZSALESCHANNEL'.
  l_t_range-sign      = 'I'.
  l_t_range-option    = 'EQ'.
  l_t_range-low       = <ls_hierarchy>-/bic/zsaleschannel.
  IF l_idx <> 0.
    MODIFY l_t_range INDEX l_idx.
  ELSE.
    APPEND l_t_range.
  ENDIF.
ENDLOOP.

After we saved our development and go back, we see a the little ABAP icon on the right (highlighted in red). If you want to remove a filter, click on the trashcan to remove the logic.

Conclusion

Sometimes it is necessary to filter your data. You could do this in different ways, but the most effiencient way is the DTP filter. Because the loading don't touch the data you not select. If you remove the data via a start routine, all the data has to be catched from the database and you select more than you need at the end.

Besides ABAP routine you could also use variables to filter data. In SAP BW/4HANA the filter is a little bit different this will be covered in another post.

author.

Hi,

I am Tobias, I write this blog since 2014, you can find me on twitter, facebook and youtube. I work as a Senior Business Warehouse Consultant. In 2016 I wrote the first edition of Analysis Office - The Comprehensive Guide. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

In my newsletter you get informed about new topics
You learn how to use Analysis Office
You get tips and tricks about SAP BI topics
You get the first 3 chapters of my ebook Analysis Office - The Comprehensive Guide for free

Query Link Components

Link your Query Components in a SAP BW/4HANA system again, after you have decouple them by mistake

67,00 €

Transport Dependency Check

Check the dependency of your transport requests before you want to transport your development to your productive system.

57,00 €

Tags: sapbw, abap, 2018november

Write a comment

Comments: 10

#1
Nadine Ngassi (Tuesday, 18 December 2018 18:50)

Hi Tobias,
I have read this code and want to know how to write a routine on a DTP to tell him to filter OUT some data which are into another DSO? I'll be glad to have an answer from you. I gave you here my email: nadine.jobangebot... googlemail.com. You change the ... with @
My regards,
nadine
#2
Tobias (Wednesday, 19 December 2018 19:21)

Hi Nadine,

thanks for your Comment. You can filter values out by using the "l_t_range-sign = 'E' " instead of "l_t_range-sign = 'I'.". E means exclude and I means Include.

Best regards,

Tobias
#3
Christian (Monday, 29 April 2019 16:06)

Hi ! I am having a problem on method cl_rssh_hierarchy_func=>get while importing
e_t_hiestrucall = lt_hierachy due to incompactibilities between both structures.
I am trying to retrieve costelmnts from a hierarchy, but I cannot find the correct type of lt_hierachy . Do you have any idea how to find it?
Thanks !
#4
Tobias (Tuesday, 30 April 2019 10:47)

Hi Christian,

you need the hierarchy table of costelmnts. This is /BI0/WHCOSTELMNT.
#5
Thomas (Tuesday, 02 June 2020 17:00)

Hi,

How to filter out by many key fields?
E.g. i have 20 key fields. But for a combination of 3 of them i need the lastest (rank 1 over those three)
#6
Tobias (Wednesday, 03 June 2020 15:40)

Hi Thomas,

a DTP Filter on a fields can not be combined. Each field is considerd as a single one. If you need a combination of 3 or more fields, you need a field as a concatination of the 3 fields. Otherwise you have an AND combination and you get no or less data.

I hope this help you a little bit.
#7
Rajan (Wednesday, 24 August 2022 15:06)

Hi
I have field where only records with numeric values should be allowed. Alphanumeric must be filtered. Is it possible to write routine in dtp filter for the requirement? Thanks in advance.
#8
Idriss (Tuesday, 30 August 2022 18:00)

Hello Thomas,

thank you very much for the interesting blog. I have the following scenario. I am trying to load the hierarchy from 0CUST_SALES into the InfoObject 0CUSTOMER. Because of the compoundings of 0CUST_SALES, the loading fails because of duplicate records. Do you have an idea how I can use the DTP filter to delete duplicate records regarding Customer? Many thanks in advance.
#9
Tobias (Wednesday, 14 September 2022 13:36)

Hi,

sorry for the late answer. I was on vacation.

@Rajan: Of course, check the function module NUMERIC_CHECK
@Idriss: Yes use the DELETE ADJACENT DUPLICATES statement to delete the duplicate entries
#10
Idriss (Monday, 20 February 2023 09:10)

Hi Thomas,

thank you very much for your answer!

Beste regards
Idriss

SAP Business Warehouse DTP Filter with ABAP Routine

Conclusion

author.

Subscribe