Removing/Filtering records using Group Filters in Oracle Report (Oracle Report Builder)

Purpose: This will post will you understand the concept of “Group Filters” in Oracle report. Group filter allows you to remove records returned from the query (group) conditionally.

·         To use group filter click on the group and open the property Inspector of the group (shortcut “F4 key”).

·         There are 3 possible Filter types by which you can filter the records of the group.

o   First: - If you want your query to return first ‘n’ number of records then use the “First” filter type. Use “Number of Records” field to specify the number of records to be returned.

o   Last: - If you want your query to return last ‘n’ number of records then use the “Last” filter type. Use “Number of Records” field to specify the number of records to be returned.

o   PL/SQL:- If you want your query to return records conditionally then use “PL/SQL” filter type. In this type we have to write the conditions and logic in simple PL/SQL code. Double click on PL/SQL Filter field and write your code in the editor.

o   Here I had used a simple condition to explain the PL/SQL group filter. If the CF "CF_Balance" is not equal to zero then the record will display otherwise not.

Code:

If cf_balance <> 0 then

Return true;

Else

Return false;

End if;