Filtering Web Intelligence Queries Using External Lists

Sometimes a data source, such as a universe does not contain all of the data we wish to use in a report. For example, you may wish to leverage a simple external list of values to cross-reference against data from other data sources, such as a universe in your Web Intelligence report. Such cross-referencing allows for the external list to filter what is displayed in the overall report. Here, explained in detail are two options for bringing in an external list into a Web Intelligence query.

OPTION 1: Copy and paste ID numbers to a Web Intelligence query filter

This is the simplest option and it’s best for cases when your list of ID numbers is smaller, i.e. fewer than 1,000.

Start by pasting your ID list to a blank Microsoft Word document.

Press Ctrl-H on your keyboard to open the Find and Replace menu. Type ^p in the Find what box and ; (semicolon) in the Replace with box.

Click Replace All.

Close the Find and Replace menu. Your list will now appear as one, long string of text where each ID number is separated by a semicolon. This is the required format for Web Intelligence filters.

Highlight and copy the ID list.

Close the Word document. Open your Web Intelligence report and navigate to the Query window. Drag the desired ID number field into the Query Filters panel.

Press your cursor in the values box of the new query filter. Hit Ctrl-V on your keyboard to paste the list of ID numbers.

Click the Run Query button and save the report.

OPTION 2: Filter based on Excel spreadsheet

This is the more formal of the 2 options, but does require more maintenance and usually takes longer to set up.

Start by pasting your ID list to a blank Microsoft Excel document. Be sure to give your ID list a proper column header and make sure that the list begins in Cell A1.

Save the Excel document and close.

Open BI Launch Pad and log in. Navigate to the folder in which your Web Intelligence report is or will be stored. On the top toolbar, click New > Local Document.

Click Browse and select your Excel file. Click the Add button in the lower-right corner.

Your Excel file is now uploaded to BusinessObjects.

Open your Web Intelligence report and navigate to the Query window.

Click Add Query > From Excel.

Select the Excel document you had uploaded earlier.

Confirm the worksheet you want to pull from, the fields/range you want to look at, and whether your file contains column headers. Assuming your Excel document is well-formatted, you can accept all of the defaults and click OK.

This will create a new query tab.

Before you can add your filter, you must first click the Run Query button and then save the report. Now, return to the Query Panel. On the query tab containing your Universe query, add a new filter for ID number and select the option for Result from another query.

Select the ID number field from your Excel spreadsheet and click OK.

Your query is now filtered using the external list.

Business IntelligenceWeb IntelligenceExternal Lists