Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Applies to: SAP BW 3.5, BI 7.0. For more information, visit Business Intelligence homepage.  
Summary

This document guides the user to generate an ABAP executable program which helps the user to create an excel file, fill it with necessary data and then place that particular excel formatted file in the path required. 
Author(s):    Koundinya Karanam

Company:    Mahindra Satyam

Created on:  29 September 2009

Author Bio

Koundinya Karanam is a SAP BI Consultant working for Mahindra Satyam. Skill set includes SAP Business Intelligence, SAP POS, SAP Integrated Planning and Business Objects.   

Introduction

In SAP BW, using Open Hub, we can generate a CSV formatted file and place that in the required path (either Application server or local machine). Now by using this ABAP executable program, we can generate a file in excel format with the required fields and place that particular file in a specified path required. We can also automate the process of sending the excel file as an attachment in the mail and send it to as many mail id's as required.

Business Scenario

In our daily scenario's, we need to send BI reports to Top level Management or Middle Level Management users. To generate the report and perform Export to Excel file and to send the file to user mail box, much manual intervention is necessary. So in order to generate a report and export the same to an excel file automatically and place it in the specified path (either local machine path or Application server path or Network path) and also to send the excel file as an attachment to different mail id's, we need to follow the below process.

To convey the same, I have considered two business scenarios. The first scenario specifies to download the data in the excel sheet and place that in a specified path - the student's data will be placed retrieving the necessary data from an ODS and then download, place the excel file in a specified path present in your local machine. The second scenario is that we will send the same excel file as an attachment without downloading it to our local machine which will be automated. These two scenarios are achieved with the help of an ABAP Executable program. 

Functionalities of the program:

Ø  This program fetches the data from a table and places it in an excel file.

Ø  The excel file will be downloaded and placed in a specified path (either in Local machine or Application server path or Network area path)

Ø  The excel file can be sent as an attachment to many number of mail id's marking them in Copy and Blind Copy too.  Note: The Table or ODS or Infoobject which we are using should be readily available in BW.
 

Step-by-Step Procedure for Implementing the Program

This program is created in BW server.
STEP 1: Creation of Program

-          Go to TCODE: SE38.

-          Create an executable program ZTEST_FILE_DNLD. 
 
-          Give the necessary title and the Attributes (Type, Status etc...). 
-          Now it prompts to save this program in a package. Fill the required fields with the package name which you use in your project.

-          Go ahead providing a new Transport request or assign an old transport request if exists and click ok to move ahead. 
 
-          Now the ABAP editor is in front of you. Copy and paste the below code in the ABAP editor and save.  

Business Scenario 1:

To download the excel file and place it in a specified path. 
*Declaration of necessary fields*TYPES: BEGIN OF ty_output,
         stud_id TYPE /bi0/oistu_id,
         zmarks1 TYPE /bic/oiz_marks1,
         zmarks2 TYPE /bic/oiz_marks2,
         zmarks3 TYPE /bic/oiz_marks3,
       END OF ty_output,
          BEGIN OF ty_output2,
         /bic/stu_id(15)
             /bic/z_marks1(15),
             /bic/z_marks2(15),
             /bic/z_marks3(15),
       END OF ty_output2.
**Declaration of data specifying the name of the path where the downloaded file is to be placed.

DATA: ofname    TYPE string,
      fpath     TYPE string VALUE 'C:\Temp\',

      it_output TYPE STANDARD TABLE OF ty_output,
      wa_output TYPE ty_output. **To display the header in the file, we need to append the data required, as mentioned in the below code.
DATA: it_output2 TYPE STANDARD TABLE OF ty_output2,
      wa_output2 TYPE ty_output2.
               
wa_output2-/bic/stu_id = 'Student ID'.
wa_output2-/bic/z_marks1 = 'Subject Marks1'.
wa_output2-/bic/z_marks2 = 'Subject Marks2'.
wa_output2-/bic/z_marks3 = 'Subject Marks3'.

APPEND wa_output2 TO it_output2.

CLEAR wa_output2.
 **Usage of Select statement which fetches the data from active table of an ODS where the data resides in BW, and to place the data in an internal table.
SELECT /bic/stu_id /bic/z_marks1 /bic/z_marks2 /bic/z_marks3 FROM /bic/azod_stu00 INTO TABLE it_output.
 **Concatenation of the display of Date and Time, Current (Application Server) Date for the file name.

      CONCATENATE fpath   'Student_Marks' sy-datum '.xls'
        INTO ofname. **Now we are calling the function "GUI_DOWNLOAD" which transfer the data of an internal table from the server to a file on your local machine. The module "Gui_Download" replaces the obsolete modules Ws_Download and Download. The file dialog of the download module is available in class Cl_Gui_Frontend_Services. For more info on this, kindly refer to the SAP documentation of this function.
 CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
      filename                = ofname
      filetype                = 'ASC'
      write_field_separator   = 'X'

  TABLES
    data_tab                        = it_output2

 EXCEPTIONS
   file_write_error                = 1
   no_batch                        = 2
   gui_refuse_filetransfer         = 3
   invalid_type                    = 4
   no_authority                    = 5
   unknown_error                   = 6
   header_not_allowed              = 7
   separator_not_allowed           = 8
   filesize_not_allowed            = 9
   header_too_long                 = 10
   dp_error_create                 = 11
   dp_error_send                   = 12
   dp_error_write                  = 13
   unknown_dp_error                = 14
   access_denied                   = 15
   dp_out_of_memory                = 16
   disk_full                       = 17
   dp_timeout                      = 18
   file_not_found                  = 19
   dataprovider_exception          = 20
   control_flush_error             = 21
   OTHERS                          = 22.
          
IF sy-subrc <> 0.

ENDIF.
 
CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
      filename                = ofname
      filetype                = 'ASC'
      append                  = 'X'
      write_field_separator   = 'X'
  TABLES
    data_tab                        = it_output
 EXCEPTIONS
   file_write_error                = 1
   no_batch                        = 2
   gui_refuse_filetransfer         = 3
   invalid_type                    = 4
   no_authority                    = 5
   unknown_error                   = 6
   header_not_allowed              = 7
   separator_not_allowed           = 8
   filesize_not_allowed            = 9
   header_too_long                 = 10
   dp_error_create                 = 11
   dp_error_send                   = 12
   dp_error_write                  = 13
   unknown_dp_error                = 14
   access_denied                   = 15
   dp_out_of_memory                = 16
   disk_full                       = 17
   dp_timeout                      = 18
   file_not_found                  = 19
   dataprovider_exception          = 20
   control_flush_error             = 21
   OTHERS                          = 22.
          
IF sy-subrc <> 0.

ENDIF. 

Business Scenario 2:

To send the excel file as an attachment to mail id's specified without saving the excel file in a path in our local machine. TYPES: BEGIN OF ty_rec,
        bname TYPE xubname,
        persnumber TYPE ad_persnum,
        smtp_addr TYPE ad_smtpadr,
       END OF ty_rec.

TYPES: BEGIN OF ty_out,
         stud_id TYPE /bi0/oistu_id,
         zmarks1 TYPE /bic/oiz_marks1,
         zmarks2 TYPE /bic/oiz_marks2,
         zmarks3 TYPE /bic/oiz_marks3,
       END OF ty_out.

DATA: it_attach TYPE STANDARD TABLE OF solisti1,
      wa_attach TYPE solisti1,

      it_rec TYPE STANDARD TABLE OF ty_rec,
      wa_rec TYPE ty_rec.

DATA: it_out TYPE STANDARD TABLE OF ty_out,
      wa_out TYPE ty_out.

SELECT /bic/stu_id /bic/z_marks1 /bic/z_marks2 /bic/z_marks3 FROM /bic/azod_stu00 INTO TABLE it_out.

PERFORM build_xls_data.
*PERFORM get_recepients.
PERFORM send_mail.

*&---------------------------------------------------------------------*
*&      Form  build_xls_data
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM build_xls_data.
  CONSTANTS:
      con_tab  TYPE c VALUE cl_abap_char_utilities=>horizontal_tab,
      con_cret TYPE c VALUE cl_abap_char_utilities=>cr_lf.

  CONCATENATE 'Student ID' 'Subject Marks1' 'Subject Marks2' 'Subject Marks3'
           INTO wa_attach SEPARATED BY con_tab.
  CONCATENATE con_cret wa_attach
           INTO wa_attach.
  APPEND wa_attach TO it_attach.
  CLEAR wa_attach.

  LOOP AT it_out INTO wa_out.
    DATA: lv_sub1(20),
          lv_sub2(20),
          lv_sub3(20).

          lv_sub1 = wa_out-zmarks1.
          lv_sub2 = wa_out-zmarks2.
          lv_sub3 = wa_out-zmarks3.

    CONCATENATE wa_out-stud_id lv_sub1 lv_sub2 lv_sub3 
             INTO wa_attach SEPARATED BY con_tab.

    CONCATENATE con_cret wa_attach
             INTO wa_attach.
    APPEND wa_attach TO it_attach.

    CLEAR: wa_attach,
           wa_out.
  ENDLOOP.
ENDFORM.

"build_xls_data

*&---------------------------------------------------------------------*
*&      Form  send_mail
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM send_mail.
  DATA: lv_document_data TYPE sodocchgi1.
* Body of the mail
  DATA: contents TYPE TABLE OF solisti1 WITH HEADER LINE.
* Recepient list.
  DATA: lv_receivers TYPE TABLE OF somlreci1,
        wa_receivers TYPE somlreci1.

  DATA: lv_packing_list TYPE TABLE OF sopcklsti1,
        lv_p_item TYPE sopcklsti1,
        lv_date LIKE sy-datum,
        len TYPE int4,
        doc_count TYPE i.

  lv_document_data-obj_descr = 'Student Marks Report'.
  lv_document_data-sensitivty = 'P' .
  lv_document_data-doc_size = 1.
  READ TABLE it_attach INTO wa_attach INDEX doc_count.
  lv_document_data-doc_size = ( doc_count - 1 ) * 255 + STRLEN( wa_attach ).

* Body of the mail.
*Below code represents the body of the mail. "text-001" contains the contents of the first line, 
*"text-002" contains the contents of the second line and so on
  

Creation of Text Elements:

 Create the following Text elements and activate. We can also create text elements by double clicking on the serial number assigned to the text in the program (text-001, text002, text003, text004) which would take us to the Text elements screen.
  
 
  MOVE text-001 TO contents.
  APPEND contents.
  CLEAR contents.

  MOVE text-002 TO contents.
  APPEND contents.
  CLEAR contents.

  MOVE text-003 TO contents.
  APPEND contents.
  CLEAR contents.

  MOVE text-004 TO contents.
  APPEND contents.
  CLEAR contents.

*If we need to send this mail in "TO" list, please refer to the below code.

  wa_receivers-receiver = 'XXXXXXX@YYYY.com'.
  wa_receivers-rec_type = 'U'.
  wa_receivers-no_forward = 'X'.
  APPEND wa_receivers TO lv_receivers.

*If we need to copy this mail to any of the recepients, please refer to the below code.

  wa_receivers-receiver = 'XXXXXXX@YYYY.com'.
  wa_receivers-rec_type = 'U'.
  wa_receivers-copy = 'X'.
  wa_receivers-no_forward = 'X'.
  APPEND wa_receivers TO lv_receivers.

  DESCRIBE TABLE contents LINES len.
  lv_p_item-body_start = 1.
  lv_p_item-body_num = len.
  lv_p_item-doc_type = 'RAW'.
  APPEND lv_p_item TO lv_packing_list.

  DESCRIBE TABLE it_attach LINES len.
  lv_p_item-transf_bin = 'X'.
  lv_p_item-head_num   = 1.
  lv_p_item-head_start = 1.
  lv_p_item-body_start = 1.
  lv_p_item-body_num = len.
  lv_p_item-doc_type   =  'XLS'.
  lv_p_item-obj_descr  =  'Student Marks Report'.
  lv_p_item-obj_name   =  'Marks Report'.
  lv_p_item-doc_size   =  len * 255.
  APPEND lv_p_item TO lv_packing_list.

*In the below code, "text-005" refers to the sender address. 

  CALL FUNCTION 'SO_DOCUMENT_SEND_API1'
    EXPORTING
      document_data              = lv_document_data
      put_in_outbox              = 'X'
      sender_address             = text-005
      sender_address_type        = 'B'
      commit_work                = 'X'
    TABLES
      packing_list               = lv_packing_list
      contents_bin               = it_attach
      contents_txt               = contents
      receivers                  = lv_receivers
    EXCEPTIONS
      too_many_receivers         = 1
      document_not_sent          = 2
      document_type_not_exist    = 3
      operation_no_authorization = 4
      parameter_error            = 5
      x_error                    = 6
      enqueue_error              = 7
      OTHERS                     = 8.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
            WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

MESSAGE 'mail sent successfully' TYPE 'I'.

ENDFORM.                    "send_mail
  
Now 'Check', 'Save' and 'Activate' the program. Check for any Syntax errors and modify the code if necessary.

Now we can go ahead executing the program to check the functionality of placing the file of Excel format in the necessary path and also sending the Excel file as an Attachment.    

Related Content

Please find below references related to the above content. 
An ABAP Program to Effortlessly Check Data Load Status in BW 
ABAP WORKBENCH Overview 
Using Programs to Retrieve Data

Copyright

© Copyright 2009 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, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.

IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.

Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated 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.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, 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 other countries.

Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company.

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.

  • No labels