How-to Guide SAP NetWeaver ‘04

How To… Load a Flat File into BW-BPS Using a Web Browser Version 3.0 – November 2006 Applicable Releases: SAP NetWeaver ’04 (as of SAP BW 3.5, SAP SEM 3.1)

© Copyright 2006 SAP AG. All rights reserved. No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, OS/2, Parallel Sysplex, MVS/ESA, AIX, S/390, AS/400, OS/390, OS/400, iSeries, pSeries, xSeries, zSeries, z/OS, AFP, Intelligent Miner, WebSphere, Netfinity, Tivoli, and Informix are trademarks or registered trademarks of IBM Corporation in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc. HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape. MaxDB is a trademark of MySQL AB, Sweden. SAP, R/3, mySAP, mySAP.com, xApps, xApp, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Data

contained in this document serves informational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty. These materials are provided “as is” without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement. SAP shall not be liable for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these materials. SAP does not warrant the accuracy or completeness of the information, text, graphics, links or other items contained within these materials. SAP has no control over the information that you may access through the use of hot links contained in these materials and does not endorse your use of third party web pages nor provide any warranty whatsoever relating to third party web pages. SAP NetWeaver “How-to” Guides are intended to simplify the product implementation. While specific product features and procedures typically are explained in a practical business context, it is not implied that those features and procedures are the only approach in solving a specific business problem using SAP NetWeaver. Should you wish to receive additional information, clarification or support, please refer to SAP Consulting. Any software coding and/or code lines / strings (“Code”) included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, except if such damages were caused by SAP intentionally or grossly negligent.

1 Business Scenario This document describes a solution developed for BW-BPS that lets you prompt the end user for a file name (or specify it in a parameter group) and load the data directly from the file into BW-BPS. The purpose is to give the user an easy to use mechanism to load flat files using a web browser. Note: There’s a similar “How-to” paper that describes a solution for loading data into BW-BPS for SAPGUI based front-ends (planning workbench or planning folders).

Version History September 2003 Initial release of “How-to” paper. November 2003 BW release (no functional changes). December 2003 Chapter 8: Minor correction in INIT function module: Add message

class UPF. Chapter 11.2: Add include JS.HTM in layout.

August 2004 Chapter 11.4: Correction of BSP page required for BW 3.5 Support Pack 4.

July 2005 Chapter 4: Correction for session handling (logon/logoff). Use different start URL for the file upload.

March 2006 Chapter 12: Added new BSP version based on HTMLB (new design) November 2006 Chapter 12.3: Fix for CX_SY_REF_IS_INITIAL exception. Change

Event Handler: OnInitialization

2 The Result The data of the flat file is loaded into BW-BPS and is ready to be processed further or be saved into the underlying InfoCube.

- 1 -

3 The Step By Step Solution

1. Preliminary remarks The solution requires you to implement two ABAP function modules as part of a function group. Therefore you need to have basic programming skills and the authorizations to do the programming in your system.

2. Create a new function group

Run transaction SE37. Then select Goto Function Groups Create Group.

Enter a group name, a description and save it.

Select a package and save the definition to a transport.

3. Create function module A

In transaction SE37, create a function module according to appendix A.

Adjust the IF-statement according to your requirements in order to overwrite or add loaded to existing data. Note: You can make a copy of function

UPB_ALV_EXIT, which will provide you with the correct interface specification already.

4. Create function module B

In transaction SE37, create a function module according to appendix B.

The combination of characteristics values that is created at the end of this function must be a subset of the planning level.

Note: You can make a copy of function UPB_ALV_EXIT_INIT, which will provide you with the correct interface specification already.

- 2 -

5. Define the file structure in the global data of the function group

While you are editing the one of the function modules, select Goto Global data. Then maintain the file structure as defined in the example in appendix C.

Adjust the file structure to your requirements. You must use the technical names of the characteristics and key figures for a 1:1 field mapping.

You also need to change the type definitions to reflect the client and planning area you are working on.

6. Save all and activate all programs

- 3 -

7. Set up the planning framework

Define a planning level that includes all characteristics and key figures that you want to load.

(It is possible to select more characteristics and key figures than are in the file. However they will remain empty.)

Create a planning package that corresponds to the contents of the file.

- 4 -

8. Create a planning function

You can now create a planning function of type “Exit function” for the given planning level.

Then specify the two function modules A and B, which you implemented in steps 3 and 4.

Important: All fields of the level need to be included in the list of fields to be changed.

- 5 -

9. Create a parameter group

There are no parameters for the function. Simply create an empty parameter group.

10. Create a new web interface

Run transaction BPS_WB. Click on the “new” button and create a new web interface containing just one page.

Note: The web interface is actually not used by the file upload. However, the file upload web page will make use of the generated BSP application.

11. Generate the web interface

Click on “Generate” (Edit Generate). Enter a description and generate the web interface.

- 6 -

12. Add a new BSP page to the web interface.

Run transaction SE80. In the repository browser, select the BSP application that matches the technical name of the web interface (ZFILE).

13. Add a new BSP page

Right-click on “Page with Flow Logic” and select “Create”. Enter the page name (ZFILE_UPLOAD.htm) and a description.

Maintain the properties, layout, event handler, and page attributes according to Appendix E.

Note: If the BPS web interface is based on the new design (HTMLB), then use Appendix F instead of E.

In the event handler, adjust the planning objects of the API call according to the planning area, level, package, function, and parameter group that you have specified in steps 7 to 9.

14. Activate the BSP page

Check and activate the new BSP page.

- 7 -

- 8 -

4 Start the File Upload

1. Determine the start URL

In order to ensure correct session handling (logon/logoff), the start URL needs to point to the original web interface. The file upload page is passed as a parameter to the web interface.

General format of the start URL:

http://<server>:<port>/sap/bc/bsp/sap/<web_interface>/<web_interface>.HTM?bps-page=<upload_page>.HTM

If you follow the naming used in this how-to paper, then the URL would be:

http://<server>:<port>/sap/bc/bsp/sap/ZFILE/ZFILE.HTM?bps-page=ZFILE_UPLOAD.HTM

Note: Check the capitalization in SE80.

2. Start the BSP application

Paste the URL into the address bar of your browser. Of course the URL can be assigned to a portals role or stored as a local bookmark.

Note: The URL in the screen shots is just an example.

3. Select the upload file and start the upload.

Either type in the file name or click the “Browse” button and select the file.

Then click on the “Upload” button.

The system will now read the file and start the planning function to process the file contents. The data will be stored in the BPS buffer (same as with any other planning function).

- 9 -

4. Save the data

Click the “Save” button to store the data in BPS.

The system will display either a success or an error message.

5. Exit the BSP application

If the data was successfully loaded and saved, you click the “Exit” button to release the locked data and make it available for further processing in BPS.

- 10 -

5 Comments • The load function is solely based on internal values. For example, dates have to

be loaded in the YYYYMMDD format.

• In the field mapping, pay extra attention to leading zeros as they are often not included in files but required by InfoObjects.

• You need to make assumptions regarding decimal notation and date/time formats. Either stick to one fixed format or foresee different conditions in the function module.

• The field mapping is straightforward in the example (move-corresponding) since the technical names of the characteristics and key figures are used to define the file structure. Of course, you can add more complex mapping rules in the LOOP-statement of the function module A.

• If the combination of characteristics values that is created at the end of function module B is NOT a subset of the planning level, you will get error “The generated data is not contained in the selection condition.” (UPC 204).

• All coding lines that need to be changed to match your configuration are marked by: “ <<<< ADJUST

• Do not re-generate the Web Interface! This would delete the new File Upload BSP page.

• The BSP application needs to follow the old or new design of the BPS web interface. In the web interface builder, the new design will have XML version >= 300 (Web Interface > XML View).

6 Summary When implementing this exit planning function you need only few coding adjustments to fit to your requirements and setup of BW-BPS. The user gets a very easy to use function to load data from a local flat file via a web browser into BW-BPS.

- 11 -

7 Appendix A: Load Function FUNCTION z_sem_bps_exit_file_load. *"-------------------------------------------------------------------- *"*"Local interface: *" IMPORTING *" REFERENCE(I_AREA) TYPE UPC_Y_AREA *" REFERENCE(I_PLEVEL) TYPE UPC_Y_PLEVEL *" REFERENCE(I_PACKAGE) TYPE UPC_Y_PACKAGE *" REFERENCE(I_METHOD) TYPE UPC_Y_METHOD *" REFERENCE(I_PARAM) TYPE UPC_Y_PARAM *" REFERENCE(IT_EXITP) TYPE UPF_YT_EXITP *" REFERENCE(ITO_CHASEL) TYPE UPC_YTO_CHASEL *" REFERENCE(ITO_CHA) TYPE UPC_YTO_CHA *" REFERENCE(ITO_KYF) TYPE UPC_YTO_KYF *" EXPORTING *" REFERENCE(ET_MESG) TYPE UPC_YT_MESG *" CHANGING *" REFERENCE(XTH_DATA) TYPE HASHED TABLE *"-------------------------------------------------------------------- DATA: lt_data TYPE yt_data, ls_data TYPE ys_data. DATA: ls_file TYPE ys_file. * Has any data been loaded? CHECK NOT gt_file[] IS INITIAL. * Overwrite existing data or add to existing data? * Change the IF depending on which alternative you require IF 1 = 1. “ <<<< ADJUST REFRESH lt_data. " Delete and overwrite existing data ELSE. lt_data = xth_data. " Keep existing data ENDIF. * Map and merge loaded data LOOP AT gt_file INTO ls_file. CHECK sy-tabix > 1. " Skip first line with field names CLEAR ls_data. MOVE-CORRESPONDING ls_file TO ls_data-s_chas. " Map characteristics MOVE-CORRESPONDING ls_file TO ls_data-s_kyfs. " Map key figures COLLECT ls_data INTO lt_data. ENDLOOP. * Return loaded data to BPS xth_data = lt_data. * Clear temporary table REFRESH gt_file. ENDFUNCTION.

- 12 -

8 Appendix B: Init Function (Web) FUNCTION z_sem_bps_exit_file_web. *"---------------------------------------------------------------------- *"*"Local interface: *" IMPORTING *" REFERENCE(I_AREA) TYPE UPC_Y_AREA *" REFERENCE(I_PLEVEL) TYPE UPC_Y_PLEVEL *" REFERENCE(I_PACKAGE) TYPE UPC_Y_PACKAGE *" REFERENCE(I_METHOD) TYPE UPC_Y_METHOD *" REFERENCE(I_PARAM) TYPE UPC_Y_PARAM *" REFERENCE(IT_EXITP) TYPE UPF_YT_EXITP *" REFERENCE(ITO_CHASEL) TYPE UPC_YTO_CHASEL *" REFERENCE(ITO_CHA) TYPE UPC_YTO_CHA *" REFERENCE(ITO_KYF) TYPE UPC_YTO_KYF *" EXPORTING *" REFERENCE(ETO_CHAS) TYPE ANY TABLE *" REFERENCE(ET_MESG) TYPE UPC_YT_MESG *"---------------------------------------------------------------------- CONSTANTS: c_memory_id_file_upload TYPE memory_id VALUE 'Z_SEM_BPS_FILE_UPLOAD'. DATA: lto_chas TYPE yto_chas, ls_chas TYPE ys_chas, ls_mesg TYPE upc_ys_mesg, ls_chasel TYPE upc_ys_chasel, ls_charng TYPE upc_ys_charng, l_dummy(72) TYPE c. DATA: ls_file TYPE ys_file, l_content TYPE string, l_line TYPE string, l_filelen TYPE i. FIELD-SYMBOLS: <f> TYPE ANY. REFRESH gt_file. * Retrieve file content from memory IMPORT content TO l_content FROM MEMORY ID c_memory_id_file_upload. IF sy-subrc <> 0 OR l_content IS INITIAL. * No data loaded MESSAGE e001(upf) INTO l_dummy WITH 'No data in file'. “<<<CHANGE CLEAR ls_mesg. MOVE-CORRESPONDING syst TO ls_mesg. APPEND ls_mesg TO et_mesg. EXIT. ENDIF. FREE MEMORY ID c_memory_id_file_upload. TRY. * Convert file content into structured table * TAB-delimited file PERFORM gui_char_conv TABLES gt_file " data table USING l_content " file contents 'X' " has_field_separator 'X' " read_by_line space " dat_mode space " codepage 'X' " ignore_cerr '#' " replacement CHANGING l_filelen. " file length

- 13 -

CATCH cx_root. MESSAGE e001(upf) INTO l_dummy WITH 'Data conversion error.'. CLEAR ls_mesg. MOVE-CORRESPONDING syst TO ls_mesg. APPEND ls_mesg TO et_mesg. EXIT. ENDTRY. * Create one dummy combination * If we don't do this, the upload won't work since the second function * will not be executed at all in case no transaction data exists so far. * The combination must be a subset of the planning level! CLEAR ls_chas. LOOP AT ito_chasel INTO ls_chasel. READ TABLE ls_chasel-t_charng INTO ls_charng INDEX 1. IF sy-subrc = 0. ASSIGN COMPONENT ls_chasel-chanm OF STRUCTURE ls_chas TO <f>. IF sy-subrc = 0. <f> = ls_charng-low. ENDIF. ENDIF. ENDLOOP. COLLECT ls_chas INTO lto_chas. eto_chas = lto_chas. ENDFUNCTION. *&--------------------------------------------------------------------* *& Form gui_char_conv *&--------------------------------------------------------------------* * This routine is basically a copy of FORM gui_char_upload as * used in the function module GUI_UPLOAD. The difference is that * the file contents are passed as a parameter (STRING) instead * of being read from the front-end. *---------------------------------------------------------------------* FORM gui_char_conv TABLES par_data_tab USING par_file_contents TYPE string value(par_has_field_separator) TYPE c value(par_read_by_line) TYPE c value(par_dat_mode) TYPE c value(par_encoding) TYPE abap_encoding value(par_ignore_cerr) TYPE abap_bool value(par_replacement_char) TYPE abap_repl CHANGING par_filesize TYPE i. DATA: prc_error TYPE i VALUE 0, prc_position TYPE i VALUE 0. DATA: prc_dest_buffer TYPE string. DATA: strdecimal TYPE c, npacked TYPE p DECIMALS 2 VALUE 0, strtemp(10) TYPE c. DATA: strdatformat(10) TYPE c, ddate TYPE d VALUE '11112233', strfield(10) TYPE c, noffset TYPE i. * DATA: conv TYPE REF TO cl_abap_conv_in_ce, * prc_encoding(20) TYPE c. * * prc_encoding = par_encoding.

- 14 -

CLASS cl_abap_char_utilities DEFINITION LOAD. " Determine decimal delimiter CLEAR strtemp. WRITE npacked TO strtemp(10). SEARCH strtemp FOR ','. IF sy-subrc EQ 0. strdecimal = ','. ELSE. strdecimal = '.'. ENDIF. " Determine date format WRITE ddate TO strfield DD/MM/YYYY. IF strfield CS '33'. ENDIF. noffset = sy-fdpos. CASE noffset. WHEN 0. strdatformat = 'DD.MM.YYYY'. WHEN 3. strdatformat = 'MM/DD/YYYY'. WHEN 8. strdatformat = 'YYYY/MM/DD'. ENDCASE. DATA: prc_strinput TYPE string. prc_strinput = par_file_contents. * If this upload should disregard CR/LF we need the * size of the first component and it must be * of a character type. IF par_read_by_line IS INITIAL. FIELD-SYMBOLS: <first_field> TYPE ANY. DATA: prc_data_type TYPE c VALUE space, prc_dest_data_length TYPE i VALUE 0. ASSIGN COMPONENT 1 OF STRUCTURE par_data_tab TO <first_field>. IF sy-subrc = 0. DESCRIBE FIELD <first_field> TYPE prc_data_type. IF prc_data_type <> 'C' AND prc_data_type <> 'g'. RAISE bad_data_format. ENDIF. DESCRIBE FIELD <first_field> LENGTH prc_dest_data_length IN CHARACTER MODE. IF sy-subrc <> 0. RAISE bad_data_format. ENDIF. ELSE. RAISE bad_data_format. ENDIF. ENDIF. DO. CLEAR prc_dest_buffer. DATA: prc_strinputlength TYPE i. prc_strinputlength = strlen( prc_strinput ). IF prc_strinputlength = 0. EXIT. ENDIF. * Find EOL in source buffer IF par_read_by_line IS NOT INITIAL.

- 15 -

* Check if we have found a complete line and write it * to the data table. * Check for CR/LF FIND cl_abap_char_utilities=>cr_lf IN prc_strinput MATCH OFFSET prc_position. IF sy-subrc = 0. MOVE prc_strinput(prc_position) TO prc_dest_buffer. prc_position = prc_position + 2. SHIFT prc_strinput LEFT BY prc_position PLACES. ELSE. * Check for LF FIND cl_abap_char_utilities=>cr_lf+1(1) IN prc_strinput MATCH OFFSET prc_position. IF sy-subrc = 0. MOVE prc_strinput(prc_position) TO prc_dest_buffer. prc_position = prc_position + 1. SHIFT prc_strinput LEFT BY prc_position PLACES. ELSE. * Check for CR FIND cl_abap_char_utilities=>cr_lf(1) IN prc_strinput MATCH OFFSET prc_position. IF sy-subrc = 0. MOVE prc_strinput(prc_position) TO prc_dest_buffer. prc_position = prc_position + 1. SHIFT prc_strinput LEFT BY prc_position PLACES. ELSE. MOVE prc_strinput TO prc_dest_buffer. prc_strinput = ''. ENDIF. ENDIF. ENDIF. ELSE. * Check if the data read so far is longer than one line of the * data table. IF prc_dest_data_length > prc_strinputlength. prc_dest_data_length = prc_strinputlength. ENDIF. MOVE prc_strinput(prc_dest_data_length) TO prc_dest_buffer. SHIFT prc_strinput LEFT BY prc_dest_data_length PLACES. ENDIF. PERFORM write_line_to_data_tab IN PROGRAM saplsfes TABLES par_data_tab USING par_has_field_separator strdecimal strdatformat par_dat_mode CHANGING prc_dest_buffer. ENDDO. ENDFORM. " gui_char_conv

- 16 -

9 Appendix C: Global Data (File Structure) FUNCTION-POOL z_file. TYPE-POOLS: abap. * File structure: * The following type declaration defines the order and length of the fields * in the file that should be loaded into BW-BPS. * ADJUST >>>> TYPES: BEGIN OF ys_file, 0version(3), 0fiscvarnt(2), 0fiscyear(4), 0company(6), 0currency(5), 0amount(25), END OF ys_file. * <<<< ADJUST * File data: * Global table to temporarily store loaded data. DATA: gt_file TYPE STANDARD TABLE OF ys_file WITH DEFAULT KEY. * Type definitions: * Structures and tables that correspond to the planning area. * Note: Replace client number (800) and area (ZFILE) corresponding to your setup. * ADJUST >>>> TYPES: yth_data TYPE /1sem/_yth_data_800zfile, yt_data TYPE /1sem/_yt_data_800zfile, ys_data TYPE /1sem/_ys_data_800zfile, yto_chas TYPE /1sem/_yto_chas_800zfile, ys_chas TYPE /1sem/_ys_chas_800zfile, ys_kyfs TYPE /1sem/_ys_kyfs_800zfile. * <<<< ADJUST

- 17 -

- 18 -

10 Appendix D: Example File Assumption for the example:

a) First line of the file contains field descriptions (maximum length in brackets)

b) Version field has leading zeros

c) Decimal point is used for amounts

d) Save the data as “Text (Tab delimited)” Example file: 0version(3) 0fiscvarnt(2) 0fiscyear(4) 0company(6) 0currency(5) 0amount(25)

001 K4 2002 C1000 EUR 1234.56 001 K4 2002 C2000 GBP 789.01 001 K4 2003 C1000 EUR 23.45 001 K4 2003 C2000 GBP 67.89

www.sdn.sap.com/irj/sdn/howtoguides