Query Options

---

To access Query Options (shown in Figure 21-6), select Options from the Query Panel. These options relate to the individual query, and unlike New Document Options, they are not global for all subsequent documents.

Figure 21-6: Query Options

If you are accessing dimensional data in which there are multiple rows for the exact same value, select No Duplicate Rows to add a DISTINCT to the SQL Select statement. For example, if you have a CUSTOMER or PRODUCT table in which there are multiple records with different valid to/from dates for the same customers or products, by default, you will receive multiple rows of information in a list report. BusinessObjects generates the following SQL:

SELECT CUSTOMER.CUSTOMER_ID, CUSTOMER.CUSTOMER_NAME FROM CUSTOMER

When you select the option No Duplicate Rows, BusinessObjects modifies the SQL statement:

SELECT DISTINCT CUSTOMER.CUSTOMER_ID, CUSTOMER.CUSTOMER_NAME FROM CUSTOMER

By default, all list of values queries contain the DISTINCT qualifier. This option is not important for queries that contain a measure that uses a GROUP BY function, as all rows are aggregated and grouped by each unique row identifier.

In earlier versions of BusinessObjects, when you modified a query, you could only Cancel or Run it. If you had a particularly complex query with nested conditions that you either wanted to schedule via BCA or to continue modifying at a later point in time, you could select the option Do Not Retrieve Data, click OK, then Run, and save a blank document. With the addition of the Query Panel’s Save and Close button, the Do Not Retrieve Data option is no longer necessary.

Delete Trailing Blanks trims blank values from individual rows of data. The values are not trimmed for each column, but just for an individual row.

Partial Results allows you to retrieve a limited number of rows from the data source, ideally for testing complex queries or selecting top ten values, as described earlier. The Default Value is the maximum number of rows you can retrieve as specified by the universe designer. To enter a different value, select Other.

---