Passing Selection Values from BEx Query to Transformation of the Virtual Provider based on DTP for Direct Access.

[Former Member]

1.  Business Scenario:

Report based on G/L account data, uses the mapping values that are stored in a DSO. Storing the data by applying the mappings in the Standard Cube load is not feasible, as the user requirement is to see the current version mappings applied to  historical transaction data  or  historical version mappings applied to current transactional data.

Data dependency:

The mappings in the DSO can change frequently based on the business need. Huge volume of G/L Data

In order to meet the business requirement, considering the data dependency, the report is built on a

Virtual Cube.

Hence, the mapping from the ‘mapping DSO’ needs to be applied to the transaction data before the report is displayed using a virtual Cube. The Virtual Cube uses the Base Cube as a source and a transformation created between Base Cube and Virtual Cube. When the user executes the BEx report, the business logic implemented in the Transformations is applied to the resultant dataset before it is displayed in the report to the end user.

The below picture gives the Scenario overview:

2. Sample Report Output

In order to understand the business scenario better, the below example is discussed with some sample data and expected report output.

Note: Sample data provided is only for requirement understanding; it does not contain any data from the client (Actual Data).

Sample Transaction Data in Base Cube:

Company Code	Profit Center	G/L Account	Debit/Credit Amount
C100	P100	100000100	100
C100	P100	100000200	150
C100	P101	100000100	300
C100	P101	100000200	200

Sample Mappings Data in Mappings DSO:

G/L Account	Version	Relationship Type	Base Account
100000100	10/01/2012	APS	GL1
100000200	10/01/2012	KKR	GL2
100000100	11/01/2012	ABC	GL3
100000200	11/01/2012	XYZ	GL4

Expected Report Output

Case1: User enters Verison = 10/01/2012

Company Code	Profit Center	G/L Account	Relationship Type	Base Account	Debit/Credit Amount
C100	P100	100000100	APS	GL1	100
C100	P100	100000200	KKR	GL2	150
C100	P101	100000100	APS	GL1	300
C100	P101	100000200	KKR	GL2	200

Case 2: User enters Version = 11/01/2012

Company Code	Profit Center	G/L Account	Relationship Type	Base Account	Debit/Credit Amount
C100	P100	100000100	ABC	GL3	100
C100	P100	100000200	XYZ	GL4	150
C100	P101	100000100	ABC	GL3	300
C100	P101	100000200	XYZ	GL4	200

3. Technical Challenge

The user requires ability to select the Version of the mappings which has to be applied to the transaction data before it is displayed in the BEx report

• User selection of version to be executed during query run to the transaction data
• Version data is not a part of transaction data & not a part of Base cube

To address this, a Manual Input variable is created on the Version info object which is ready for input. Having a ready for input manual input variable in the BEx report leads to below two issues,

   1.     Passing the Version value entered in the selection prompt of the BEx Query to backend for using in the transformation, between Base Cube and Virtual Cube, for doing a lookup from mapping DSO is not straight forward.

   2.     As the value entered at the variable selection prompt of the BEx is used as filter for fetching the records from the Base Cubes, the report output does not display any data because Version is not available in the Base Cube.

In order to address the above technical challenges, below step by step approach is followed.

4.  Approach to overcome the Challenge

In order to overcome the above challenge following steps have been implemented,

Note: For the approach, we have used a multi cube instead of a base cube. But the approach will still be same when only a base cube is used.

Step 1: A DSO is created as shown below. This DSO contains the G/L Account to Base Account & Relationship Type mappings. YYVERSION, which is a date in our case, is used to determine the mappings that have to be applied to the report output.

Step 2: A Virtual Cube is created that has the Multi Cube as its source. A transformation is created between the Multi Cube and the Virtual Cube as shown below.

The structure of the Virtual Cube is as shown below. Highlighted in RED are the characteristics for

Version, Relationship Type and Base Account.

Version is added to the Virtual Cube, so that a User Entry Variable can be created in the BEx Query, enabling user for entering the Value for Version of the mappings which have to be applied to the report output.

Base Account and Relationship Type are added to the Virtual Cubes, so that they can be derived from the Mapping DSO, based on the Version entered by the user.

Step 3: Created a BEX Query as shown below, on Virtual Cube from Step 2. The Query contains a Filter for Version (YYVERSION).

Step 4: The ABAP code mentioned in the Appendix A and Appendix B has been written in the Inverse

End Routine and End Routine respectively, so that the value entered by user for Version is stored in to a

Global Variable. The value thus stored into the Global Variable is used in the End Routine for doing the look up from the Mapping DSO.

Step 5: In the transformation from Multi Cube to the Virtual Cube, the Field Level routine for YYVERSION

is enabled and in the “METHOD invert_YYVERSION.”, a line of code as below has been added.

This step is very important for our scenario as we do not have YYVERSION in the Multi Cube, which is source for the Virtual Cube. Hence the value entered by user for YYVERSION is passed as a selection to the Multi Cube, and since there is no YYVERSION available in the Multi Cube, the report would always display “No Applicable Data Found”.

By enabling the Inverse Routine, by adding the dummy line of code, for the YYVERSION, we were able to influence the transfer of   selection conditions, in which the system does not transfer any selection conditions to the source and therefore we were able to retrieve the dataset from the source.

5. Mapping Data used for Testing

Below test mapping data is used.

The mapping data contains 2 records with different Version, one for 01/01/2001 and  other for

01/01/2010, for the transaction level G/L account 1000100262. Both the records have the Base

Account as 1123001038. The Relationship type for Version 01/01/2001 is FTP and the relationship type for Version 01/01/2010 is FLT.

6. Test Results

     Test 1

       Input: Version = 01/01/2001

Report Output: The Value for Version is 01/01/2001 and the Value for Relationship Type = FTP

    Test 2

Input: Version = 01/01/2010

Report Output: The Value for Version is 01/01/2010 and the Value for Relationship Type = FLT

7.  Appendix A

Global Declaration.

*$*$ begin of global - insert your declaration only below this line *-*

DATA: GI_THX_SELECTION_CS TYPE RSARC_THX_SELCS, GL_SX_SELECTION_CS LIKE LINE OF GI_THX_SELECTION_CS, GL_SX_SELECTION_CS2 LIKE LINE OF GI_THX_SELECTION_CS.

... "insert your code here

*$*$ end of global - insert your declaration only before this line  *-*

Code in the Inverse End Routine

METHOD inverse_end_routine.

TYPE-POOLS : SBIWA.

DATA:

I_RT_CHAVL_CS TYPE RSARC_RT_CHAVL,

I_THX_SELECTION_CS TYPE RSARC_THX_SELCS,

C_T_SELECTION TYPE SBIWA_T_SELECT, E_EXACT TYPE RS_BOOL.

DATA:

L_TR_DIMENSIONS TYPE RSMDS_TR_DIMENSIONS,

"table of dimension references

L_R_DIMENSION LIKE LINE OF L_TR_DIMENSIONS,

"dimension reference

L_DIMNAME TYPE RSMDS_DIMNAME,

"dimension name

L_SX_SELECTION_CS LIKE LINE OF I_THX_SELECTION_CS,

"work area for single characteristc RANGE table L_R_UNIVERSE TYPE REF TO CL_RS_INFOOBJECT_UNIVERSE. "reference for InfoObject universe

DATA:

L_S_SELECTION LIKE LINE OF C_T_SELECTION,

L_S_CHAVL_CS LIKE LINE OF I_RT_CHAVL_CS, DATE TYPE SY-DATUM.

*Start

1. TRY.

* Transform selection set for outbound

CALL METHOD I_R_SELSET_OUTBOUND->TO_RANGES CHANGING

C_T_RANGES = I_RT_CHAVL_CS.

* Get reference to InfoObject universe

L_R_UNIVERSE = CL_RS_INFOOBJECT_UNIVERSE=>GET_INSTANCE( ).

*Get all dimensions (i.e. fields) from outbound selection which are

*restricted

L_TR_DIMENSIONS = I_R_SELSET_OUTBOUND->GET_DIMENSIONS( ).

LOOP AT L_TR_DIMENSIONS INTO L_R_DIMENSION.

CLEAR L_SX_SELECTION_CS.

* Get dimension name (= field name)

L_DIMNAME = L_R_DIMENSION->GET_NAME( ).

* Transform dimension name to InfoObject name

L_SX_SELECTION_CS-CHANM = L_R_UNIVERSE->DIMNAME_TO_IOBJNM(

L_DIMNAME ).

*Project complete outbound selection set to current dimension and

*and convert to RANGE table representation

CALL METHOD I_R_SELSET_OUTBOUND->TO_RANGES

EXPORTING

I_R_DIMENSION = L_R_DIMENSION

CHANGING

C_T_RANGES   = L_SX_SELECTION_CS-RT_CHAVL.

APPEND L_SX_SELECTION_CS TO I_THX_SELECTION_CS. ENDLOOP.

READ TABLE I_THX_SELECTION_CS INTO GL_SX_SELECTION_CS

WITH KEY CHANM = 'YYVERSION'. CATCH CX_RSMDS_INPUT_INVALID CX_RSMDS_INPUT_INVALID_TYPE.

* Should not occur

* If the exception occurs request all values from source

* for this routine to be on the save side

*c_r_selset_inbound = cl_rsmds_set=>get_universal_set( ).

*c_exact = rs_c_false. "Inversion is no longer exact

1. ENDTRY.

C_TH_FIELDS_INBOUND = I_TH_FIELDS_OUTBOUND. C_R_SELSET_INBOUND = I_R_SELSET_OUTBOUND. C_EXACT = RS_C_TRUE.

*$*$ end of inverse routine - insert your code only before this line *-*

1. ENDMETHOD.

1. 8.  Appendix B

METHOD end_routine.

*=== Segments ===

FIELD-SYMBOLS:

<RESULT_FIELDS>   TYPE _ty_s_TG_1.

DATA:

MONITOR_REC    TYPE rstmonitor.

TYPES: TY_D_MAP TYPE /BIC/AYGL_DSO00.

DATA: IT_D_MAP TYPE STANDARD TABLE OF TY_D_MAP , ST_D_MAP TYPE TY_D_MAP.

* Get base account and Rel.Type

SELECT * INTO TABLE IT_D_MAP FROM /BIC/AYGL_DSO00

FOR ALL ENTRIES IN RESULT_PACKAGE

WHERE CHRT_ACCTS = RESULT_PACKAGE-CHRT_ACCTS

AND GL_ACCOUNT = RESULT_PACKAGE-GL_ACCOUNT

AND /BIC/YYVERSION IN GL_SX_SELECTION_CS-RT_CHAVL.

LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>.

* Get base account and Rel.Type

CLEAR ST_D_MAP.

READ TABLE IT_D_MAP INTO ST_D_MAP

WITH KEY CHRT_ACCTS = <RESULT_FIELDS>-CHRT_ACCTS

GL_ACCOUNT = <RESULT_FIELDS>-GL_ACCOUNT.

*

<RESULT_FIELDS>-/BIC/ZACC_BASE = ST_D_MAP-/BIC/ZACC_BASE.

<RESULT_FIELDS>-/BIC/ZRELTYP  = ST_D_MAP-/BIC/ZRELTYP.

<RESULT_FIELDS>-/BIC/YYVERSION  = ST_D_MAP-/BIC/YYVERSION.

1. ENDLOOP. "<RESULT_FIELDS>

ENDMETHOD