A group filter determines which records to include in a group. You can use the packaged filters, First and Last, to display the first n or last n records for the group, or you can create your own filters using PL/SQL. You can access group filters from the Object Navigator, the Property Inspector (the PL/SQL Filter property), or the PL/SQL Editor.
The function must return a boolean value (TRUE
or FALSE
).
Depending on whether the function returns TRUE
or FALSE
,
the current record is included or excluded from the report.
Difference between group filters and Maximum Rows to Fetch property
The Maximum Rows to Fetch property restricts the actual number of records fetched by the query. A group filter determines which records to include or exclude, after all the records have been fetched by the query. Since Maximum Rows to Fetch actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you set the Filter Type property to Last or Conditional, Reports Builder must retrieve all of the records in the group before applying the filter. Also, you should be aware that when using Maximum Rows to Fetch for queries, it can affect summaries in other groups that depend on this query. For example, if you set the Maximum Rows to Fetch property to 8, any summaries based on that query will only use the 8 records retrieved.
Group filters cannot be added to groups if the Filter Type property is set to First or Last.
Group filters cannot be added to cross-product groups.
The function that you enter for a group filter can only depend upon the following columns:
a database column owned by the group's query or a query above it in the data model hierarchy
computed columns (formulas or summaries) that depend on unrelated queries (that is, computed columns that do not depend upon columns in the group, the group's ancestors, or the group's descendants)
In a group filter, you can read the values of Reports Builder columns and
parameters of the correct frequency, but you cannot directly set their values.
For example, you can use the value of a parameter called COUNT1
in a condition (for example, IF :COUNT1 = 10
), but you cannot directly
set its value in an assignment statement (for example, :COUNT1 = 10
).
Note also that the use of PL/SQL global variables to indirectly set the
values of columns or parameters is not supported. If you do this, you may
get unpredictable results. You also cannot reference any page-dependent
columns (that is, Reset
At of Page) or columns that rely on page-dependent columns in a group
filter.
function filter_comm return boolean is
begin
if :comm IS NOT NULL then
if :comm < 100 then
return (FALSE);
else
return (TRUE);
end if;
else
return (FALSE); -- for rows with NULL commissions
end if;
end;
Creating or editing a group filter
About external PL/SQL libraries
Copyright © 1984, 2005, Oracle. All rights reserved.