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.

Walkthroughs are presented using the AdventureWorks 2012 sample database.

2. Sort by a measure

When you add a data visualization that incorporates a measure to the report body area, that measure is automatically added to the report group's underlying metric set. This allows you to configure sorting for the measure via the Data Analysis Panel.

But what if you want to sort report rows by a measure that is not used by any data visualizations in the report body? Because you have access to the Data Analysis Panel, you can simply add measures manually by dragging them directly from the Explore window as normal. The following example illustrates this.

The data for this example comes from the [Sales].[SalesTerritory] table.

SalesTerritory table
SalesTerritory table

Design a report as follows:

  1. Create a new report from the main menu.
  2. Go to the Explore window and locate the [Sales].[SalesTerritory] table under your data connector.
  3. Drag the Name column to the grouping area (drop a column or hierarchy here to repeat controls for each value).
  4. Drag the Name column to the report body; it will appear as a data label for displaying territory names.
  5. Drag the SalesYTD column and hover over the magnifying glass icon to the left of the grouping area.

The Data Analysis Panel opens automatically, allowing you to drop the SalesYTD column onto the MEASURES field.

Design the initial report
Design the initial report

In the Data Analysis Panel, edit the SalesYTD measure to open the Configure Metric Set Element dialog.

Click Define custom measure sorting. In the Configure Measure Sorting dialog, set the Sort Direction to Descending.

Configure sorting for the SalesYTD measure
Configure sorting for the SalesYTD measure

Switch to View mode to see the report. Verify the territory names are sorted from highest to lowest SalesYTD measure values by comparing against the SalesTerritory table shown earlier.

View the report
View the report

3. Filter using a slicer

The following example shows you how to filter scorecard rows by adding a slicer hierarchy.

Design a scorecard as follows:

  1. Create a new scorecard from the main menu.
  2. Go to the Explore window and locate the [Production].[Product] table under your data connector.
  3. Drag the Name column to the grouping area (drop a column or hierarchy here to repeat controls for each value).
  4. Drag the Name column to the scorecard body; it will appear as a data label for displaying product names.
  5. Drag the ListPrice column to the scorecard body; it will appear as a data label for displaying list price values.

Click the magnifying glass icon to the left of the grouping area. This opens the Data Analysis Panel for the scorecard group. Drag the Color column to the SLICERS field for filtering purposes.

Drag the Color hierarchy to SLICERS
Drag the Color hierarchy to SLICERS

Edit the Color hierarchy from the Data Analysis Panel. In the Configure Metric Set Element dialog, click the Default Parameter Value dropdown and select Red as the filter value.

Set the default value for the slicer hierarchy
Set the default value for the slicer hierarchy

Switch to View mode to see the scorecard rows. Observe that only products with a red color are listed.

View the scorecard
View the scorecard

4. Add a Header control

The following example shows you how to use Header controls in your report or scorecard to allow users to sort and filter on multiple columns in View mode. This functionality is similar to using column menus for sorting and filtering a table visualization.

Design a scorecard as follows:

  1. Create a new scorecard from the main menu.
  2. Go to the Explore window and locate the [Production].[Product] table under your data connector.
  3. Drag the Name column to the grouping area (drop a column or hierarchy here to repeat controls for each value).
  4. Drag the Name column to the report body; it will appear as a data label for displaying product names.
  5. Drag the ListPrice column to the report body; it will appear as a data label for displaying list price values.

The scorecard should look like the following figure.

Initial scorecard design
Initial scorecard design

Go to the toolbar, click Data Visualization, and then select Header. A header control is added to the scorecard header area.

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

Select the header control and go to its Properties. Set the Analysis Element dropdown to the Name hierarchy. This is the hierarchy it will sort/filter on. The text of the header control also automatically updates to reflect the name of the chosen hierarchy.

Set the Analysis Element property
Set the Analysis Element property

Using similar steps, add a second header control to sort/filter the ListPrice column.

Switch to View mode to see the scorecard. Hover over the ListPrice column header and click the A-Z icon. 

View the scorecard and click the A-Z icon
View the scorecard and click the A-Z icon

In the Sort/Filter menu, click Descending. The scorecard rows are now sorted in descending order based on the ListPrice column.

Sort by ListPrice in descending order
Sort by ListPrice in descending order

Next, open the Sort/Filter menu for the Name column. Click the Filter Value dropdown and select a few hierarchy members.

Filter on the Name column
Filter on the Name column

The scorecard displays rows corresponding to the selected hierarchy members.

Scorecard is now filtered
Scorecard is now filtered

4.1. One Click Sorting

The Header control also supports one click sorting, which allows users to click the header to immediately sort the rows in ascending, descending, or unspecified order. A triangle icon above the header text tells you which direction it is currently sorted. If you don't see the icon, it means the sort direction is unspecified.

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

Enable One Click Sort property
Enable One Click Sort property

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