Blog

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.

el1

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.

el2

Click Replace All.

el3

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.

el4

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.

el5

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.

el6

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.

el7

Save the Excel document and close.

el8

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.

el9

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

el10

Your Excel file is now uploaded to BusinessObjects.

el11

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

el12

Click Add Query > From Excel.

el13

Select the Excel document you had uploaded earlier.

el14

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.

el15

This will create a new query tab.

el16

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.

el17

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

1 Comment

  1. Christina Dalton

    Thanks for the tutorial. It was easily to follow and really helped me increase my understanding and efficiency in Webi.

Leave a Comment

Your email address will not be published. Required fields are marked *