Skip to Content

Introduction:

Many companies are still making their notes/comments in excel. These comments has to be saved in BPC Models on different dimensions combination.

This blog will explain how to load Mass Comments in BPC models.

Approach:

For Mass uploading of comments in BPC Models, we can write Customize program (Code has mentioned in the end) in BW.

Assume customize Program name: ZBPC_COMMENT****

Execute ZBPC_COMMENT_*** in SE38. Next screen would be as below:

 

Variables:

1.Variable “FNAME”-> Browse file from Local System.

Below is the format of excel file where you want to upload comments.

Change ‘DATEWRITTEN’ field to “Number’ format without any decimal values. In the File the    header name should be same as you using in program for Model dimensions.

 

2.Variable “TABLE”-> Give the technical name of the comment table.

to get comment table SE38-> UJ0_GET_GEN_TABNAME -> Execute-> Give Environment and Model Name-> Execute.

Get the comment table name from listed tables and put in TABLE  variable. Example: /1CPMB/*****

Click on execute.

3. TRUN: Before execution ,its better to test if the file format and path are correct. This is an Optional Step.

Program Detail:

Below mentioned Code can be pasted and enhance according to business requirement.

(For more detail how to guide about writing the program, find below reference links)

REPORT  ZUPDATE_BPCCOMMENTTABLE.


TYPES: BEGIN OF gty_s_string,
               line(5000) TYPE c,
        END OF gty_s_string.
DATA: gt_string TYPE TABLE OF gty_s_string.
FIELD-SYMBOLS: <gt_table>    TYPE  STANDARD TABLE.
FIELD-SYMBOLS: <gs_table>    TYPE  ANY.
DATA: gt_fieldcat1         TYPE lvc_t_fcat.

PARAMETERS : p_fname   TYPE string  OBLIGATORY .
PARAMETERS : p_table TYPE tvdir-tabname OBLIGATORY
              MATCHCODE OBJECT dd_dbtb_16.
PARAMETERS : c_trun AS CHECKBOX DEFAULT 'X'.

*======================================================================*
*                          INITIALIZATION
*======================================================================*
INITIALIZATION.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_fname.
   PERFORM file_open USING 'Select input file.' CHANGING p_fname.

AT SELECTION-SCREEN.
*Main logic
   PERFORM f_process_data.
*======================================================================*
*                          TOP-OF-PAGE
*======================================================================*
TOP-OF-PAGE.

*======================================================================*
*                          START-OF-SELECTION
*======================================================================*
START-OF-SELECTION.


  IF c_trun IS INITIAL.
     IF <gt_table>[] IS NOT INITIAL.
       MODIFY (p_table) FROM TABLE <gt_table>.
       COMMIT WORK.
     ENDIF.
   ENDIF.

FORM f_prepare_table USING value(pv_table).
   DATA: ls_tname TYPE tabname.
   ls_tname = pv_table.
   CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
     EXPORTING
       i_structure_name = ls_tname
     CHANGING
       ct_fieldcat      = gt_fieldcat1
     EXCEPTIONS
       OTHERS           = 1.
   IF sy-subrc NE 0 OR
      gt_fieldcat1[] IS INITIAL.
     STOP.
   ENDIF.
ENDFORM.                    " F_PREPARE_TABLE

FORM f_process_data .
   CASE sy-ucomm.
     WHEN  'ONLI'.
       DATA:  lr_dref  TYPE REF TO data.
       DATA:  ls_dref  TYPE REF TO data.
*build table
       CREATE DATA lr_dref TYPE STANDARD TABLE OF (p_table).
       ASSIGN lr_dref->* TO <gt_table>.
*Build table structure same as input table
       CREATE DATA ls_dref TYPE (p_table).
       ASSIGN ls_dref->* TO <gs_table>.
*Build output field structure
       PERFORM f_prepare_table  USING p_table.
*Upload data from local
       PERFORM f_upload_data_gui.
*Get data from table
       PERFORM f_build_data_table.
   ENDCASE.
ENDFORM.                    " F_PROCESS_DATA

FORM f_upload_data_gui .
   DATA : lv_leng TYPE i.
   DATA : lv_leng1 TYPE i.
   lv_leng = STRLEN( p_fname ).
   lv_leng1 = lv_leng - 3.
   IF p_fname+lv_leng1(3) EQ 'CSV'.
     PERFORM f_update_data_4m_csv.
   ELSE.
*     PERFORM f_update_data_4m_xls.
   ENDIF.
ENDFORM.                    " F_UPLOAD_DATA_GUI

FORM f_update_data_4m_csv .
   REFRESH: gt_string.
   CALL METHOD cl_gui_frontend_services=>gui_upload
     EXPORTING
       filename                = p_fname
     CHANGING
       data_tab                = gt_string
     EXCEPTIONS
       file_open_error         = 1
       file_read_error         = 2
       no_batch                = 3
       gui_refuse_filetransfer = 4
       invalid_type            = 5
       no_authority            = 6
       unknown_error           = 7
       bad_data_format         = 8
       header_not_allowed      = 9
       separator_not_allowed   = 10
       header_too_long         = 11
       unknown_dp_error        = 12
       access_denied           = 13
       dp_out_of_memory        = 14
       disk_full               = 15
       dp_timeout              = 16
       not_supported_by_gui    = 17
       error_no_gui            = 18
       OTHERS                  = 19.
   IF sy-subrc NE 0.
     MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
   ENDIF.
ENDFORM.                    " F_UPDATE_DATA_4M_CSV

FORM f_build_data_table .
   DATA: lt_value TYPE STANDARD TABLE OF string.
   DATA: gt_field TYPE STANDARD TABLE OF string.
   DATA: lv_string TYPE string.
   DATA: lv_value TYPE string.
   DATA: lv_field TYPE string.
   FIELD-SYMBOLS : <ls_value> TYPE ANY.

   LOOP AT gt_string INTO lv_string.
     IF sy-tabix EQ 1.
       SPLIT lv_string AT ',' INTO TABLE gt_field.
       DELETE gt_field WHERE table_line EQ space.
       DELETE gt_field WHERE table_line IS INITIAL.
     ELSE.
*populate file data
       REFRESH lt_value.
       SPLIT lv_string AT ',' INTO TABLE lt_value.
       LOOP AT lt_value INTO lv_value.
         CLEAR : lv_field.
         READ TABLE gt_field INTO lv_field INDEX sy-tabix.
         IF lv_field IS NOT INITIAL.
           UNASSIGN <ls_value>.
           ASSIGN COMPONENT lv_field OF STRUCTURE
               <gs_table> TO <ls_value>.
           IF <ls_value> IS ASSIGNED.
             CONDENSE : lv_value,lv_field.
             <ls_value> = lv_value.
           ENDIF.
         ENDIF.
       ENDLOOP.
     IF <gs_table> IS  NOT INITIAL.
         APPEND <gs_table> TO <gt_table>.
       ENDIF.
       CLEAR <gs_table>.
     ENDIF.
   ENDLOOP.
ENDFORM.                    " F_BUILD_DATA_TABLE

FORM file_open  USING value(pv_popup) TYPE string
                 CHANGING pv_filename TYPE string.   .
   CONSTANTS: gc_txt_imp TYPE string      VALUE 'Upload Data'.
   DATA gt_filetab       TYPE filetable.
   DATA g_subrc         TYPE i.
   DATA gwa_filetab     TYPE file_table.
   CLEAR gt_filetab[].
   IF pv_popup IS INITIAL.
     pv_popup = gc_txt_imp.
   ENDIF.
   CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
       window_title      = pv_popup
       initial_directory = 'C:\'
       file_filter       = '*.*'
     CHANGING
       file_table        = gt_filetab
       rc                = g_subrc
     EXCEPTIONS
       OTHERS            = 0.
   READ TABLE gt_filetab INTO gwa_filetab INDEX 1.
   CHECK sy-subrc EQ 0.
   pv_filename = gwa_filetab-filename.
ENDFORM.                    " FILE_OPEN




 

Conclusion:

By executing this program , we can save mass comments in BPC. It reduce lot of effort and save time for completing the requirement. Moreover we can mention User IDs who wanted to save particular Comment in System.

 

Reference Links:

How to create ABAP Program: https://www.wikihow.com/Create-an-ABAP-Program

How to create ABAP Program: http://www.saptraininghq.com/how-to-create-your-first-sap-abap-program/

 

Regards

Priyanka

 

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Varkey George

    Dear Priyanka,

    Thank you your Blog.

    There are two types of Comments which are kept outside BPC in MS files:

    1. On the Planning data – Quantity & Value – the Planner makes some assumptions to arrive at these numbers. These assumptions are recorded & reviewed by Managers as well. But these data lies outside BPC.
    2. On the Variance Analysis – Any explanation of the Variances of each Period are also in MS Files.

    These comments are specific to each Number for each Period & how to maintain these in BPC for future reference in a single Database/ System is the question.

     

    (0) 
    1. Priyanka Soni Post author

       

      Hi Varkey,

       

      Thanks for reading the blog.

      Your comment file should be unique with dimensions combination. If your file has multiple comments with same combination, while saving in BPC it will override the earlier comment and will keep the last one.

      Suggestion to use any dimension member to make your comments combination unique. Example : Planning comments in Audit ID =A, and Variance Analysis Audit ID=B.

      Once your file is ready you can save them either by using EPMSAVECOMMENT Function ( as mentioned by Vadim) or by given program. Please be careful about comments length as max length limit you need to check in Model structure – which can be 255/1332.

      Let me know if any concern.

      Thanks

      Priyanka

       

       

      (1) 

Leave a Reply