Filtering, Highlighting, and Slicers in Power View

Important

Please visit the most up-to-date Power View documentation on office.microsoft.com. Power View is now a feature of Microsoft Excel 2013, and is part of the Microsoft SQL Server 2012 Reporting Services add-in for Microsoft SharePoint Server 2010 and 2013 Enterprise Editions.

Power View provides several ways to filter and highlight data in reports. Because of the metadata in the underlying model, Power View knows the relationships between the different tables and fields in a report. This makes it possible to use items on a view to filter each other.

Note

Power View is a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition.

Highlighting, slicers, and filters filter all the visualizations on a view, and each other. You can create filters for the whole view or for individual visualizations. None of the filters apply across the whole report; all of them are specific to individual views in the report.

All of them allow you to select one or multiple values.

Clicking the beverages bar in the inset bar chart highlights beverage values in the large bar chart and filters the cards.

In this article

Interactive cross-filtering and highlighting with charts

Slicers

Filters

Filter behavior in a saved Power View report

Interactive cross-filtering and highlighting with charts

Charts can act as filters, thanks to those relationships in the underlying model. This is interactive filtering, meaning that you can select values directly on the chart and have that filter other data regions on a view. If you select one column in a column chart, this automatically:

• Filters the values in all the tables and tiles, and bubble charts in the report.

• Adds highlighting to bar and column charts. It highlights the parts of other bar and column charts that pertain to that value, showing the contribution of the filtered values to the original values.

You use Ctrl + click to select multiple values. To clear the filter, click inside the filter, but not on a value.

Interactive filtering also works with charts with multiple series. Clicking one section of a bar in a bar chart filters to that specific value. Clicking an item in the legend filters all the sections in that series.

In the following image, the column chart on the left is highlighting just cold beverages in the bar chart on the right. Cold beverages are only yellow and white. This shows what part of the yellow and white foods are cold beverages.

Column chart filtering a value in a series

Interactive filter selections are saved when you move from one view to another, but they are not saved with the report. When you save the report and open it later, each chart will be back in its original state.

Note

Clicking a value in the legend of a chart highlights that value in that chart. It does not filter or highlight any other visualization in that view.

Back to Top

Slicers

Slicers are another kind of filter. They filter everything on the page. Power View slicers look very much like slicers in PowerPivot for Excel. You create a single-column table from any field that is an attribute, and then convert the table into a slicer. Each value is a button, and a button in the top corner clears (resets) the filter. To select a value just click that button. The data is filtered in the report immediately.

You can select multiple values by holding the Ctrl key when you click.

You can also select all except a certain set of values by resetting the filter with the button in the top corner, and then using Ctrl + click to unselect specific values. This shows overall values excluding the unselected values.

Note

Clicking values in a table that is not a slicer does not filter the report.

You can add more than one slicer to your report. Slicers filter each other; for example, if you have two slicers, one for Product Categories and one for Products, when you click a category in the former, it filters the latter to show only products in that category. The filtering effects of all slicers are combined.

Unlike chart filters, slicer filters:

• Filter charts. They do not highlight charts.

• Are saved with the report. When you save, close, and reopen a report, the same values will be selected in the slicer.

• Can use image fields. Behind the scenes, Power View uses the Row Identifier field for that image as the filter in the slicer.

In the following image, the Category slicer on the left is filtered for fruits, so only the fruits in the Drawing slicer on the right remain.

The slicer on the left filters the values in the slicer on the right.

Back to Top

Filters

Power View also has basic and advanced filters for the whole view and for individual visualizations. Like slicers, filters in the filters area are saved with the report and affect only one view, not the whole report. Unlike slicers, these filters are not in the view itself, so they do not take up any space in the report.

If the filters area is visible in design mode, you and your report viewers can see and interact with the filters in the filters area even in reading and full screen presentation modes.

• To see the filters area, on the Home tab, click Filters Area or hover over a visualization and click the filter icon in the upper-right corner. Then drag fields from the fields section of the field list to the filters area.

Note

• If you have set a filter in the filters area, it will continue filtering the report even if you close the filters area.

• You cannot filter on an image field.

View-level filters

Like slicers, view-level filters in the filters area filter all the visualizations and all the slicers on a view.

1. Click the Filters Area icon on the Home tab.

   This opens a blank pane for view-level filters.

2. Drag fields from the fields section of the field list on the right to the filters area to filter the whole report on those fields.

   Or click the arrow next to a field in the fields section of the field list and then click Add to view filter.

Visualization-level filters

You can set visualization-level filters on tables, matrices, cards, and charts, but not on tile containers or slicers. However, you can set filters on the tables, matrices, and charts that are inside a tile container.

• View filters for an individual visualization by hovering over its upper-right corner and then clicking the filter icon. This opens the filters area already populated with the fields in the visualization.

  You can drag other fields from the field list to the filters area, even fields that are not in that visualization or anywhere on the view.

  Or click the arrow next to a field in the fields section of the field list and then click Add to [visualization] filter.

• If the filters area is expanded, when you select a visualization you see the visualization type in a gray heading in the filters area. For example, if you select a chart, you see a chart heading in the filters area. Click that heading to see the filters for the chart.

You can delete filters that you add to a visualization, but you cannot delete the filters for fields that are in the visualization. You can clear them, though, so they have no effect.

If you add a filter for a field in a visualization and then delete the field from the visualization, the filter remains in effect for that visualization.

• To remove the filter, open the filters area for the visualization and delete the filter.

Filtering measure fields

You can create filters in visualization-level filters on measure fields of integer and decimal measure data types. You can filter on measures that are not displayed in any visualization on the view.

For more information, see Measure and Non-Measure Fields in Power View.

Basic and advanced filters

There are two kinds of filters in the filters area: basic and advanced.

Basic filters

For non-numeric fields you select and clear check boxes, similar to AutoFilters in Microsoft Excel. The numbers after each value show how many records have that value. If there are more than 500 values in a field, you’ll see a message that not all items are showing.

For numeric fields, you drag markers on a slider to select the values between the markers.

Advanced filters

You can create more sophisticated filters, such as ranges of values, or freeform values. You can type all or part of a value to include or exclude.

For example, the advanced filter in this image is filtering for all values in the Name field that contain the letters ‘le’.

Advanced filter

• To use advanced filters, click the Advanced Filter Mode icon on the right end of the blue bar for a field filter.

Search in filters

You can search for a value within a visualization-level or view-level filter on a text field, and then choose to filter on it or not. The portions of a field's values that match the search text are highlighted in the search results list. Search is not case-sensitive, so searching for Apple yields the same results as apple. The search is limited to the visualization or to the view, depending on the filter—it does not search the whole report.

You can also use wildcard characters—the question mark (?) and asterisk (*). A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

For example, in a list of countries, typing r?n in the Search box returns two countries, France and Iran. Typing r*n in the Search box returns ten countries, including Germany, Ireland, and Kyrgyzstan.

Note

If you use wildcard characters in your search, the matching portion will not be highlighted in the search results list.

Back to Top

Filter behavior in a saved Power View report

Of the different ways that you can filter and highlight data in your report, each way behaves differently when you save a report:

• If you set a filter or slicer in design view, the state of the slicer or filter is saved with the report.

• If you highlight a chart in design view, its state is not saved with the report.

• No changes you make in reading or full-screen modes are saved, so if you filter or highlight in those modes, those changes are not saved either.

Back to Top

See Also

Concepts

Power View (SSRS)