Oracle® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide > Filtering Requests in Oracle BI Answers >

Editing the SQL for a Column Filter in an Oracle BI Request

You can edit the logical SQL WHERE clause to be used as a filter. While generally not necessary, this feature is available for users who need advanced filtering capability. For a comprehensive description of SQL syntax, refer to a third-party reference book on SQL, to a reference manual on SQL from one of the database vendors, or to an online reference site.

To edit the SQL generated for a column filter

  1. While in the column filter dialog, click the Advanced button, and then select the following menu option:

    Convert this filter to SQL

  2. Type your modifications into the text box, and then click OK.

    The filter appears in the Filters area on the Criteria tab.

Example Oracle BI Answers Filters

This section contains example SQL for example requests against hypothetical data sources. The WHERE clause contains the filters. You can review these examples to see some of the kinds of information you can obtain using filters.

If you want to use an example in an actual request, you will need to modify it to reflect the table and column names used in your organization's data sources.

Example 1: Identifying Customers with the Most Sales Volume

The following example request reports data on the ten customers with the most sales volume in 2003.

SELECT Customers.Customer, Periods.Year,

SalesFacts.Dollars, RANK(SalesFacts.Dollars),



FROM "SupplierSales"

WHERE Periods.Year = 2003

AND RANK(SalesFacts.Dollars) <= 10 ORDER BY 4

Example 2: Reporting Sales for a Particular Brand

The following example request reports data on the Fizzy Brands company.

SELECT * from "SodaTable"

WHERE Product.Brand = 'Fizzy Brands'

Example 3: Reporting Salaries Above a Certain Amount

In the following example request, the filter in the WHERE clause limits the output to individuals whose yearly salaries are greater than 75000, for example, $75,000 in US dollars.

SELECT "Name.Lastname", "Name.Firstname",

"Salary.YearlySalary" FROM "SalaryTable"

WHERE "Salary.YearlySalary" > 75000


Example 4: Sales Representative's Share of East Region

The following example request limits data to customers in the East region in the year 2003.

SELECT Periods.Year, Customers.Region,

Customers.SalesRep, SalesFacts.Dollars,



FROM "SupplierSales"

WHERE Periods.Year = 2003

AND Customers.Region = 'East'

Example 5: Reporting Sales Amount by Product Category

The following example request reports the sales amount by product category in the local currency for the latest date in the fact table. LatestSalesDate is a Dynamic Repository variable.

SELECT Product."Product Category Name", "Facts -- Non Aggregatable".Currency, "Facts Local Currency".SalesAmount FROM AdventureWorks1 WHERE Time.Date = VALUEOF(LatestSalesDate)

Related Topic

Using Column Filters in an Oracle BI Request

Oracle® Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide Copyright © 2007, Oracle. All rights reserved.