Ways of Filtering Data

Filters, prompts and slicers provide ways of selecting or restricting the data that appears in your report.

You can apply filters to narrow the results returned by a query, based on criteria that you supply in a condition. Filters can be applied to detail rows before any aggregation occurs, which is equivalent to adding a WHERE clause to an SQL SELECT statement. Filters can also be applied to summary rows, which is equivalent to using a HAVING clause.

A prompt, or prompt control, provides a way to obtain user input for a filter or calculation. A parameter is a placeholder for a value that the user is asked to provide. A parameter value is the answer that the user provides when asked for input. The parameter value populates the parameter and supplies the criteria for the filter or calculation.

A slicer is a dimensional filter that is applied to the cells but not the rows or columns in a crosstab. It is defined as part of the query.

Some typical tasks you may want to do in the report specification are:

  • Add filters to both queries and subqueries
  • Add a summary or detail filter
  • Add a prompt to a separate prompt page
  • Filter the cells in a crosstab using a slicer

The main report specification objects you may want to use in filtering data are shown in the following table.

Table 1. Report specification objects used to filter data
Report Specification Objects Used to
promptPages element Contain all the prompt pages for the report.
promptButton element Represent an HTML-style navigation button used in prompt pages.
detailFilter element Specify a filter that is applied to the input data stream before aggregations or calculations are performed.
summaryFilter element Specify a filter that is applied after aggregates are calculated.
summaryFilterLevel element Specify a level to which the summary filter is applied.
selectDate, selectDateTime, selectInterval, selectOption, selectTime, selectValue, selectWithSearch, selectWithTree, selectWithTreeItem elements Specify prompt controls with characteristics based on the type of control.
defaultSelections, defaultTreeSelection, defaultRangeSelection, defaultSimpleSelection elements Specify the default values of prompt controls based on the type of control.
generatedPrompt element Specify an advanced control that acts as a placeholder for a prompt.
slicer element Create dimensional filters that reduce the data included in measure rollups.