ABAP Connectivity to Integrate Any Bex Query to SAP Dashboards using Web Service on top of Function Module Concept

[Former Member]

Follow the below steps to expose any Bex Query to Xcelsius which will enable to create Dashboard for your SAP Bex Query. The Connectivity used here is Web Services on top of ABAP Function Module.

For example consider a End to End Scenario, where i need to create a dashboard to display the status of SAP BW Process chains.

Above scenario can be achieved by Creating a data source on top of Standard tables namely,

1. RSREQDONE
2. RSICCONT

which provide data about the Process chains.

Create View “ZCUBE_DETAILS” on top of table above mentioned tables,

Create data source in Tcode RSO2 on top of the View.

Check whether data is pulled in RSA3

Replicate the Data source in BW,

Create cube ZCB_LOAD which will contain the standard Info objects which corresponds to the fields in the created Data source

Create Transformation between Data source and Cube and map them.

Create Query on Top of Cube which will display the necessary monitoring parameters like Start time, End time, Records Fetched, Status and Data of run of a process chain etc.

Create a “RFC Enabled” Function module in SE37 which will output data of the above created query as shown below,

 

FM Contains the attached code which will convert a Bex query as a FM output

Input parameter: Query name

Output Parameter: Query output captured in a table.

FUNCTION ZPRGM_QRY_XML .
TYPE-POOLS: rrx1 .
DATA: r_dataset TYPE REF TO cl_rsr_data_set.
DATA: lcount TYPE i .
DATA: xcount TYPE i .

DATA: i_var TYPE rrx1_t_var.
DATA: i_var_final TYPE rrx1_t_var.
DATA: wf_variant TYPE variant .
DATA: wa_axis LIKE LINE OF r_dataset->n_sx_version_20a_1-axis_data .
DATA: wa_axis_info LIKE LINE OF r_dataset->n_sx_version_20a_1-axis_info .
DATA: wa_chars LIKE LINE OF wa_axis_info-chars .
DATA: tmp_char TYPE rrws_thx_axis_chars.
DATA: wa_tmp_char TYPE rrws_sx_axis_chars .
DATA: wa_attrinm TYPE rrws_s_attrinm.

DATA: wa_cell LIKE LINE OF r_dataset->n_sx_version_20a_1-cell_data .
DATA: wa_textsymbols LIKE LINE OF r_dataset->n_sx_version_20a_1-txt_symbols .
DATA: wa_textsymbols1 LIKE LINE OF r_dataset->n_sx_version_20a_1-txt_symbols .
DATA: wa_set LIKE LINE OF wa_axis-set.
DATA: tmp_set TYPE rrws_tx_set.
DATA: wa_tmp_set TYPE rrws_sx_tuple .
DATA: wa_dattrinm TYPE rrws_s_attributes .
DATA: i_iset_iobjnm TYPE  rsd_iobjnm ,
e_iobjnm TYPE  rsd_iobjnm .
DATA: error_message TYPE string ,
xml_out TYPE string .
DATA: q_variables TYPE rrxw3tquery .

DATA: error_string TYPE string.
DATA: no_of_chars TYPE  i ,
no_of_keyf TYPE i .
DATA: var_nam(10) .
DATA: iobj_detail TYPE bapi6108 .
DATA: iobj_details TYPE bapi6108_t .
DATA: it_fieldcat TYPE lvc_t_fcat,
is_fieldcat LIKE LINE OF it_fieldcat.

DATA: return TYPE bapiret2_tab .
FIELD-SYMBOLS: <ltable> TYPE ANY TABLE,
<l_line>  TYPE ANY,
<l_field> TYPE ANY,
<field>   TYPE ANY.
TYPES: BEGIN OF metatype ,
fieldname(30),
outputlen(6) TYPE n,
datatype(4) ,
scrtext_l(40),
END OF metatype .
DATA: meta_data TYPE STANDARD TABLE OF metatype ,
wa_meta_data TYPE metatype .

DATA: off  TYPE i,
moff TYPE i,
mlen TYPE i.
DATA: iobj_return TYPE bapiret2_tab .,
DATA:      wf_view TYPE rszviewid .

DATA: i_axis_info TYPE  rrws_thx_axis_info ,
i_cell_data TYPE  rrws_t_cell ,
i_axis_data TYPE  rrws_thx_axis_data ,
i_txt_symbols TYPE  rrws_t_text_symbols .

DATA: n_counter(3) TYPE n .
DATA: char_count TYPE i .
DATA: wf_fldnm(40) .

DATA: struct_type TYPE REF TO cl_abap_structdescr,
tab_type TYPE REF TO cl_abap_tabledescr ,
comp_tab TYPE cl_abap_structdescr=>component_table,
comp LIKE LINE OF comp_tab,
dref TYPE REF TO data ,
dref1 TYPE REF TO data ,
op_len TYPE i .
DATA: fields TYPE tihttpnvp .
DATA: wa_fields TYPE ihttpnvp ,
wa_var TYPE w3query ,
wa_out TYPE zxml_out ,
w_col TYPE i.

CLEAR :fields, q_variables ,i_axis_info,i_cell_data,i_axis_data,i_txt_symbols.
REFRESH: fields, q_variables,i_axis_info,i_cell_data,i_axis_data,i_txt_symbols .

IF wf_query IS NOT INITIAL.
CLEAR : xml_out , return .
REFRESH return .

CALL FUNCTION 'RRW3_GET_QUERY_VIEW_DATA'
EXPORTING
i_infoprovider          = wf_ip
i_query                 = wf_query
i_view_id               = wf_view
i_t_parameter           = q_variables
IMPORTING
e_axis_info             = i_axis_info
e_cell_data             = i_cell_data
e_axis_data             = i_axis_data
e_txt_symbols           = i_txt_symbols
EXCEPTIONS
no_applicable_data      = 1
invalid_variable_values = 2
no_authority            = 3
abort                   = 4
invalid_input           = 5
invalid_view            = 6
OTHERS                  = 7.

CASE sy-subrc .

WHEN 0 .
** find no. of key figures
CLEAR: lcount ,wa_axis , wa_axis_info .
READ TABLE i_axis_data  INTO wa_axis WITH KEY axis = '000'  .
IF sy-subrc EQ 0 .
CLEAR no_of_keyf .
LOOP AT wa_axis-set INTO wa_set .
AT NEW tuple_ordinal .

no_of_keyf = no_of_keyf + 1 .
ENDAT .
ENDLOOP .
CLEAR wa_set .
ENDIF .

** find number of characteristics
READ TABLE i_axis_info  INTO wa_axis_info WITH KEY axis = '001'  .
IF sy-subrc EQ 0 .
CLEAR no_of_chars .
DESCRIBE TABLE wa_axis_info-chars LINES no_of_chars .
ENDIF .

CLEAR : iobj_detail , iobj_details .
REFRESH iobj_details .
CLEAR wa_axis_info .
* get chars. details
READ TABLE i_axis_info  INTO wa_axis_info WITH KEY axis = '001'  .
IF sy-subrc EQ 0 .
CLEAR wa_chars .
REFRESH tmp_char .

LOOP AT wa_axis_info-chars INTO wa_chars .
CLEAR wa_tmp_char .
MOVE-CORRESPONDING wa_chars TO wa_tmp_char .
INSERT wa_tmp_char INTO TABLE tmp_char.
IF NOT wa_chars-attrinm[] IS INITIAL .
LOOP AT wa_chars-attrinm INTO wa_attrinm .
CLEAR :wa_tmp_char-chanm , wa_tmp_char-caption .
MOVE: wa_attrinm-attrinm TO wa_tmp_char-chanm ,
wa_attrinm-caption TO wa_tmp_char-caption .
INSERT wa_tmp_char INTO TABLE tmp_char.
ENDLOOP .
ENDIF .
ENDLOOP .


LOOP AT tmp_char INTO wa_chars .
CLEAR off .
CLEAR :off, moff, mlen .
FIND '___' IN SECTION OFFSET off OF
wa_chars-chanm .
IF sy-subrc EQ 0 .
CLEAR : i_iset_iobjnm , e_iobjnm .
MOVE: wa_chars-chanm TO i_iset_iobjnm .
CALL FUNCTION 'RSD_IOBJNM_GET_FROM_INFOSET'
EXPORTING
i_iset_iobjnm = i_iset_iobjnm
IMPORTING
e_iobjnm      = e_iobjnm
EXCEPTIONS
name_error    = 1
no_field      = 2
OTHERS        = 3.

CLEAR wa_chars-chanm  .
MOVE: e_iobjnm TO wa_chars-chanm .

ELSE.

FIND '__' IN SECTION OFFSET off OF
wa_chars-chanm
MATCH OFFSET moff
MATCH LENGTH mlen.
IF sy-subrc EQ 0 .
off = moff + mlen .
SHIFT wa_chars-chanm LEFT BY off PLACES .
ENDIF .
ENDIF . " two __ or three _
CLEAR: iobj_return .
REFRESH : iobj_return .

CALL FUNCTION 'BAPI_IOBJ_GETDETAIL'
EXPORTING
version    = rs_c_objvers-active
infoobject = wa_chars-chanm
IMPORTING
details    = iobj_detail.


IF  NOT iobj_detail IS INITIAL .
APPEND iobj_detail TO iobj_details .
CLEAR iobj_detail .
ELSE .

MOVE: wa_chars-chanm TO iobj_detail-infoobject ,
wa_chars-caption TO iobj_detail-textlong .

APPEND iobj_detail TO iobj_details .
CLEAR iobj_detail .

ENDIF .
CLEAR : iobj_detail .
ENDLOOP .
ENDIF .

* build field cat. for building the itab
CLEAR: is_fieldcat, iobj_detail, it_fieldcat .
REFRESH : it_fieldcat .
LOOP AT iobj_details INTO iobj_detail .
is_fieldcat-fieldname = iobj_detail-infoobject .
IF is_fieldcat-fieldname+0(1) EQ '0' .
SHIFT is_fieldcat-fieldname LEFT BY 1 PLACES .
ENDIF .
*        IF iobj_details-datatp = 'CHAR' .
*          is_fieldcat-datatype = iobj_details-datatp.
is_fieldcat-outputlen = '130' .
*        ELSE .
is_fieldcat-datatype = 'CHAR' . "iobj_details-datatp.
*          is_fieldcat-outputlen = iobj_details-outputlen .
*        ENDIF .

is_fieldcat-scrtext_l = iobj_detail-textlong.
APPEND is_fieldcat TO it_fieldcat.
CLEAR : is_fieldcat , iobj_detail .
ENDLOOP .


CLEAR :n_counter, wa_axis .
READ TABLE i_axis_data  INTO wa_axis WITH KEY axis = '000'  .
IF sy-subrc EQ 0 .
LOOP AT wa_axis-set INTO wa_set .
AT NEW tuple_ordinal .

n_counter = n_counter + 1 .

CONCATENATE 'VALUE' n_counter INTO is_fieldcat-fieldname .
is_fieldcat-outputlen = '30'.
is_fieldcat-datatype = 'CHAR'.
ENDAT .
CONCATENATE is_fieldcat-scrtext_l wa_set-caption INTO is_fieldcat-scrtext_l SEPARATED BY  ` ` .

*        is_fieldcat-scrtext_l = wa_set-caption.
AT END OF tuple_ordinal .
SHIFT is_fieldcat-scrtext_l LEFT DELETING LEADING ' ' .
APPEND is_fieldcat TO it_fieldcat.
CLEAR : is_fieldcat .
ENDAT .

ENDLOOP .
ENDIF .

CLEAR: meta_data, wa_meta_data .
REFRESH meta_data .
LOOP AT it_fieldcat INTO is_fieldcat .

MOVE-CORRESPONDING is_fieldcat TO wa_meta_data .
APPEND wa_meta_data TO meta_data .
CLEAR: wa_meta_data, is_fieldcat .
ENDLOOP .

* create itab
SORT it_fieldcat BY fieldname.
DELETE ADJACENT DUPLICATES FROM it_fieldcat COMPARING fieldname .
CLEAR: comp , comp_tab ,is_fieldcat .
REFRESH comp_tab .

LOOP AT it_fieldcat INTO is_fieldcat .
CLEAR op_len .
op_len = is_fieldcat-outputlen .
comp-name = is_fieldcat-fieldname.
comp-type = cl_abap_elemdescr=>get_c( op_len ).
APPEND comp TO comp_tab.
CLEAR : is_fieldcat , comp .
ENDLOOP .

CLEAR struct_type .

CALL METHOD cl_abap_structdescr=>create
EXPORTING
p_components = comp_tab
p_strict     = cl_abap_structdescr=>false
RECEIVING
p_result     = struct_type.

CLEAR tab_type .
CALL METHOD cl_abap_tabledescr=>create
EXPORTING
p_line_type  = struct_type
p_table_kind = cl_abap_tabledescr=>tablekind_std
RECEIVING
p_result     = tab_type.

CREATE DATA dref1 TYPE HANDLE tab_type.

ASSIGN dref1->* TO <ltable>.

CREATE DATA dref TYPE HANDLE struct_type.

ASSIGN dref->* TO <l_line>.

CLEAR :wa_axis , char_count , lcount, xcount.
REFRESH tmp_set .
LOOP AT i_axis_data INTO wa_axis WHERE axis = '001' .
LOOP AT wa_axis-set INTO wa_set .
CLEAR wa_tmp_set .
MOVE-CORRESPONDING wa_set TO wa_tmp_set .
INSERT wa_tmp_set INTO TABLE tmp_set.
IF NOT wa_set-attributes[] IS INITIAL .
LOOP AT wa_set-attributes INTO wa_dattrinm .
CLEAR: wa_tmp_set-chanm , wa_tmp_set-chavl , wa_tmp_set-chavl_ext , wa_tmp_set-caption .
MOVE: wa_dattrinm-attrinm TO wa_tmp_set-chanm ,
wa_dattrinm-attrivl TO wa_tmp_set-chavl ,
wa_dattrinm-attrivl TO wa_tmp_set-chavl_ext ,
wa_dattrinm-caption TO wa_tmp_set-caption .

INSERT wa_tmp_set INTO TABLE tmp_set.
ENDLOOP .
ENDIF .
ENDLOOP .
ENDLOOP .

LOOP AT tmp_set INTO wa_set .
AT NEW tuple_ordinal .
IF lcount GT 0 .
lcount = lcount - 1 .
ENDIF .
CLEAR <l_line> .
ENDAT .
CLEAR off .

CLEAR :off, moff, mlen .
FIND '___' IN SECTION OFFSET off OF
wa_set-chanm .
IF sy-subrc EQ 0 .
CLEAR : i_iset_iobjnm , e_iobjnm .
MOVE: wa_set-chanm TO i_iset_iobjnm .
CALL FUNCTION 'RSD_IOBJNM_GET_FROM_INFOSET'
EXPORTING
i_iset_iobjnm = i_iset_iobjnm
IMPORTING
e_iobjnm      = e_iobjnm
EXCEPTIONS
name_error    = 1
no_field      = 2
OTHERS        = 3.

CLEAR wa_set-chanm  .
MOVE: e_iobjnm TO wa_set-chanm .
ELSE.

FIND '__' IN SECTION OFFSET off OF
wa_set-chanm
MATCH OFFSET moff
MATCH LENGTH mlen.
IF sy-subrc EQ 0 .
off = moff + mlen .
SHIFT wa_set-chanm LEFT BY off PLACES .
ENDIF .
ENDIF . " check three _ or two _

IF wa_set-chanm+0(1) EQ '0' .
SHIFT wa_set-chanm LEFT BY 1 PLACES .
ENDIF .
ASSIGN COMPONENT wa_set-chanm OF STRUCTURE <l_line> TO <l_field>.
IF wa_set-chavl = '#' .
ELSE .
CONCATENATE wa_set-chavl_ext wa_set-caption INTO <l_field> SEPARATED BY ' ' .
ENDIF .

AT END OF tuple_ordinal .
CLEAR: xcount , char_count , n_counter .

lcount = lcount + 1 .

LOOP AT i_cell_data INTO wa_cell FROM lcount .

n_counter = n_counter + 1 .
IF n_counter GT no_of_keyf .
EXIT .
ENDIF .
CONCATENATE 'VALUE' n_counter INTO wf_fldnm .
ASSIGN COMPONENT wf_fldnm OF STRUCTURE <l_line> TO <l_field>.

<l_field> = wa_cell-value .

ENDLOOP .

lcount = lcount + no_of_keyf .
w_col = 1.
DO 9 TIMES.
ASSIGN COMPONENT w_col OF STRUCTURE <l_line> TO <field>.
IF w_col = 1.
wa_out-date = <field>.
ELSEIF w_col = 2.
wa_out-req = <field>.
ELSEIF W_COL = 3.
wa_out-P_ID = <field>.
ELSEIF W_COL = 4.
wa_out-T_OBJ = <field>.
ELSEIF W_COL = 5.
wa_out-U_NAM = <field>.
ELSEIF W_COL = 6.
wa_out-S_TIME = <field>.
ELSEIF W_COL = 7.
wa_out-E_TIME = <field>.
ELSEIF W_COL = 8.
wa_out-N_REC = <field>.
ELSEIF W_COL = 9.
wa_out-STAT = <field>.
ENDIF.
w_col = w_col + 1.
ENDDO.
APPEND wa_out TO out.
CLEAR wa_out.
ENDAT .
ENDLOOP .
ENDCASE .
ENDIF .
ENDFUNCTION.

Create Web Service on top of the Function Module

Complete the below navigation steps which will create a Web service

Goto Tcode SOAMANAGER to configure the Web Service: give the name of web service created in BW and Click on “Create Service”

Provide the Service name and Binding Name as shown,

Select the appropriate Authentication method as shown below

Click on “Open porttype WSDL document”,

New page will open, copy the link from the Address bar, which is the WSDL link

Open SAP Dashboard tool and create a Blank model. And under “Connection” Create a “Web Service Connection”

Provide the WSDL link in the WSDL URL as shown below and map the source and target fields in the Spread sheet. Hard code the Name of the query in the Xcelsius spread sheet.

Map the objects to a  Drop down box and apply the destination on to a Spread Table component and give a selection

Click on Preview, Process chain status details will be shown as below,

Now we have created a Dashboard which reads the Sap Bex query through a Web service connection which is created upon a ABAP Function Module!