Technical Articles
Column to Row Transpose using BW Expert Routine
This blog post will explain a method to implement expert routine for column to row transpose on a table with huge number of columns. In a recent project I had a requirement to load a excel placed in SharePoint location to BW on HANA system. The data from excel will be used for reporting in WebI and Lumira. The issue with the excel is, the data is entered by the business in below format.
| Visitor’s Category | Jan-2021 | Feb-2021 | Mar-2021 | Apr-2021 | …………….. | Dec-2025 |
| Type 1 | ||||||
| Type 2 | ||||||
| ……….. | ||||||
| Type N |
i.e., the number of columns will keep increasing in future and its bit difficult to model the data for reporting if each member of a time dimension is a separate column.
The desired format for reporting would be
| Visitor’s Category | CalMonth | Value |
| Type 1 | ||
| Type 2 | ||
| ……….. | ||
| Type N |
While creating a data source using SDI based source system a virtual table will be created against the remote source. So, we had to freeze the number of columns (created columns till Dec-2025) to avoid changing the virtual table structure in future.
As BW is on top of HANA, I had the option to perform the transpose in two layers. Either transpose the data in the application layer using BW transformation or load the data in a staging ADSO and perform the transpose in database layer using native HANA modelling.
Transpose in database layer
Transposing using HANA modelling would be better since the operations are carried out in database layer. But the main drawback here is the number of columns in the source table. Implementing transpose using the native HANA modelling would require creating a calculated column or a projection for each column in the table (60 in this case). Below blogs explain in detail about implementing transpose using native HANA modelling.
https://blogs.sap.com/2014/01/02/table-transpose-in-sap-hana-modeling/
https://answers.sap.com/questions/12929729/transpose-columns-to-rows-in-sap-bw.html
https://archive.sap.com/documents/docs/DOC-50541
Transpose in application layer
Transpose can be done using routines or rule group in the BW transformation. In case of rule group, we need to create one rule for each column which is a tedious process for the number of columns are high. This approach is explained clearly in below document.
https://archive.sap.com/documents/docs/DOC-11537
So decided to perform the transpose using expert routine. The below code is short and simple which is not dependent on the number of columns to be transposed and easier to maintain that the above explained approaches.
Transpose in application layer using Expert routine
***Source Structure - System Defined***
TYPES:
BEGIN OF _ty_s_SC_1,
* Field: FIELDNAME Field Name.
FIELDNAME TYPE C LENGTH 200,
* Field: JUL16 Jul-16.
JUL16 TYPE C LENGTH 17,
* Field: AUG16 Aug-16.
AUG16 TYPE C LENGTH 17,
*
*****Other Months*****
*
* Field: NOV27 Nov-27.
NOV27 TYPE C LENGTH 17,
* Field: DEC27 Dec-27.
DEC27 TYPE C LENGTH 17,
* Field: RECORD Record Number.
RECORD TYPE RSARECORD,
END OF _ty_s_SC_1.
TYPES:
_ty_t_SC_1 TYPE STANDARD TABLE OF _ty_s_SC_1
WITH NON-UNIQUE DEFAULT KEY.
***Source Structure - System Defined***
***Target Structure - System Defined***
TYPES:
BEGIN OF _ty_s_TG_1,
* InfoObject: 0REQTSN Request Transaction Sequence Number.
REQTSN TYPE RSPM_REQUEST_TSN,
* InfoObject: 0DATAPAKID Data Package.
DATAPAKID TYPE /BI0/OIDATAPAKID,
* InfoObject: 0RECORD Data Record Number.
RECORD TYPE /BI0/OIRECORD,
* Field: TYPE Type.
TYPE TYPE C LENGTH 200,
* Field: ZCALMON ZCALMON.
ZCALMON TYPE C LENGTH 30,
* InfoObject: 0CALMONTH Calendar Year/Month.
CALMONTH TYPE /BI0/OICALMONTH,
* InfoObject: 0RECORDMODE BW Delta Process: Update Mode.
RECORDMODE TYPE RODMUPDMOD,
* Field: VALUE VALUE.
VALUE TYPE P LENGTH 9 DECIMALS 3,
END OF _ty_s_TG_1.
TYPES:
_ty_t_TG_1 TYPE STANDARD TABLE OF _ty_s_TG_1
WITH NON-UNIQUE DEFAULT KEY.
***Target Structure - System Defined***
***Data Declaration - Custom Code***
DATA: SRC_STRUC TYPE TABLE OF _TY_S_SC_1,
L_TABLEDESCR_REF TYPE REF TO CL_ABAP_TABLEDESCR,
L_DESCR_REF TYPE REF TO CL_ABAP_STRUCTDESCR,
FIELD_LIST TYPE ABAP_COMPDESCR,
TRG_DATA TYPE _TY_S_TG_1.
***Data Declaration - Custom Code***
***Routine Code - Custom Code***
L_TABLEDESCR_REF ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( SRC_STRUC )
.
L_DESCR_REF ?= L_TABLEDESCR_REF->GET_TABLE_LINE_TYPE( ).
FIELD-SYMBOLS: <FS> TYPE ANY.
DATA FIELD_NAME(5) TYPE C.
BREAK-POINT.
LOOP AT SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>.
CLEAR TRG_DATA.
LOOP AT L_DESCR_REF->COMPONENTS INTO FIELD_LIST .
IF FIELD_LIST-NAME = 'FIELDNAME'.
TRG_DATA-TYPE = <SOURCE_FIELDS>-FIELDNAME.
ELSEIF FIELD_LIST-NAME = 'RECORD'.
CONTINUE.
ELSE.
CLEAR: FIELD_NAME.
TRG_DATA-ZCALMON = FIELD_LIST-NAME.
CASE FIELD_LIST-NAME+0(3).
WHEN 'JAN'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '01' INTO
TRG_DATA-CALMONTH.
WHEN 'FEB'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '02' INTO
TRG_DATA-CALMONTH.
WHEN 'MAR'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '03' INTO
TRG_DATA-CALMONTH.
WHEN 'APR'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '04' INTO
TRG_DATA-CALMONTH.
WHEN 'MAY'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '05' INTO
TRG_DATA-CALMONTH.
WHEN 'JUN'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '06' INTO
TRG_DATA-CALMONTH.
WHEN 'JUL'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '07' INTO
TRG_DATA-CALMONTH.
WHEN 'AUG'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '08' INTO
TRG_DATA-CALMONTH.
WHEN 'SEP'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '09' INTO
TRG_DATA-CALMONTH.
WHEN 'OCT'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '10' INTO
TRG_DATA-CALMONTH.
WHEN 'NOV'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '11' INTO
TRG_DATA-CALMONTH.
WHEN 'DEC'.
CONCATENATE '20' FIELD_LIST-NAME+3(2) '12' INTO
TRG_DATA-CALMONTH.
WHEN OTHERS.
EXIT.
ENDCASE.
FIELD_NAME = FIELD_LIST-NAME.
ASSIGN COMPONENT FIELD_NAME OF STRUCTURE <SOURCE_FIELDS> TO
<FS>.
IF SY-SUBRC = 0.
TRG_DATA-VALUE = <FS>.
APPEND TRG_DATA TO RESULT_PACKAGE.
ELSE.
EXIT.
ENDIF.
ENDIF.
ENDLOOP.
ENDLOOP.
***Routine Code - Custom Code***
With few modifications the code can be used to transpose CALWEEK and CALQUARTER data.