SAP BW: Create a fixed length file with Open Hub Destination

At my current project we are using SAP BW and its Open Hub Destination (Open Hub) object to create a number of flat file interfaces to export data from SAP BW to other systems. An Open Hub creates a comma separated value (CSV) file which can be placed on a local directory or on an application server. The Open Hub uses a standard transformation and DTP to transform and load data from an InfoCube, DSO or InfoObject. If you are exporting to an application server you can also use Process Chains for scheduling.

Now we got this fancy old-school requirement to create an interface in which the files should be fixed length instead of CSV. Great. Since the Open Hub uses a mandatory separator (the comma, or in most cases a semicolon) to separate the fields and it does not have any options to determine the exact position of each field in a record in the file, a workaround is needed to make this work.

Update: Thomas Brandt mentioned in the comments below this post that with SAP BW 7.3 it now is possible to export to CSV, ASCII and XML. Check SAP Help for more info. If your BW hasn’t been upgraded to 7.3 you can still use the workaround.

In this example I will create an Open Hub called ZFIXED on top of the 0PERSON InfoObject which generates a fixed length file. The file will contain 4 fields according to the specs below:

1. Person, position 1 to 10;
2. City, position 11 to 20;
3. Country, position 21 to 30;
4. Date of birth, position 31 to 38.

Let’s see how this works:1. First create your Open Hub. Do not select a template. We don’t need this.

2. Choose Destination Type File and enter the file directory in which you want the file to be exported. For this example I just use a local directory. In a production scenario you should use the Application Server option since you then can export the file to an application server and use logical file names. Also it is not possible to use the DTP between your source object and the Open Hub in a Process Chain if you use a local directory.

3. Go to the Field Def. tab. Here you enter a name for your field (i.e. FIELD), use Type STRG and 38 for Length. Now Activate the Open Hub.

4. Create a transformation from 0ZPERSON to the Open Hub ZFIXED. Connect the InfoObjects you want to use from 0PERSON to ZFIXED. I connected 0PERSON, 0CITY, 0COUNTRY and 0DATEBIRTH.

5. Change the Rule Type into Routine. We need some simple ABAP coding to merge the 4 fields and set the correct starting positions for each field. Scroll down until you see “RESULT = .” and add the code below.

    DATA: lv_char TYPE c LENGTH 38.

lv_char(10) = SOURCE_FIELDS-PERSON.
lv_char+10(10) = SOURCE_FIELDS-CITY.
lv_char+20(10) = SOURCE_FIELDS-COUNTRY.
lv_char+30(8) = SOURCE_FIELDS-DATEBIRTH.

RESULT = lv_char.

 

6. Activate the transformation, create a DTP and Execute it!

7. You can now open the file in Notepad.exe. It should look something like this:

HackingSAP.com - Mar 28, 2012 | Knowledge sharing, SAP BW
Tagged | Open Hub, Open Hub Destination, SAP BW