Advanced filters

OpenOffice.org 3 Calc Guide

Chapter 13 Calc as a Simple Database

< Previous Section

Next Section >

• Associating a range to a name
• Sorting data
• Filters
• Advanced filters
• Calc functions similar to database functions
• Using formulas to find data
• Address, Indirect, Offset, Index
• Database-specific functions

< Previous Page

Next Page >

An advanced filter supports up to eight filter conditions, as opposed to the three supported by the simple filter. The criteria for an advanced filter is stored in a sheet. The first step in creating an advanced filter is entering the filter criteria into the spreadsheet.

1. Select an empty space in the Calc document. The empty space may reside in any sheet in any location in the Calc document.
2. Duplicate the column headings from the area to be filtered into the area that will contain the filter criteria.
3. Enter the filter criteria underneath the column headings (see Table 4). The criterion in each column of a row is connected with AND. The criteria from each row are connected with OR.

Table 4. Example advanced filter criteria

Name	Test 1	Test 2	Quiz 1	Quiz 2	Average	Grade
="Andy"		>80
					<80

Define named ranges to reference your advanced filter criteria and any destination ranges for filtered data as shown in the Define names dialog. Each appropriately configured named range is available in drop down list boxes in the Advanced Filter dialog shown below.

After creating one or more sets of filter criteria, apply an advanced filter as follows:

1. Select the sheet ranges that contain the data to filter.
2. Use Data > Filter > Advanced Filter to open the Advanced Filter dialog.
3. Select the range containing the filter criteria and any other relevant options.
4. Click OK.

Apply an advanced filter using a previously defined named range.

Applying an advanced filter using a macro is simple (see Listing 8). The cell range containing the filter criteria is used to create a filter descriptor, which is then used to filter the range containing the data.

Listing 8. Use an advanced filter.

Sub UseAnAdvancedFilter()
  Dim oSheet     'A sheet from the Calc document.
  Dim oRanges    'The NamedRanges property.
  Dim oCritRange 'Range that contains the filter criteria.
  Dim oDataRange 'Range that contains the data to filter.
  Dim oFiltDesc  'Filter descriptor.
 
  REM Range that contains the filter criteria
  oSheet = ThisComponent.getSheets().getByIndex(1)
  oCritRange = oSheet.getCellRangeByName("A1:G3")
 
  REM You can also obtain the range containing the 
  REM filter criteria from a named range.
  REM oRanges = ThisComponent.NamedRanges
  REM oRange = oRanges.getByName("AverageLess80")
  REM oCritRange = oRange.getReferredCells()
 
  REM The data that I want to filter
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oDataRange = oSheet.getCellRangeByName("A1:G16")
 
  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oDataRange.filter(oFiltDesc)
End Sub

Change properties on the filter descriptor to change the behavior of the filter (see Table 5).

The filter created in Listing 8 filters the data in place. Modify the OutputPosition property to specify a different output position (see Listing 9). The filter descriptor must be modified before the filter is applied.

Table 5. Advanced filter properties.

Property	Comment
ContainsHeader	Boolean (true or false) that specifies if the first row (or column) contains headers which should not be filtered.
CopyOutputData	Boolean that specifies if the filtered data should be copied to another position in the document.
IsCaseSensitive	Boolean that specifies if the case of letters is important when comparing entries.
Orientation	Specifies if columns (com.sun.star.table.TableOrientation.COLUMNS) or rows (com.sun.star.table.TableOrientation.ROWS) are filtered.
OutputPosition	If if CopyOutputData is True , specifies the position where filtered data are to be copied.
SaveOutputPosition	Boolean that specifies if the OutputPosition position is saved for future calls.
SkipDuplicates	Boolean that specifies if duplicate entries are left out of the result.
UseRegularExpressions	Boolean that specifies if the filter strings are interpreted as regular expressions.

Listing 9. Copy filtered results to a different location.

  REM Copy the output data rather than filter in place.  
  oFiltDesc.CopyOutputData = True
 
  REM Create a CellAddress and set it for Sheet3,
  REM Column B, Row 4 (remember, start counting with 0)
  Dim x As New com.sun.star.table.CellAddress
  x.Sheet = 2
  x.Column = 1
  x.Row = 3
  oFiltDesc.OutputPosition = x

(Warning, advanced material.) The OutputPosition property returns a copy of a struct. Because a copy is returned, it is not possible to set the individual values directly. For example, oFiltDesc.OutputPosition.Row = 2 does not work (because you set the Row on the copy to 2, but do not change the original).

Manipulating filtered data

Filtered data copied to a new location may be selected, modified, and deleted at will. Data that is not copied, however, requires special attention because rows that do not match the filter criteria are simply hidden. OpenOffice.org behaves differently depending on how the cells became hidden and what operation is done.

Cells may be hidden using an outline, data filter, or the hide command. When data is moved by dragging or using cut and paste, all of the cells are moved—including the hidden cells. When copying data, however, filtered data includes only the visible cells and data hidden using an outline or the hide command copies all of the data.