Sorting and filtering a report or scorecard

Contents[Hide]

1. Overview

This article shows various ways to sort and filter rows in a report or scorecard.

Reports and scorecards use their own metric set to determine the rows they display and their order, which you can sort and filter like you can a normal metric set.

2. Sort & filter while editing

When editing a report or scorecard, you can change the sorting and filtering to determine the settings that take effect when first viewed.

Click the cube & magnifying glass icon to the left of the grouping area. This opens the Data Analysis Panel for that group's metric set.

Open the Data Analysis Panel for the group
Open the Data Analysis Panel for the group

You can then click the sort & filter icon for any measure or hierarchy to sort or filter the report or scorecard by its values.

If the measure or hierarchy you want to sort or filter by is not already selected, you can add it here. For example, you can click to add data from the same data source or a new formula, or drag data from the Explore window.

Add a hierarchy to Slicers if it's meant only for filtering. If you add a measure, a data label will initially be added to display its values, but you can edit the measure and select its Hidden option or delete the data label if you intend only to sort or filter by its values.

After opening the group's Data Analysis Panel, the group metric set becomes selected as indicated by the blue outline around the clicked icon. When selected, you can preview the report/scorecard data and its sorting by opening the Data Preview window at the bottom of the screen.

Group metric set Data Preview
Group metric set Data Preview

Using this Data Analysis Panel, you can also use other metric set tools, including top/bottom N filteringcustom measure sorting and custom hierarchy sorting.

3. Add a filter

To allow the filtering to be changed while viewing, you can add filters to the parameters bar by clicking Filter in the toolbar, then choosing a filter type.

Add a member value filter
Add a member value filter

In a similar way to a dashboard, a view parameter is created together with the filter and is automatically connected to some compatible data. The parameter bar appears at the top of the screen with your filter, and the Parameters window opens below with the view parameter's details, which you can review.

Change the Name to determine the label that appears above the filter in the parameter bar, then scroll down to confirm which hierarchies or measures are connected to this filter.

Verify the filter's connections
Verify the filter's connections

To filter the rows of the report or scorecard itself, the group metric set's hierarchy should be selected as in the image above (i.e., under Group 1).

Switch to View mode. In the toolbar, click Change Filters to open the parameter bar and use the filter. 

Click Change Filters when viewing
Click Change Filters when viewing

Note
Users can open the Parameters bar using the context (right-click) menu when the toolbar is not available.

3.1. Filtering visualizations

Data visualizations may select data separately from the report or scorecard grouping, especially when they display multiple rows or data points within each visualization (e.g., a line chart). In this case, they appear separately in the Parameters window from the report or scorecard groupings.

If a visualization currently displays the report's or scorecard's own data, the text Displaying group data is shown in their Data Analysis Panel and options are disabled unless you open the group's Data Analysis Panel directly. You can click Select data separately if you want to create a separate metric set for the visualization.

Create a new metric set for the control
Create a new metric set for the control

Afterward, you can select more data or make other changes just for this visualization. New visualizations added using Data Visualization in the toolbar already start out with their own separate metric set.

Your filter must be connected to each visualization's metric set in order to filter its data. To edit the connections, re-open the parameter bar by choosing Change Filters in the toolbar, then click the Edit icon next to your filter.

Scroll down the Parameters window to your data and connect the filter to whichever visualizations should be affected.

Connect the filter to visualizations
Connect the filter to visualizations

4. Add a header control

You can add Header controls to your report or scorecard to allow users to sort and filter the rows in View mode. This functionality is similar to using column headers for sorting and filtering a table visualization

Note
The Header control can filter only hierarchies and measures present in the group metric set.

Add a Header from the toolbar
Add a Header from the toolbar

The Header can be found in the Data Visualization menu in the toolbar for reports, scorecards and small multiples. Once added, open the Properties window and set its Analysis Element. The text of the header will automatically update to reflect the name of the chosen element.

Set the Analysis Element property
Set the Analysis Element property

When viewing the scorecard or report, hover a header and click the A-Z icon to access its sort & filter popup. 

Hover over a header to access the sort & filter popup
Hover over a header to access the sort & filter popup

A sort & filter popup appears, which works the same as for a table visualization's headers.

Sort & filter popup
Sort & filter popup

You can find more details and examples about how sorting works with hierarchies, measures, and grouping in the article Sorting a table visualization.

4.1. One click sorting

The Header control also supports one-click sorting, which allows you to click the header to immediately sort the rows in ascending, descending, or unspecified order. A triangle icon above the header text tells you the current sort direction. If you do not see the icon, it means the sort direction is unspecified.

To use this option, go to Properties for the header control and select Enable One Click Sort.

Enable One Click Sort
Enable One Click Sort

One Click Sort icon indicates sort direction is Descending
One Click Sort icon indicates sort direction is Descending

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri