Create a context filter

When working with dimensional data, you can use context filters, or slicer filters, to quickly focus your report on a particular view of the data.

For example, the following crosstab contains product lines in the rows, years in the columns, and revenue as the measure. We want to filter the values to show us the revenue for only Web orders from Asia Pacific. To change the context, you drag Asia Pacific and Web from the source tree to the Context filter section of the overview area. The crosstab then shows the revenue for only Asia Pacific and Web. Changing context changes only the values that appear. It does not limit or change the items in the rows or columns.

Figure 1. Crosstab showing revenue by product line by year filtered to show only Asia Pacific and web orders
a crosstab showing revenue by product line by year filtered to show only Asia Pacific and web orders

The members that are used as the context filter appear in the report header when you run the report.

Any summary values in the report are recomputed to reflect the results returned by the context filter.

You can create multiple context filters to filter across two or more different hierarchies.

Tip: The Budget vs. Actual sample report in the Sales and Marketing (cube) package includes a context filter.
Note: There are limitations when using context filters:
  • Calculated measures do not get suppressed by the filter. To avoid this, drag the desired members from the Source tab source tab directly onto the row or column edge instead of using the members in the context filter.
  • Suppression can be slow on large cubes, because all of the data is retrieved and then filtered.

    If the desired data in the context filter is from a hierarchy that appears on the crosstab edge, drag the desired members from the Source tab directly onto the crosstab edge.

    If the desired data is from a hierarchy that does not appear on the crosstab edge, use a context filter in conjunction with other means to limit the data, such as a top or bottom filter.

To use a calculation or expression to define a context filter, create a slicer member set using the query explorer. A slicer member set is an expression that returns a set of members from the same dimension. For example, you can define a slicer member set that filters for the top five products with the greatest revenue.

Context filters are different from other filters. When you filter data, members that do not meet the filter criteria are removed from the report. A context filter does not remove members from a report. Instead, their values are filtered or you see blank cells.

You can also use context filters as prompts when the report is run.

When creating context filters,

  • use only members from hierarchies that are not already projected on an edge of the crosstab
  • use only one member per hierarchy