How do I base a BO query on a list of values in an Excel file?

[vmckee]

Hello,

First of all, I appologize if the following question is a stupid one - I checked the FAQs and keyword search and could not find the answer I was looking for.

I am brand new to Business Objects and my first task is to query a list of people from a data warehouse based on person IDs contained in an Excel file. So basically, I am trying to select the person IDs and names from the data warehouse that match the IDs in the Excel spreadsheet.

If anyone knows how to do this, I would greatly appreciate the knowledge. Thanks in advance.

 

[blom0344]

Hello vmckee,

Your question is far from a stupid one.

Be aware that you can only enter a limited amount of values into a condition anyway. If you have hundreds of ID´s to compare with, you will run into the maximum number of entries of 256 anyway. I cannot back it up , but on our ORACLE 7.3.4 warehouse database I run into problems long before I have selected 256 entries.

BO works with list of value files .lov that are created when you create a new object in a universe (see object properties). If you could arrange the data from your Excelfile to be uploaded to a temporary datawarehouse table, you could add this table to your universe as a lookup table for your ID´s and use the list of values that is associated with the object you create on your temporary table-field in a report.

blom0344

 

[vmckee]

Thanks! I think that should help.

vmckee

 

[MAVenugopal]

Run Business query from EXCEL and specify the range of values in the condition.

Hope this helps.

 

[blom0344]

Hello Venugopal,

I think you are mixing things up a bit. Business query is a tool to directly query on a relational database and display data directly in Excel instead of in BO reporter. That's just the opposite way!!!
And by the way , as far as I know standard BO doesn't come with BO query anymore, you really have to buy it as an additional tool (at a hefty price !!) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[MAVenugopal]

Dear Blom,

As stated by you, Business Query is an add on product and we found that it is useful when the users want to query from within EXCEL. Since a large number of users are familiar with EXCEL, they prefer to work within EXCEL using Business Query. When the list of items are available in an EXCEL sheet they could run the query from the same EXCEL sheet and later on import the results to BO if required.

There is another expensive tool from Business Objects called Set Analyzer. The functionality you are looking for is available in Set Analyzer.

I would also be keen to know if there is a way to get query results based on values in an EXCEL sheet with out the above mentioned tools.

Regards

 

[TMHill]

Hi All

I have given myself a function in Excel, which joins a range of cells together seperated by commas. This creates a string which can be pasted into an "In List" criteria in a business objects data provider.

I can paste up to 1000 distinct employee IDs (length of 9 characters) in and it runs quite happily. I can then create another condition and paste the next 1000 IDs in, and make it an "Or" condition if I want.

I know this isn't the perfect solution, but its really really simple, and everyone in my department now relies on it.

Put simply - its a fudge, but it works.

Tom