Value based on multiple MERGED DIMENSIONS

Question

The VLOOKUP equivalent within business objects is to use a merged dimension then create a detail object using the merged dimension. Which works fine.

However, what if you want the value to be returned based on multiple merged dimensions? When creating a detail object it only gives you the option to select one.

e.g if I have 2 reports

Report 1

Employee ID

Name

Report 2

Employee ID

Age

I could do a merge on Employee ID, create a detail object using the merged dimension which returns Age.

However if i had 2 reports like this

Report 1

Employee ID

Position ID

Name

Report 2

Employee ID

Position ID

Age

And only want to return Age if the Employee ID and Position ID match. How would i go about this?

Thanks Chris

Answer 1

The ability to merge on variables was introduced in version 4.2 SP3. So if you are on that version or higher you can concatenate your Employee ID and Position ID from each data source like this.

Report1 Employee ID~PositionID=[Report1 Employee ID] + "~" + [Report1 Position ID]
Report2 Employee ID~PositionID=[Report2 Employee ID] + "~" + [Report2 Position ID]

Then merge the resulting Report1 Employee ID~PositionID and Report1 Employee ID~PositionID variables.