Search code examples
business-objectswebi

Check Variable in Universe agains the Excel Data provider value list?

I have two data providers, universe and Excel, and would like to show all records from the universe based on what is provided in Excel.

To illustrate, I have a list of name in Excel, only this one dimension, no identifier or related data attached to these names.

Excel

On the universe side, I only have Firstname and Lastname dimensions but not the complete name.

Universe

So in order to match them, I would create a new variable called Complete Name, which is the concatenation of both Firstname and Lastname.

My question is, how to link the variable I created with the List of Names in Excel provider, so that only the record from the universe side that has matching value with List of Names in Excel will show?

NB: I do not have authority to change the universe as it is controlled by other group. My version of Webi is SAP BI 4.2.

*Also, I notice that I can do the concatenation myself via Free-hand SQL, but then again I have no idea how to filter/match based on the list of names in excel.

Please help! Any clue/advice would be appreciated!

Solution

  • I think need to merge on objects that have values in the same format.

    If your version of BusinessObjects is 4.2 SP02 or earlier you will have to do the name concatenation in free-hand SQL and merge that resulting column with List of Names from your spreadsheet. In BusinessObjects 4.2 SP03 the capability to merge on variables was added. I have not worked with that version yet, but as I understand it you could leave you universe query as is and create a variable to concatenate Firstname and Lastname and then merge that with List of Names from the the spreadsheet.

    If you are getting "This object is incompatible" or something like that when trying to add dimensions see these links on how to create associated detail objects.