return table in update rules

[Former Member]

hi,

in update rules there is option of return table

i know what is return table but i dont know

where and how to write a code for that

please suggest me in which scenario it is generally used

i will assign points

[Former Member]

thanks for answering

but its not clear

like where to write code in update rules

[Former Member]

Hi Ram,

Remember that during the update rules (without return table), one record is created for each key figures.

If the update of all characteristics are the same for all key figures, one single record will be posted in the cube.

If you have different characteristics values for each key figure, you could post as many records as the number of key figures in that cube.

The return table functionality is coded in the key figures and is valid only for this particular key figure. It enables to post several records for one single key figure and one single charateristic.

a typical application is to transform Key Figures in Characteristic values, e.g. COPA value fields into Accounts.

A record like

CALMONTH = '200701'

COST = 200

SALES = 300

is passed to the update rules of an infocube having CALMONTH and ACCOUNT as characteristics and AMOUNT as Key figure.

In order to transform your value fields into accounts, let's assume that you have somewhere stored the relation:

VALUE_FIELD ACCOUNT

SALES 100000

COST 200000

and you have filled t_mapping in your start routine

In the AMOUNT update rule, you create a reurn table routine:

RESULT_TABLE = ICUBE_VALUES.

LOOP at t_mapping INTO s_mapping.

CONCATENATE

'COMM_STRUCTURE-'

s_mapping-VALUE_FIELD

INTO lv_vfieldnm.

ASSIGN (lv_vfieldnm) TO <lf_vfield_value>.

IF <lf_vfield_value> IS NOT INITIAL.

MOVE <lf_vfield_value> TO RESULT_TABLE-AMOUNT.

RESULT_TABLE-ACCOUNT = ls_mapping-ACCOUNT.

ENDIF.

APPEND RESULT_TABLE.

ENDLOOP.

For each record of your mapping table, the routine will post the corresponding key figure value (from the communcation structure) into its corresponding account.

For the account characteristic can therefore remain "blank" in the characterisc screen of your update rules.

The original record is now splitted in two

CALMONTH ACCOUNT AMOUNT

200701 100000 300

200701 200000 200

Another example, perhaps a bit more simple: you have your SALES posted by CALMONTH from a datasource and you need to have this amount posted in your cube by CALMNONTH but also the year to date SALES_YTD; e.g. when select March in a report, SALES = sales of March and SALES_YTD = JanFebMar.

In the SALES_YTD you create a return table routine (simplified)

RESULT_TABLE = ICUBE_VALUES.

lv_month2 = COMM_STRUCTURE-CALMONTH+4(2).

i = 1.

WHILE i LE lv_month2.

SALES_YTD = SALES.

CONCATENATE COMM_STRUCTURE-CALMONTH(4) i

INTO RESULT_TABLE_CALMONTH

i = i+1

APPEND RESULT_TABLE.

ENDWHILE.

This will post any monthly amount to all previous months. Here you CALMONTH shall be "blank" in the time period screen of your update rules. Even if you assign the communication structure value for the calmonth, it would be overriden by the return routine.

Finally the records are aggregated just prior the insertion in the fact table.

hope this will help you to understand the return table functionality

Olivier.

[Former Member]

I have the same issue, and Im trying to make to two registers in destination for each one on the source.

Example:

Source ODS

CREDIT SECECO KF1 KF2

000001 ABC 5.5 10.1

000002 EFG 6.3 11.5

Expected Result:

Destination ODS (Key Values = CREDIT, TYPE)

CREDIT SECECO TYPE KF3

000001 ABC 1 5.5

000001 ABC 2 10.1

000002 EFG 1 6.3

000002 EFG 2 11.5

Real Result 1:

CREDIT SECECO TYPE KF3

000001 00 1 5.5

000001 00 2 10.1

000002 00 1 6.3

000002 00 2 11.5

Im using a return table for Update routine KF3 and it is working fine. However SECECO (a characteristic) is getting blank values always.

Here my code:

FORM compute_data_field

TABLES MONITOR STRUCTURE RSMONITOR "user defined monitoring

RESULT_TABLE STRUCTURE /BIC/AZBI990400

USING COMM_STRUCTURE LIKE /BIC/CS8ZBI9905

RECORD_NO LIKE SY-TABIX

RECORD_ALL LIKE SY-TABIX

SOURCE_SYSTEM LIKE RSUPDSIMULH-LOGSYS

ICUBE_VALUES LIKE /BIC/AZBI990400

CHANGING RETURNCODE LIKE SY-SUBRC "Do not use!

ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel update

*

$$ begin of routine - insert your code only below this line -

• fill the internal table "MONITOR", to make monitor entries

• DATA:

RESULT_TABLE = ICUBE_VALUES.

RESULT_TABLE-/bic/KF3 = COMM_STRUCTURE-/BIC/KF1.

RESULT_TABLE-/bic/SECECO = COMM_STRUCTURE-/BIC/SECECO.

RESULT_TABLE-/bic/TYPE = 1.

APPEND RESULT_TABLE.

RESULT_TABLE-/bic/KF3 = COMM_STRUCTURE-/BIC/KF2.

RESULT_TABLE-/bic/SECECO = COMM_STRUCTURE-/BIC/SECECO.

RESULT_TABLE-/bic/TYPE = 2.

APPEND RESULT_TABLE.

• if the returncode is not equal zero, the result will not be updated

RETURNCODE = 0.

• if abort is not equal zero, the update process will be canceled

ABORT = 0.

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

ENDFORM.

The Update Rules for SECECO is left as Blank. I tried mapping it to SECECO but the result was this:

Real Result 2:

CREDIT SECECO TYPE KF3

000001 ABC 0.0

000001 00 1 5.5

000001 00 2 10.1

000002 EFG 0.0

000002 00 1 6.3

000002 00 2 11.5

I have already tried with a fixed value like the one for TYPE, still no succees. The only difference between SECECO and TYPE is that TYPE is part of the key of the ODS Destiny. However SECECO must not be part of the key.

I also tried looping the RESULT_TABLE at the end and assigning the value directly to SECECO with the same result.

¿What else should I consider?

I would award points.

DavidG

[Former Member]

Lets say your info source supplies profit center, month,amount to the cube and you wish to add another field or fields such as the cost centers that are assigned to this profit center, then you use return table to do this. In this case, you cant derive the cost center with master data attribute option, as they have 1:M relationships.

Ravi Thothadri