Web Intelligence Quick Tip
1 | Page
Filter Based on a List of Values from Excel
Follow these steps if you have a list of values (for example, UINs) in an Excel spreadsheet, and you would
like to use this list as a query filter.
Prepare the Excel Spreadsheet
• If your Excel list contains more than 1000 rows, you will need to break this list up into individual lists
of 1000 rows or less. See If you have more than 1000 rows in the excel spreadsheet at the end of
this document.
• If your Excel spreadsheet contains UINs, format the cells as text in Excel prior to creating your query.
Create first query using the Excel Document as the data source.
1. In Web Intelligence Rich Client, select Excel from the New Document Data Source list.
2. Browse your computer to locate the desired Excel Spreadsheet.
Web Intelligence Quick Tip
2 | Page
3. Select the document and click Open.
4. If necessary, select the desired worksheet from the Sheet Name drop-down list. The default will be
the first sheet in the Excel Workbook.
5. Click OK.
6. The Query Window is displayed showing the objects created from your Excel list.
Web Intelligence Quick Tip
3 | Page
7. Click Run Query to run your first query.
Create Second Query
1. From the report manager window, click the Edit Data Provider button.
2. From the Edit Query window, select the Add Query drop-down menu, then select From Universe.
Web Intelligence Quick Tip
4 | Page
3. Select the Universe from the Universe window.
4. Add Results Objects and any Query Filters needed for this query.
5. Drag the UIN object into the Query Filters panel.
Web Intelligence Quick Tip
5 | Page
6. Leave the operator as the default In List.
7. Click the Operand Menu Button.
8. Select Results from Another Query.
9. Select the UIN object from Query 1 (from STEP 1 above) and click OK.
Web Intelligence Quick Tip
6 | Page
10. Click Run Queries button.
The results of the second query will be limited to the rows that contain a UIN that matches a UIN
from the spreadsheet list.
Web Intelligence Quick Tip
7 | Page
If you have more than 1000 rows in the excel spreadsheet:
1. Divide the excel list into individual worksheets with no more than 1000 rows in each sheet.
2. Add a query for each spreadsheet (following the above steps).
3. Repeat the steps in Step 2 to create a filter on the UIN from each Excel query.
4. Nest the filters together (drag and drop one on top of the other).
5. Click the And operator to change to an Or operator.
