lookups in Bex????

[Former Member]

Hi Experts,

We have a scenario, where in i need to populate results of a query using lookups.

There are some BPC cubes on which I need to build queries using BEx Designer.

Fields in BPC Cube - ENTITY, COMPANY, COUNTRY ... etc

Data in cube:

ENTTIY COMPANY COUNTRY KEYFIG

BU_ABC 1234 CON_IN 75.52

BU_XYZ 3456 CON_US 75.36

BU_ABC 23456 CON_US 78.52

BU_ABC 23456 CON_IN 78.52

The fields Entity , Company , Country are dimensions and have data stored in it.

Query Output.

BU PU SU COUNTRY KEYFIG

ABC ABCD AB IN 75.52

XYZ XZ X US 75.36

Query Output should be based on look ups from Dimensions like ENTITY, COUNTRY.

Below is a sample data available in ENTITY Dimension.

ENTITY dimension:

ID EC_ENTITY PU SU

BU_ABC ABC PU_ABCD SU_AB

PU_ABCD ABCD 0 0

SU_AB AB 0 0

BU_XYZ XYZ PU_XYZ2 SU_X

SU_X X 0 0

SU_XYZ2 XZ 0 0

For eg: in Cube we have the first record entiy as BU_ABC.

For this it has to lookup in Entity dimention. In ID Column it finds BU_ABC , corresponding to this the value from EC_ENTITY should be populated for BU field in Query output.

For PU: - Check the PU value in ENTITY DIMENSION for BU_ABC. (it is PU_ABCD).

Do a search in the ID Column of ENTITY Dimension and enter the corresponding EC_ENTITY value in Query output. (here value is ABCD.)

For SU:- Check the SU Value in ENTITY DIMENSION for BU_ABC. (it is SU_AB).

Do a search in the ID Column of ENTITY Dimension and enter the corresponding EC_ENTITY value in Query output. (Here value is AB).

Similarly this procedure needs to be followed for all the records and all dimensions in the cube.

How can we achieve this in BEx or WAD?

is there any other way to achieve this.?

Thanks in advance.

[Former Member]

you have two options.

1) in BW system you can create new fields on your trnafer rules..

use routines to populate in the format you want and then use that format for your variable in query designer.

2nd option is you create customer variable exit in bex query designer and use that as input, when customer enter a field, the variables go to bw, goes to exit, run the abap and chnage the ouput for you.

i wil go with option 1 as you dont want abap running at run time , but before in the back ground when data is getting loaded.

hope this helps.